Новый выпуск рубрики «Бизнесхак на каждый день» посвящен работе в Экселе. Большинство менеджеров, офисных работников, руководителей, аналитиков, специалистов работают с таблицами и массивами данных. И если Excel (или аналоги) занимает в вашей работе не последнее место, вы можете экономить очень много времени, если оптимизируете рабочий процесс.
В моей преподавательской практике нередки случаи, когда ученики начинают заниматься индивидуально и сразу рассказывают про какой-нибудь огромный отчет, подготовка которого требует нескольких часов (а чаще — дней) и много нервов. В подавляющем большинстве случаев мы находим решение, которое позволяет сделать отчет за 15–20 минут или за час.
Используйте простой принцип: если конкретная задача в офисных приложениях рутинная, требует последовательности однотипных шагов, больших временных затрат и при этом она не интеллектуальная (т.е. не требует экспертных оценок, а только арифметических, логических операций), значит, высока вероятность того, что вы делаете что-то не так. И эту задачу можно оптимизировать.
Если есть сомнения, оптимально ли вы работаете с конкретным файлом, списком, таблицей, воспользуйтесь следующим критерием: представьте, что объем работы увеличился в пять раз, в десять, в двадцать… А операции остались теми же самыми.
Сможете ли вы при существующей методике или наборе формул обработать в 20 раз больший объем строк/столбцов/таблиц? Или это займет слишком много времени, так как вы многое делаете вручную? Если последнее верно, то вам наверняка стоит пересмотреть свои методики и приемы в данном случае.
В этой статье расскажу о самых полезных функциях и приемах, позволяющих упростить и ускорить работу в Excel.
Как сделать файл Excel быстрее и «легче»
- Поменять формат на .XLSX (формат версий 2007 и более поздних), .XLSM (с макросами) или .XLSB (самый быстрый и сжатый, с макросами). Удивительно, но до сих пор многие пользуются по инерции файлами версии 2003 (расширение .XLS), хотя они медленнее и могут занимать в разы больше дискового пространства.
- Не заливать строки и столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).
- Не ставить фильтр на все столбцы (их в файле новых версий 16 384. В вашей таблице обычно намного меньше).
- Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.
- Очистить примечания, если их много и они не нужны.
- Проверить, нет ли проверки данных на очень большом диапазоне ячеек.
- Не сохранять кэш сводных таблиц (Параметры сводной таблицы → Сохранять исходные данные вместе с файлом).
- Удалить неиспользуемые именованные диапазоны (диспетчер имен вызывается сочетанием клавиш Ctrl+F3).
- Удалить ненужные макросы, если они есть (чтобы попасть в редактор макросов, нажмите Alt+F11).
Несопоставимое сопоставимо: диаграмма с двумя осями
У нас есть несовместимые по объему данные — количество сотрудников в компании и выручка (план-факт). Но мы хотим сравнить их в динамике на одном графике. Строим обычный график по всем данным:
Данные по количеству сотрудников настолько небольшие (относительно), что их не видно на графике (они внизу). Чтобы их увидеть, щелкаем правой кнопкой и меняем тип диаграммы:
Меняем тип на «График» и указываем, что количество сотрудников отображается на вспомогательной оси:
Результат:
Как отсортировать список не по алфавиту?
В Excel можно быстро отсортировать данные в алфавитном порядке (или в обратном алфавитном порядке). Но как быть, если элементы списка должны сортироваться в произвольном порядке?
Например, у вас есть отчет по продажам в разных городах, для каждого из которых указан федеральный округ, и его нужно сортировать именно по последнему. Причем ЦФО должен идти на первом месте, СЗФО — на втором, а ПФО — на третьем. По алфавиту их отсортировать не получится.
Как быть? Заходить в Сортировку (раздел «Данные» на ленте инструментов), выбирать сортировку по региону и в списке «Порядок» выбрать «Настраиваемый список».
После этого появится новое окно, в котором вы сможете создать новый список. Для этого просто вводите элементы в том порядке, который вам нужен:
Теперь вы сможете сортировать список в нужном вам порядке.
Как быстро заполнить пустые ячейки?
Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):
Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (т.е. ставим знак =) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны.
Как просуммировать ячейки с нескольких листов?
Если у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе, в ячейку, где вы хотите увидеть результат, введите стандартную формулу (например, СУММ (SUM)), но укажите в аргументе через двоеточие название первого и последнего листов из списка тех, что вам нужно обработать:
Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:
Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий:
=ФУНКЦИЯ(первый_лист:последний_лист!ссылка на диапазон).
Маленькие графики в ячейках: спарклайны
У вас есть много рядов данных: допустим, данных по продажам книг (цифры в примере случайные, но это не столь важно), и вы хотите посмотреть динамику по каждому ряду, но при этом не создавать отдельных диаграмм. Для этого подойдут спарклайны — мини-графики в ячейках, которые появились в версии Excel 2010.
Выделяем те ячейки, в которых будут спарклайны, и в разделе «Вставка» на ленте инструментов выбираем один из трех типов спарклайна (график и гистограмма подойдут для отображения динамики продаж, а «Выигрыш-Проигрыш» — для тех случаев, когда вы хотите визуально разделить отрицательные, положительные и нулевые значения — пример будет ниже):
Выбираем диапазон с данными, на вкладке «Конструктор», которая появляется на ленте инструментов Excel при выделении ячеек со спарклайнами, можно изменить их внешний вид:
- поменять цвет спарклайна и маркеров;
- выделить первую, последнюю, максимальную и минимальную точки;
- добавить ось;
- выделить отрицательные точки.
Пример с типом спарклайна «Выигрыш-проигрыш». Спарклайн показывает, были по соответствующему ряду возвраты (отрицательные «продажи») и нулевые продажи; и если были — то когда:
А что делать, если у вас Excel 2003 или 2007?
В этих версиях спарклайнов еще не было. Но вы можете воспользоваться функцией ПОВТОР (REPT), которая повторяет текст определенное количество раз. Ее первый аргумент — сам текст, а второй — количество повторов.
Выберите подходящий символ — можно какую-нибудь красивую иконку из шрифта Windings. Чтобы посмотреть, какие символы есть в шрифте и какие у них коды, напишите в столбец числа от 1 до 255, а правее введите функцию СИМВОЛ (шрифт в правом столбце нужно соответственно поменять на Windings). Для продаж книг подойдет символ с номером 38 ☺. Хорош и обычный квадратик — у него номер 110 в шрифте Windings. Обратите также внимание на шрифты Webdings, Wingdings 2 и Wingdings 3.
Итак, составляем итоговую формулу: повторяем нужный нам символ в количестве, пропорциональном продажам из соответствующего столбца. При этом продажи в зависимости от масштаба стоит поделить на 10, 100 или другую степень десятки, чтобы у вас не было тысяч символов:
Как быстро добавить новые данные в диаграмму?
Возможно, вам приходится обновлять некоторые диаграммы раз в месяц, квартал или с другой периодичностью. В примере вам нужно добавить данные за ноябрь и декабрь в диаграмму:
Как сделать это максимально быстро? Очень просто: выделите те данные, которые нужно добавить (в данном случае диапазон A12:B13), скопируйте их (Ctrl+C), выделите диаграмму мышкой и вставьте (Ctrl+V) данные. Несколько секунд — и готово
Если вам понравились эти бизнесхаки, еще больше их можно найти в книге Игоря Манна и Рената Шагабутдинова «Бизнесхак на каждый день». И, конечно, не пропускайте рубрику в нашем блоге. До следующего понедельника!
P.S. Все данные в примерах вымышлены.
Обложка поста: vsetop.com
