Книги Проза Остросюжетная проза Молодёжная литература Современная зарубежная литература Классическая литература Интеллектуальная проза Романы взросления Детство Художественная литература для детей Научно-познавательные книги для детей KUMON Чевостик Развитие и обучение детей Досуг и творчество детей Книги для подростков Для родителей Комиксы для детей Детское творчество Умные книжки Подготовка к школе Необычный формат Подарочные Психология Популярная психология Стресс и эмоции Любовь и отношения Осознанность и медитация Книги для родителей Быть подростком Защита от токсичности Бизнес Аудиокниги Менеджмент Продажи Истории успеха Развитие сотрудников Предпринимателю Управление компанией Стратегия Управление проектами Переговоры Публичные выступления HR Российский бизнес IT Культура Автофикшн и биографии Серия «Таро МИФ» Серия «Мифы от и до» Подарочные книги Культурные истории, страноведение Искусство и архитектура Театр и кино, музыка, литература Серия «Главное в истории» Саморазвитие Спокойствие и душевное равновесие Аудиокниги Мечты и цели Мотивация Мозг и интеллект Продуктивность Психология Общение Сила воли Тайм-менеджмент Деньги Обучение Выбор профессии Принятие решений Осознанность Лайфстайл Современная магия Дом и сад Кулинария Велнес, красота, мода Творчество Вдохновение и мотивация Handmade и творческий бизнес Рисование для начинающих Рисование для продолжающих Леттеринг и каллиграфия Писательство Фотомастерская Активити для взрослых Легендарная серия Барбары Шер Психология творчества Дизайн Развитие творчества Творческий бизнес Визуальное мышление Творческое мышление МАК МИФ Комиксы Детские комиксы Взрослые комиксы Молодежные комиксы Серии Познавательные комиксы Здоровье и медицина Правильное питание Спорт Долголетие Бег Фитнес Медитация Здоровый сон Диеты Научпоп Физика Математика Экономика Здоровье и медицина Мышление и психология Технологии Подарочные книги Искусство, культура и путешествия Для детей Работа и бизнес Для души и уюта Захватывающие истории Время для себя Маркетинг Маркетинг и брендинг Генерация идей Копирайтинг, блогинг, СМИ Серия «Думай иначе» Настольные игры Курсы и мероприятия «Книжные» профессии Душа, ум и тело Карьера и бизнес Лектории Практикумы: hard skills Бесплатно Курсы месяца Получить профессию Все курсы Для бизнеса Электронная библиотека Офисная библиотека Детские подарки Подарки партнерам Продвижение бренда Курсы для компаний Издать книгу Издательство Работа у нас Логотип Предложить книгу Об издательстве Авторам Вопросы и ответы Контактная информация Блоги Блог МИФа Психология и саморазвитие Творчество Проза Кругозор Книжный клуб МИФа Комиксы Бизнес-блог Бизнесхак и маркетинг Формула менеджмента Саморазвитие Корпоративная культура Опыт МИФа Обзоры книг Папамамам Развитие ребенка Психология Вот так книга! Искусство учиться
Бизнесхак и маркетинг
Бизнесхак на каждый день. Выпуск №7. Полезные функции для работы в Excel
21 ноября 2016 10 992 просмотра

Ренат Шагабутдинов
Ренат Шагабутдинов

Новый выпуск рубрики «Бизнесхак на каждый день» посвящен работе в Экселе. Большинство менеджеров, офисных работников, руководителей, аналитиков, специалистов работают с таблицами и массивами данных. И если Excel (или аналоги) занимает в вашей работе не последнее место, вы можете экономить очень много времени, если оптимизируете рабочий процесс.

Большинство рутинных и повторяющихся операций можно как минимум упростить, как максимум — полностью автоматизировать!

В моей преподавательской практике нередки случаи, когда ученики начинают заниматься индивидуально и сразу рассказывают про какой-нибудь огромный отчет, подготовка которого требует нескольких часов (а чаще — дней) и много нервов. В подавляющем большинстве случаев мы находим решение, которое позволяет сделать отчет за 15–20 минут или за час.

Используйте простой принцип: если конкретная задача в офисных приложениях рутинная, требует последовательности однотипных шагов, больших временных затрат и при этом она не интеллектуальная (т.е. не требует экспертных оценок, а только арифметических, логических операций), значит, высока вероятность того, что вы делаете что-то не так. И эту задачу можно оптимизировать.

Если есть сомнения, оптимально ли вы работаете с конкретным файлом, списком, таблицей, воспользуйтесь следующим критерием: представьте, что объем работы увеличился в пять раз, в десять, в двадцать… А операции остались теми же самыми.

Сможете ли вы при существующей методике или наборе формул обработать в 20 раз больший объем строк/столбцов/таблиц? Или это займет слишком много времени, так как вы многое делаете вручную? Если последнее верно, то вам наверняка стоит пересмотреть свои методики и приемы в данном случае.

В этой статье расскажу о самых полезных функциях и приемах, позволяющих упростить и ускорить работу в Excel.

Как сделать файл Excel быстрее и «легче»

  • Поменять формат на .XLSX (формат версий 2007 и более поздних), .XLSM (с макросами) или .XLSB (самый быстрый и сжатый, с макросами). Удивительно, но до сих пор многие пользуются по инерции файлами версии 2003 (расширение .XLS), хотя они медленнее и могут занимать в разы больше дискового пространства.
  • Не заливать строки и столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).
  • Не ставить фильтр на все столбцы (их в файле новых версий 16 384. В вашей таблице обычно намного меньше).
  • Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.
  • Очистить примечания, если их много и они не нужны.
  • Проверить, нет ли проверки данных на очень большом диапазоне ячеек.
  • Не сохранять кэш сводных таблиц (Параметры сводной таблицы → Сохранять исходные данные вместе с файлом).
  • Удалить неиспользуемые именованные диапазоны (диспетчер имен вызывается сочетанием клавиш Ctrl+F3).
  • Удалить ненужные макросы, если они есть (чтобы попасть в редактор макросов, нажмите Alt+F11).

Несопоставимое сопоставимо: диаграмма с двумя осями

У нас есть несовместимые по объему данные — количество сотрудников в компании и выручка (план-факт). Но мы хотим сравнить их в динамике на одном графике. Строим обычный график по всем данным:

Данные по количеству сотрудников настолько небольшие (относительно), что их не видно на графике (они внизу). Чтобы их увидеть, щелкаем правой кнопкой и меняем тип диаграммы:

Меняем тип на «График» и указываем, что количество сотрудников отображается на вспомогательной оси:

Результат:

Как отсортировать список не по алфавиту?

В Excel можно быстро отсортировать данные в алфавитном порядке (или в обратном алфавитном порядке). Но как быть, если элементы списка должны сортироваться в произвольном порядке?

Например, у вас есть отчет по продажам в разных городах, для каждого из которых указан федеральный округ, и его нужно сортировать именно по последнему. Причем ЦФО должен идти на первом месте, СЗФО — на втором, а ПФО — на третьем. По алфавиту их отсортировать не получится.

Как быть? Заходить в Сортировку (раздел «Данные» на ленте инструментов), выбирать сортировку по региону и в списке «Порядок» выбрать «Настраиваемый список».

image01

После этого появится новое окно, в котором вы сможете создать новый список. Для этого просто вводите элементы в том порядке, который вам нужен:

Теперь вы сможете сортировать список в нужном вам порядке.

Как быстро заполнить пустые ячейки?

Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (т.е. ставим знак =) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем 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
 
Похожие статьи