Топ-5 скрытых и полезных действий в Excel, которые сильно упростят вам жизнь

Использование закладок для навигации в Excel

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

Excel имеет такую же возможность. Одним щелчком мыши мы можем перенестись в самые важные места в нашей рабочей книге.

Если говорить полностью, то в Excel нет функции "закладки", но мы можем перепрофилировать существующую функцию для решения той же задачи. Эта функция - Поле имени.

Поле имени (фото: XelPlus)

Поле имени, расположенное в верхнем левом углу рабочего листа, предоставляет множество полезных функций, таких как:

• Отображение адреса текущей выбранной ячейки
• Перейти к местоположению ячейки
• Перейти к официальной таблице Excel
• Выделить целый столбец или строку ячеек
• Выделить диапазон ячеек
• Присвоить имена отдельным ячейкам или диапазонам ячеек
• Обеспечить мгновенный выбор именованных отдельных ячеек или диапазонов ячеек

Именно эти два последних пункта позволят нам "отмечать" важные места в рабочей книге.

Процесс добавления в закладки:

• Выберите ячейку, содержащую важную и часто посещаемую информацию
• Щелкните в поле имени (это окрасит отображаемый в данный момент адрес в синий цвет)
• Удалите (или нажмите клавишу backspace) текущую информацию
• Введите название вашей закладки
• Нажмите Enter

Последний шаг, нажатие Enter, очень важен. Многие новички совершают ошибку, нажимая другую ячейку или кнопку на ленте после выполнения 4-го шага. Если вы не нажмете клавишу Enter, запись будет отменена.

Существует несколько ограничений на наименование:

• Никакие две ячейки (или диапазона ) не могут иметь одинаковое имя
• Имя должно начинаться с буквы (но может содержать цифры)
• Пробелы в имени не допускаются (но вы можете использовать символ подчеркивания для имитации пробела)
• Чтобы использовать закладку, щелкните небольшую стрелку вниз справа от поля имени, чтобы отобразить список именованных диапазонов

Если вам необходимо управлять своими "закладками", выберите Формулы (вкладка) - Определенные имена (группа) - Менеджер имен. Откроется диалоговое окно, в котором вы сможете добавлять, изменять или удалять именованные диапазоны.

Как использовать закладку "Поле имени" (фото: XelPlus)

Анализируйте данные для мгновенного появления идей

Этот следующий способ экономии времени ранее был известен как "Идеи", но недавно был переименован в "Анализ данных". Эта функция доступна пользователям Microsoft 365 и использует возможности искусственного интеллекта.

Предположим, вы только что получили следующий набор данных. У вас есть данные о продажах за 12 месяцев, и вы хотите создать отчет, отображающий 5 продуктов с самым низким рейтингом за весь период. Этот отчет можно сделать в 2 клика.

• Функция “Анализ данных” расположена в правой части ленты “Главная”
• Откроется панель “Анализ данных”, на которой для вашего просмотра будет представлено несколько предлагаемых отчетов
• Поскольку наш вопрос достаточно конкретен, мы можем ввести его в поле “Задайте вопрос о своих данных” в верхней части панели
• Если вы удовлетворены результатом, нажмите кнопку “Вставить сводную таблицу”, чтобы создать сводную таблицу на новом листе

Анализ данных (фото: XelPlus)

Прогнозирование в один клик

Прогнозирование - обязательный навык при работе с данными о продажах или доходах. Инструмент “Лист прогноза” появился в Excel 2016 и находится в группе “Прогноз” на ленте “Данные”

Чтобы использовать инструмент “Лист прогноза”, выберите диапазон ячеек, содержащий даты и исторические данные о продажах (CTRL-A), затем нажмите кнопку “Лист прогноза”

Будущие продажи прогнозируются с учетом сезонного поведения. Мы также видим линии для верхней и нижней границ ожидаемого поведения.

Окончание периода прогнозирования можно скорректировать вместе с другими параметрами, такими как уровень достоверности, сезонность и обработка дублирующихся данных.

Прогнозирование (фото: XelPlus)

Инструмент быстрого анализа Excel

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

Установив курсор в диапазоне данных, нажмите CTRL-A , чтобы выделить данные, затем нажмите CTRL-Q, чтобы активировать инструмент быстрого анализа.

Отсюда вы можете одним щелчком мыши выполнить следующие задачи:

• Форматирование - применяйте условное форматирование на основе правил “сверху/снизу” или процентов, гистограмм, цветовых шкал, наборов значков и т. д
• Диаграммы - создавайте разнообразные диаграммы на основе анализа данных
• Итоги - создавайте итоги, средние значения или подсчеты в строках и столбцах
• Таблицы - создание таблиц данных Excel или сводных таблиц
• Спарклайны - добавляйте в ячейки спарклайны (мини-диаграммы на основе ячеек), которые отображают линии, столбцы или информацию о выигрышах/проигрышах

Любую из этих опций можно предварительно просмотреть, просто наведя указатель мыши на нее.

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

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

Инструмент быстрого анализа Excel (фото: XelPlus)

Power Query (максимальная экономия времени)

Power Query может использовать любой, кто работает с данными: в любом формате и для любых целей.

Если вам когда-либо понадобится перенести данные в Excel из внешних источников (например, текстовых файлов с разделителями, других файлов Excel, PDF-файлов, баз данных, веб-сайтов, папок и т. д.), а затем исправить или очистить данные, Power Query станет для вас идеальным инструментом для экономии времени.

Power Query находится на вкладке “Данные” слева в группе под названием “Получить и преобразовать”.

Отсюда вы можете создать запрос для импорта данных из десятков различных источников.

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

Действия Power Query:

• Создание сводных таблиц
• Объединение таблиц
• Выполнение сложных операций поиска
• Разделение данных
• Объединение данных
• Написание формул агрегации
• Группировка данных
• Отмена свертывания данных
• Корректировка дат

И самая удивительная часть Power Query - это возможность его повторного использования. Функция записывает все преобразования, выполняемые вами в источнике данных, и превращает их в повторяемый код.

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

Power Query (фото: XelPlus)