Журнал

Как выжать из Excel максимум

10 продвинутых лайфхаков от лектора курса «Advanced Excel».

cov-5eeb233fa220b772581155.jpg

Данные — золото для любого бизнеса. Но, как правило, они организованы в файлы, в которых еще нужно разобраться. Microsoft Excel — один из тех инструментов, который помогает это сделать.

Операционный менеджер Shell Никита Свидло рассказывает о десяти простых, но очень полезных лайфхаках Excel, которые сэкономят время и деньги.

#1. Разбить на колонки

Допустим, вы собираете данные из Google Форм. Они организуются в формате CSV-файла и не разбиваются на колонки автоматически. То есть ячейка выглядит как длинная строка, где текст разделен запятыми, косой линией или точкой с запятой.

Работать с данными в таком формате неудобно. Чтобы проводить вычисления, я разбиваю эти данные на колонки.

Как это сделать

Нажимаем на вкладку «Данные», выбираем «Текст по столбцам». Появляется окошко, в котором нужно выполнить три действия поэтапно:

1. Выбрать формат данных. В окне первого шага поставить галочку на опции «С разделителями» (иногда система по умолчанию выбирает правильный разделитель). Нажать «Далее».

2. Выбрать формат разделителя. Из предложенных вариантов (знак табуляции, точка с запятой, запятая, пробел или другой) выбрать один — например, запятую. Нажать «Далее».

3. Выбрать формат колонки — обычный, текстовой или с датой. Вид новой колонки отображается сразу же во всплывающем окне. Нажать «Готово». Система предупредит, что информация перестроится — и через пару секунд получаем таблицу с колонками.

#2. Объединить колонки

Допустим, у меня есть таблица из трех колонок: код клиента, его имя и фамилия. Но удобнее использовать эту информацию в формате строки.

Как это сделать

Формула СЦЕПИТЬ позволяет объединить несколько значений в одной строке. Например, нужно объединить ячейки В2 и C2. Для этого:

1. Вписать в ячейку формулу — например, =СЦЕПИТЬ(В2; C2). 

2. Добавить «;" "» между значениями. После этого формула будет выглядеть как =СЦЕПИТЬ(В2;" ";C2).

Альтернатива. Символ & (амперсанд) тоже помогает связывать колонки. Чтобы объединить значения А3, В3 и С3, нужно прописать код — например, "=А3&" "В3&" "&C3". Вместо пробела можно ставить дефис, тире или любой другой знак пунктуации.

#3. Ссылки на ячейки и диапазоны внутри одной книги Excel

Я всегда советую не использовать вставку как значение при работе в Excel. Ведь уже через 10 минут будет сложно вспомнить, откуда взята та или иная цифра или значение. Куда удобней и практичней использовать линк на другой лист Excel. Так всегда можно найти исходное значение и легко проверить все значения.

Как это сделать

1. Вписать «=» в ячейку, куда хотите добавить ссылку.

2. Перейти на другой лист, щелкнуть по второй ячейке. Так вы поставите ссылку на любое значение в другом листе Excel-книги. Нажать Enter.

3. Значение переносится в эту ячейку. В строке вверху будет выглядеть примерно так: «=’Лист1’!М7».

Если в первой ячейке нажать комбинацию «Ctrl + [», откроется лист, откуда взяты эти данные. Удобно — всегда знаешь, откуда берутся значения, при этом комбинация работает и открывает ссылки на другие книги Excel.

Курсы по теме:

#4. Связь с другими книгами Excel

Excel с лёгкостью может делать ссылки не только внутри одной книги, но и на другие книги Excel. Например, часто мне нужно использовать данные из другого файла — и сделать так, чтобы значения внутри моего файла обновлялись.

Как это сделать

1. Вписать «=» в ячейку, куда хотите добавить ссылку.

2. Перейти в другую книгу, щелкнуть по нужной ячейке. Так вы поставите ссылку на любое значение в другой книге Excel. Нажать Enter.

3. После чего ссылка будет выглядеть так: «=’[Книга1.xls]Лист1’!М7».

При этом внешними ссылками можно управлять с помощью специального меню на панели инструментов «Дата» > «Запросы и соединения» > «Редактировать ссылки».

В открывшемся меню будут отображены все файлы, на которые в данной книге сделаны ссылки. В этом меню можно изменить путь к файлу, обновить все ссылки и даже разорвать связь с файлом, что автоматически переводит все ссылки на этот файл в значения.

Excel и сам подсказывает, когда значения можно обновлять.

Если данные во втором файле изменились, вверху страницы появится предупреждение в формате «У вас есть линки на внешние источники, которые могут быть обновлены. Обновить?». Нажимаете «Да», и все линки в вашей табличке автоматически обновятся. Но это можно сделать и вручную — только для отдельных линков.

#5. Сделать запрос в Google Таблицу

Стартапы и небольшие компании часто работают в Google Таблицах. В эти файлы собираются данные из Google Аналитики или других сервисов в реальном времени, к ним есть онлайн-доступ сразу у нескольких человек из команды.

Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!

Но важно создать и мастер-файл с финальным отчетом в Excel — так у вас будет больше возможностей для визуализации, вы точно не потеряете некоторые значения и защитите свой файл от несанкционированного доступа.

Как это сделать

Чтобы получить данные из онлайн-файла, нужно:

1. Настроить доступ к файлу Google Таблицы — «Для всех, у кого есть ссылка».

2. Скопировать ссылку на Google Таблицу и кликнуть на вкладку «Данные».

3. Кликнуть на блок «Получить и преобразовать данные» слева на верхней панели Excel-файла.

4. Кликнуть на кнопку «Получить данные». Появится список возможных источников, к которым можно подключиться через Excel. Выбрать опцию «Из рабочей книги». Появится диалоговое окно.

5. Вставить ссылку на таблицу в строку «Имя файла» внизу диалогового окна. Важно! В конце ссылки вместо «edit» написать «export». Нажать «Открыть». Пару секунд ждем. Откроется новое диалоговое окно.

6. Выбрать в этом окне файл-таблицу и нажать кнопку «Загрузить в». Указать, где появится эта новая информация: например — «Таблицы» > «A1».

Теперь эти данные имеют прямую связь с выбранной Google Таблицей. Если захочется обновить данные в Excel, нужно кликнуть по таблице правой клавишей и нажать опцию «Обновить». Подтянутся новые данные.

Ограничения. Нельзя подключиться, если:

#6. Сделать одну таблицу из нескольких

Допустим, в одной папке у меня есть данные за несколько месяцев — и они разделены по файлам (один месяц — один файл). Мне нужно объединить их в один файл. Например, данные из таблицы «Январь» и «Февраль» собрать в одну.

Как это сделать

1. Переходим во вкладку «Данные» > «Из файла» > «Из папки». В появившемся окне выбрать путь к нужной папке.

2. Выбрать файлы из папки, которая отобразилась в окне. Нажать кнопку «Объединить и загрузить в». Появится окно с настройками объединенного файла.

3. Выбрать файл-пример, по которому будет организовываться информация. Задаем пример — выбираем его в этом же окне слева. Ждем.

4. Выбираем из предложенных опций, в каком виде будет выгружена информация. Например, в виде таблицы. Нажимаем «Ок».

Получаем одну таблицу, где данные «Январь» и «Февраль» — вместе. Если в общую папку положить еще и «Март», то просто нажимаем «Обновить», и в таблице появляются еще и данные за март.

#7. Сделать «умную» таблицу

Простой диапазон, имеющий табличный формат, — это еще не таблица. На ячейки такой «таблицы», к примеру, нельзя ссылаться. Чтобы диапазон стал настоящей — «умной» — таблицей, нужно выполнить одно маленькое действие.

Как это сделать

Нажимаем «Ctrl+T» внутри диапазона. Появляется диалоговое окно — в нем подтверждаем, что хотим создать таблицу.

Чтобы таблица выглядела аккуратнее и эстетичнее, выбираем цветовое форматирование на верхней панели. Но можно оставить и без него.

Один из плюсов «умных» таблиц в том, что они могут расширяться: если добавляется новое поле внизу, оно автоматически подтягивается в таблицу. С обычными таблицами такого не происходит.

#8. Сделать зависимую сводную таблицу

Из «умной» таблицы можно сделать сводную таблицу — чтобы быстрее и удобнее отображать данные и проводить вычисления.

Как это сделать

1. Во вкладке «Вставка» нажать кнопку «Сводная таблица».

2. Указать, куда поместить отчет сводной таблицы: «На новый лист» или «На существующий лист». Если выбрать «На существующий», нужно будет указать ячейку — там и будет сводная таблица.

3. Набрать поля в сводную таблицу. Справа появляется панель, там — список полей, значения которых «заберем» для сводной таблицы. Например, «магазины», «продукт», «объем продукта».

Если в «умной» таблице-источнике появится новый продукт, он автоматически добавится и в сводную таблицу. Для этого нужно будет просто обновить вторую таблицу: кликнуть правой клавишей мышки и выбрать «Обновить».

Если делать то же самое в обычной таблице, сводная автоматически обновляться не будет.

#9. Добавить «умный» срез

В Excel многие пользуются фильтрами, но срезы или шкалы умеют делать не все. Я использую их, чтобы оформить нужную информацию максимально наглядно. Срезы можно делать к «умной» и сводной таблицам.

Как это сделать

1. Перейти во вкладку «Дизайн». Кликаем по кнопке «Вставить срез» в левой части верхней панели.

2. Выбрать колонки для среза. Колонки в списке те же, что и в таблице — нужно галочками отметить нужные. Появляется интерактивный срез.

Срез помогает держать нужные данные под рукой. Кликаешь на значение в срезе — и по нему фильтруется главная таблица. Это быстрее и удобнее, чем обычные фильтры.

#10. СУММЕСЛИ и её старшая сестра СУММЕСЛИМН

К примеру, есть объем реализации товара в детализации по Дате, Магазину и Продукту — и нужно получить данные о продажах только по одному Магазину или по всем Магазинам, но только по определенному Продукту. Это не сложный кейс — здесь нам и поможет СУММЕСЛИ.

Есть и более сложный кейс. Например, найти реализацию на определенную Дату, по определенному Магазину и Продукту. В таком кейсе простая формула СУММЕСЛИ уже не подойдет — нужно обращаться за помощью к ее «старшей сестре» — СУММЕСЛИМН.

Как это сделать

1. Нажать на пустую ячейку — например, H5. В ней будет отображаться результат.

2. Указать нужный вам период. Например, ввести его в ячейку H2.

3. Указать нужный код продукта. Например, ввести его в ячейку H3.

4. Ввести формулу в ячейку Н5 «=СУММЕСЛИМН(E2:E48;A2:A48;H2;C2:C48;H3)».

В итоге получается сумма реализации на определенную Дату и определенный Продукт. Условий может быть больше, чем в примере, — максимум 255.