Если вам интересна эта статья, то вам точно понравится наш обширный список формул в Excel
Данные — золото для любого бизнеса. Но, как правило, они организованы в файлы, в которых еще нужно разобраться. 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, нужно кликнуть по таблице правой клавишей и нажать опцию «Обновить». Подтянутся новые данные.
Ограничения. Нельзя подключиться, если:
- Доступ к Google Таблице ограничен — по ссылке ее не открыть.
- В Google Таблице есть объединенные колонки.
- В Google Таблице есть сводные таблицы.
- Есть графики.
#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.
Хотите получать дайджест статей?
