Опубликовано по ссылке:
Цель написания этой книги – открыть читателям новые углубленные техники использования такого привычного, казалось бы, инструмента, как Excel. За двадцать лет преподавания и консультирования я изучил огромное количество тонкостей и нюансов применения Excel, освоил не самые очевидные техники написания формул и хочу поделиться этим с вами.
Microsoft Excel используется в офисной работе повсеместно для ведения учета и анализа данных. Но при этом подавляющее большинство пользователей Excel обладают лишь базовыми навыками этого богатого программного продукта, познакомившись с ним в институте или прочитав пару статей в интернете. Они нажимают какие-то кнопки, щелкают мышкой, зачастую даже не понимая, как получается нужный им результат. В этой книге я постараюсь открыть вам некоторые тайны и секреты Excel и научить пользоваться этим мощным инструментом на совершенно новом уровне.
Книга, которую вы держите в руках, будет полезна всем, кто активно использует Excel в своей работе вне зависимости от профессии и требований к программе.
Содержание глав книги:
глава 1. В данной главе мы сосредоточимся на техниках и приемах, которые помогут вам повысить эффективность использования Excel на практике – я поделюсь с вами навыками, полученными за долгие годы работы и преподавания;
глава 2. Эта глава будет целиком посвящена формулам и некоторым предельно полезным функциям Excel. Вы глубоко погрузитесь в мир формул Excel и узнаете о них то, чего не знали раньше. После этого я познакомлю вас с несколькими крайне эффективными функциями для анализа данных и создания динамических отчетов с использованием реальных примеров;
глава 3. Здесь мы познакомимся с продвинутыми техниками форматирования, способными придать вашим данным значимости. Начнем с углубленного изучения условного форматирования, после чего выведем эту технику на новый качественный уровень с помощью пользовательского форматирования;
глава 4. В данной главе вы узнаете массу приемов, облегчающих и улучшающих процесс создания диаграмм в Excel. Речь пойдет в том числе об автоматическом изменении цветов для ключевых показателей, динамической сортировке данных и креативном подходе к созданию меток на диаграммах;
глава 5. Эта глава позволит вам познакомиться с одной из важнейших новинок в Excel за последние несколько лет – инструментом Power Query. Мы пройдемся по нескольким практическим примерам загрузки и подготовки данных для анализа с использованием Power Query;
глава 6. Заключительная глава книги будет посвящена Power Pivot – надстройке, выходящей за границы традиционного использования рабочих листов в Excel. С помощью Power Pivot вы сможете хранить большие объемы информации, создавать модели данных и производить мощные вычисления.
Демонстрационные файлы
Вы можете загрузить файлы примеров, используемые на протяжении всей книги, чтобы попрактиковаться с ними самостоятельно. Учиться лучше всего на практике. Следуйте инструкциям, исследуйте примеры и набирайтесь опыта.
Файлы можно скачать на странице книги издательства Apress по адресу: www.apress.com/gp/book/9781484264669.
Все демонстрационные файлы организованы по папкам в соответствии с главами книги.
Глава 1. Приемы Excel и инструменты для работы с данными
Наверное, все любят приемчики в Excel. Я так точно люблю. Обожаю находить всякие фишечки и неизвестные мне ранее нюансы и использовать их в работе. Это позволяет мне постоянно узнавать что-то новое.
Зачастую при решении определенной задачи в Excel ответ находится совсем не там, где вы ожидали. К примеру, он может быть скрыт в инструменте, который, как вы считали до этого, вы прекрасно знаете. Но вдруг вас осеняет, и ставший доступным вам новый прием открывает новые горизонты. Вы начинаете судорожно думать, где бы еще его применить на практике. Я обожаю это чувство.
В данной главе я поделюсь с вами приемами и трюками, которые открыл для себя за последние годы. Этими находками я обязан своим друзьям, студентам и, конечно, собственной любознательности. Надеюсь, многим из вас эти приемы помогут облегчить свою рутинную работу.
Техники заполнения
Давайте начнем с несложных техник заполнения. Это, наверное, одни из первых приемов, которые люди осваивают при знакомстве с Excel, но и здесь есть нюансы, о которых знают далеко не все.
Генерация числовых последовательностей
Как вы, наверное, знаете, для генерации простых числовых последовательностей (например, при создании порядковых номеров) заполнения одной ячейки недостаточно, поскольку по умолчанию Excel просто скопирует ее значение во все соседние ячейки.
Введите первое число из последовательности (в нашем примере это единица) в ячейку, затем выделите ее и наведите курсор на маркер заполнения (fill handle) в нижнем правом углу ячейки, показанный на рис. 1.1, чтобы появилось перекрестие.
@Рис. 1.1. Использование маркера заполнения для генерации числовых последовательностей
С зажатой левой кнопкой мыши протяните вниз на столько ячеек, сколько хотите заполнить значениями.
В каждой из затронутых ячеек появится то же число, которое было введено в исходную ячейку, что видно по рис. 1.2.
@Рис. 1.2. При протягивании одной ячейки вниз остальные ячейки заполняются тем же значением
Ввод отличающегося числового значения в соседнюю с исходной ячейку позволит создать требуемую последовательность, как показано на рис. 1.3.
@Рис. 1.3. Использование двух чисел для генерации последовательности
Но вам нет никакой необходимости делать все это. В Excel можно сгенерировать нужную вам последовательность и гораздо проще. Просто введите в ячейку число и заполняйте соседние ячейки как показано выше, но с зажатой клавишей Ctrl.
Альтернативный способ состоит в задействовании волшебной ячейки справа. Выделите эту ячейку вместе с исходной и просто протягивайте вниз, как показано на рис. 1.4.
@Рис. 1.4. Использование волшебной ячейки справа для генерации последовательности
Дополнительные опции последовательностей
Доступ к дополнительным опциям последовательностей можно получить, если переместить маркер заполнения из исходной ячейки и обратно с зажатой правой кнопки мыши. После отпускания кнопки мыши вы увидите меню, показанное на рис. 1.5. Выберите пункт Прогрессия… (Series…)
@Рис. 1.5. Нажмите правую кнопку мыши, выведите заполнения из ячейки и верните обратно, чтобы открыть секретные опции
Примечание. Доступ к этим опциям можно также получить, открыв пункт меню Главная => Заполнить => Прогрессия (Home => Fill => Series).
Диалоговое окно Прогрессия (Series), показанное на рис. 1.6, предоставляет богатые возможности в плане создания последовательностей, в числе которых заполнение по строкам или столбцам и выбор шага и предельного значения для последовательности.
@Рис. 1.6. Дополнительные опции в диалоговом окне Прогрессия
Давайте для примера создадим последовательность с шагом 2 и предельным значением 15. Результат показан на рис. 1.7.
@Рис. 1.7. Числовая последовательность от 1 до 15 с шагом 2
Это лишь один из примеров того, что можно сделать. Теперь давайте посмотрим на более реалистичный сценарий.
Перед нами стоит задача создания списка дат с 3 марта 2020 года по 30 октября 2022 года с двухнедельным интервалом (3 марта 2020 года – это вторник).
Введите значение 03/03/2020 в исходную ячейку и откройте диалоговое окно Прогрессия.
Excel автоматически определит, что вы собираетесь строить последовательность дат. Убедитесь, что в группе Тип (Type) выбран пункт Даты (Date), а в группе Единицы (Date unit) – День (Day).
Введите число 14 в поле Шаг (Step value) и 30/10/2022 – в поле Предельное значение (Stop value). Полностью заполненное окно Прогрессия показано на рис. 1.8.
@Рис. 1.8. Последовательность дат с предельным значением
Нажмите на кнопку OK.
Сгенерированный список будет выглядеть так, как показано на рис. 1.9. Этот способ куда легче, чем вводить две даты в ячейки и протягивать маркер заполнения вниз до 30 октября 2022 года.
@Рис. 1.9. Список дат с двухнедельным интервалом, начиная с 2 марта 2020 года
В данном случае последовательность дат продлилась до 25 октября 2022 года – это последний вторник из выбранного интервала.
Невероятное мгновенное заполнение
Инструмент мгновенного заполнения (Flash Fill) появился в Excel версии 2013, и когда я впервые опробовал его в работе, я ночь не спал. Наряду с Power Query, о котором мы поговорим в главе 5, этот механизм значительно облегчил выполнение задач, которые ранее занимали уйму времени.
Давайте рассмотрим на примерах, что и как умеет делать инструмент мгновенного заполнения. Сразу скажу, что мы лишь познакомимся с основами этой концепции, а глубже изучить вы ее сможете самостоятельно.
Файл: flash-fill.xlsx
В первом примере у нас есть список людей, имена которых записаны в столбце A, а фамилии – в столбце B. В столбце C мы хотим соединить их вместе.
Введите имя и фамилию первого человека в списке и нажмите на клавишу Enter. Начните писать имя второго человека, и механизм мгновенного заполнения угадает ваши намерения и предложит дополнить текст автоматически, как показано на рис. 1.10. Нажмите на клавишу Enter, чтобы подтвердить действие и заполнить список до конца.
@Рис. 1.10. Мгновенное заполнение автоматически подбирает нужный шаблон
Как же легко оказалось объединить вместе имена и фамилии людей, даже не прибегая к помощи формул.
Примечание. Вы можете отключить механизм автоматического заполнения в параметрах Excel, если вам не нужно такое поведение.
Для второго примера заполним столбец A кодами, как показано на рис. 1.11, и предположим, что во втором столбце нам необходимо извлечь из кодов содержимое между двумя дефисами. Одновременно желательно перевести извлеченные фрагменты кодов в верхний регистр.
Если пользоваться для этого формулой, она окажется весьма непростой для понимания, но с механизмом мгновенного заполнения все просто.
@Рис. 1.11. Список кодов с информацией для извлечения
Встаньте в ячейку B2 и введите буквы JH в верхнем регистре.
Нажмите сочетание клавиш Ctrl + Enter, чтобы подтвердить ввод, но остаться при этом в ячейке B2.
Нажмите сочетание клавиш Ctrl + E, активирующее мгновенное заполнение.
Да, вот так просто мы извлекли данные, необходимые нам для дальнейшего анализа. Результат показан на рис. 1.12.
Примечание. Механизм мгновенного заполнения также можно запустить, выбрав в меню Главная => Заполнить => Мгновенное заполнение (Home => Fill => Flash Fill or Data => Flash Fill).
@Рис. 1.12. Готовое решение, полученное при помощи мгновенного заполнения
Преимущества настраиваемых списков
Когда вы вводите название месяца или дня недели в Excel и протягиваете ячейки, автоматически создаются правильные последовательности, что видно по рис. 1.13. Это возможно по причине того, что Excel хранит подобные последовательности в виде настраиваемых списков (custom lists).
@Рис. 1.13. Последовательность из дней недели в Excel
Но вы можете создавать собственные настраиваемые списки в Excel. Это поможет увеличить скорость и точность ввода последовательностей данных в будущем, что весьма и весьма полезно.
Еще одно применение настраиваемых списков состоит в выполнении эффективной сортировки данных. У вас есть возможность сортировать столбцы в соответствии со списками, но что, если элементы в нем расположены не в том порядке, как вам нужно?
Предположим, у нас есть срез (slicer) для фильтрации данных в умной таблице (table) или сводной таблице. В нем содержатся дни недели, отсортированные в определенном порядке, как показано на рис. 1.14. Но, быть может, для вас первым днем недели является не понедельник (Monday), а воскресенье (Sunday). Таким образом, вы бы хотели, чтобы именно этот день недели располагался первым при выполнении сортировки.
@Рис. 1.14. Срез с днями недели, отсортированный при помощи стандартного списка
Чтобы создать собственный настраиваемый список, вам необходимо выполнить следующие действия.
Перейти в меню Файл => Параметры => Дополнительно => Изменить списки (File => Options => Advanced => Edit Custom Lists), как показано на рис. 1.15.
@Рис. 1.15. Кнопка Изменить списки (Edit Custom Lists) в меню Дополнительно (Advanced)
Вы не можете редактировать встроенные списки, так что придется создать собственный. С выбранным элементом НОВЫЙ СПИСОК (NEW LIST) в окне Список (Custom lists) введите дни недели в нужном вам порядке в правом окне Элементы списка (List entries). После каждого введенного элемента нажимайте на клавишу Enter. Готовый список показан на рис. 1.16.
Нажмите на кнопку Добавить (Add) для перемещения созданного списка в левое окно, после чего нажмите на кнопку OK для закрытия окна.
Примечание. Вы также можете импортировать список из диапазона ячеек при помощи кнопки Импорт (Import).
@Рис. 1.16. Создание своего настраиваемого списка дней недели
Созданный список можно использовать для сортировки в срезе.
Выделите срез и выберите в меню пункт Срез => Настройка среза (Slicer => Slicer Settings).
Убедитесь, что флажок Использовать настраиваемые списки при сортировке (Use Custom Lists when sorting) включен, как показано на рис. 1.17. При этом вам может понадобиться сортировать данные в порядке убывания, после чего вы можете вернуться к сортировке по возрастанию.
@Рис. 1.17. Использование настраиваемых списков для выполнения сортировки среза
Элементы среза теперь отсортированы в нужном нам порядке, что видно по рис. 1.18.
@Рис. 1.18. Срез, отсортированный в соответствии с настраиваемым списком
Создание собственных настраиваемых списков для хранения названий месяцев или дней недели – это довольно распространенный способ использования этой возможности. К тому же в разных сценариях могут понадобиться разные первые месяцы.
Кроме того, настраиваемые списки можно использовать и довольно творчески. Представьте, что вы осуществляете продажи в разных странах, что отражено в срезе (или сводной таблице). При этом в двух странах – Германии (Germany) и Швейцарии (Switzerland) – у вас расположены флагманские магазины, и вы хотели бы всегда видеть их на вершине списка для удобства.
Создание настраиваемого списка, показанного на рис. 1.19, в котором нужные нам страны расположены наверху, и использование его для сортировки обеспечит нам желаемый результат.
@Рис. 1.19. Настраиваемый список с требуемой сортировкой по странам для среза
Одновременное изменение нескольких рабочих листов
Иногда у вас возникает потребность изменить одновременно несколько рабочих листов: удалить столбцы, отформатировать ячейки или вписать в них формулы. Это можно легко реализовать путем группировки листов.
В демонстрационном файле group-worksheets.xlsx присутствует пять листов с квартальными продажами товаров. Каждый лист представляет собой отчет одного магазина, и мы хотим сделать определенные изменения. Рабочая книга показана на рис. 1.20.
@Рис. 1.20. Пять рабочих листов с данными
Чтобы произвести изменения на всех листах одновременно, необходимо щелкнуть правой кнопкой мыши на закладке с листами и выбрать пункт Выделить все листы (Select All Sheets), как показано на рис. 1.21.
@Рис. 1.21. Выделение всех рабочих листов
Все листы сгруппированы, что можно понять по слову Группа (Group) в строке заголовка рядом с названием рабочей книги (рис. 1.22).
@Рис. 1.22. Группировка листов в заголовке рабочей книги
Теперь можно приступать к изменениям на листе, и все они будут продублированы на остальных листах, входящих в группу.
В нашем случае заголовки таблицы были выделены жирным шрифтом, а в строке 8 была использована функция СУММ (SUM) для подведения итогов по кварталам, при этом сами ячейки итогов были обрамлены верхними и нижними границами, а формат в них был изменен на числовой, как показано на рис. 1.23.
@Рис. 1.23. Форматирование и применение формул на сгруппированных листах
Чтобы разгруппировать объединенные листы, просто щелкните по закладке неактивного рабочего листа.
Примечание. Можно сгруппировать не все листы, а выборочные. Для этого необходимо выделить их в закладках с зажатой клавишей Ctrl. Диапазоны последовательно расположенных листов можно выбрать, указав первый и последний из них с зажатой клавишей Shift.
Текст неполный и представлен для ознакомления.
Купить книгу:
https://dmkpress.com/catalog/computer/ofisnye_programmy/978-5-97060-922-4/
Промокоды:
Бумажная версия: промокод Ginko_MS_Excel_paper
Версия PDF: промокод Ginko_MS_Excel_PDF