10 функций Excel, которые
вам нужны

За последние десять лет в мире появилось огромное количество инновационного программного обеспечения для обработки больших массивов данных: анализа, управления и визуализации информации. Тем не менее даже через 30 лет после своего появления Microsoft Excel остается незаменимым инструментом для информационных работников, которые хотят разобраться в полученных данных. Генеральный директор Microsoft Сатья Наделла считает, что Excel по-прежнему остается флагманским продуктом Microsoft, и 750 млн работников умственного труда по всему миру каждый день это подтверждают.

Вот уже десять лет мы тестируем Microsoft Excel и учим людей пользоваться этим продуктом. Опросив несколько сотен офисных работников, мы выяснили, что они проводят более 10% своей трудовой жизни, работая с таблицами. Для тех же, кто работает в сфере исследований и разработок или финансов, этот показатель приближается к 30% — а это 2,5 часа в день.

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

В прошлом году мы составили руководство «The Definitive 100 Most Useful Excel Tips» («100 самых полезных советов по работе в Excel»). Для этого мы консультировались с экспертами по программе и проанализировали десятки тысяч результатов тестирований и информацию об использовании Excel. Хотя у каждой его функции есть сценарий использования, никто не пользуется ими всеми. Из более чем 500 функций можно выбрать около 100 действительно полезных для большинства современных информационных работников. Мы выбрали десять наиболее легких для освоения команд (на это потребуется около 2 часов), которые могут существенно улучшить вашу продуктивность. Они приведены в порядке убывания полезности. Если вы не знаете, какие функции освоить в первую очередь, попробуйте применить матрицу 2х2, которая учитывает пользу и количество времени, необходимое для освоения навыка.

Специальная вставка (10 минут на освоение)

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

Исправление этих мелких неприятностей отнимает время, поэтому «специальная вставка» такая ценная функция. Она позволяет выбрать, какие элементы выделенной ячейки вы хотите вставить в новую. После того, как вы скопировали ячейку (Ctrl+C), нажмите Ctrl+Alt+V (можно использовать раздел «Главная» > «Вставить» > «Специальная вставка» на панели инструментов или в меню правой кнопки мыши), чтобы активировать команду «Специальная вставка» и выбрать необходимые элементы для вставки. Самый частый случай использования специальной вставки — копирование только значений ячеек, сделать это можно, нажав Alt+E+S+V.

Добавление нескольких строк одновременно (2 минуты на освоение)

Часто возникает необходимость добавить несколько новых строк между существующими. Комбинация Ctrl+Shift+плюс — удобный способ сделать это, тем более что каждую новую строку вы можете добавить нажатием на клавишу «плюс». Другой быстрый способ — выделить количество строк, которое вы хотите добавить (например, пять), и, нажав правую кнопку мыши, выбрать «Вставить». Это особенно удобно, когда вам нужно добавить большое количество строк.

Мгновенное заполнение (30 минут на освоение)

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

Предположим, вы внесли список номеров продуктов в первые 10 ячеек столбца A, от «ABC-00001» до «ABC-00010», и вам нужны только цифры после «-». Благодаря мгновенному заполнению вы можете быстро избавиться от «ABC». В версии до 2013 года такое тоже было возможно, но с помощью комбинации функций. Теперь эта команда стала намного быстрее и сможет впечатлить пользователей.

Задайте шаблон, напечатав «00001» в первой пустой ячейке соседнего столбца. Если у вас включена возможность мгновенного заполнения («Файл» > «Параметры» > «Дополнительно»), просто начните вводить номер следующего продукта в следующую ячейку — и мгновенное заполнение распознает закономерность и «допишет» оставшиеся номера за вас. Нажмите клавишу Enter, чтобы принять изменения. Можно запустить функцию вручную, выбрав «Данные» > «Мгновенное заполнение» или воспользовавшись комбинацией Ctrl+E.

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

«ИНДЕКС» и «ПОИСКПОЗ» (45 минут на освоение)

Не считая функции «ВПР», позволяющей по значению одной ячейки найти соответствующее значение в другой, «ИНДЕКС» и «ПОИСКПОЗ» — самые используемые и эффективные инструменты Excel для поиска. Они очень полезны и по отдельности, но их настоящая мощь становится очевидной лишь при совместном применении. «ИНДЕКС» и «ПОИСКПОЗ» используются в связке, чтобы быстро и точно извлекать необходимую информацию из обширного массива данных. Освоение этих функций не только превратит вас в гуру Excel в глазах коллег и начальства, но и сделает утомительную рутинную задачу быстрой и простой. Вот как они работают:

«ВПР» — прекрасный инструмент, но у него есть ограничения. Он может искать значения лишь слева направо. Значение, которое нужно найти, должно находиться слева в таблице. «ИНДЕКС» и «ПОИСКПОЗ» позволяют искать значения везде в таблице, вне зависимости от их расположения.

Допустим, у вас есть таблица со списком продуктов. В ней есть столбцы, озаглавленные: «Номер продукта», «Прибыль», «Имя продукта» и «Доход». На другом листе таблицы приведен список названий продуктов, и вы хотите посмотреть, сколько прибыли приносит каждый из них. В таком случае необходимо обратиться к имени продукта (искомое значение), чтобы найти показатели прибыли. Имя находится справа от графы «прибыль», поэтому «ВПР» не сработает. Наступает идеальный момент для использования функций «ИНДЕКС» и «ПОИСКПОЗ».

Вот как будет выглядеть функция:

=ИНДЕКС (Столбец «Прибыль»; ПОИСКПОЗ (Искомое значение; Столбец «Наименование продукта»; о))

А вот как это запомнить:

=ИНДЕКС (Столбец, из которого мне нужно извлечь значение; ПОИСКПОЗ (искомое значение; столбец, с которым я хочу сравнить; 0)). 0 дает точное значение, вы можете также сравнить показатели с меньшим (-1) или большим (1) значениями.

На первый (и даже на второй) взгляд, «ИНДЕКС» и «ПОИСКПОЗ» — сложные функции. Несомненно, вам понадобится практика, но освоение этих операций того стоит: они гораздо более гибкие и эффективные, чем ВПР. Эти функции — одно из главных применений Excel.

СУММ (2 минуты на освоение)

Скорее всего, это одна из первых освоенных вами функций Excel — суммирование значений ячеек в строке или столбце. Но знали ли вы, что можете выбрать ячейку в конце строки или столбца и нажать Alt+плюс, чтобы найти сумму за считанные секунды?

Ctrl+Z / Ctrl+Y (1 минута на освоение)

Если вы еще не пользуетесь комбинацией Ctrl+Z, чтобы отменять совершенные в Excel ошибки, начинайте прямо сейчас. А заодно запомните, что комбинация Ctrl+Y выполняет противоположное действие — возврат. Два сочетания клавиш прекрасно работают вместе, позволяя вам выбирать между итерациями своей работы, пока вы не остановитесь на нужной.

Удалить дубликаты (10 минут на освоение)

Это очень простая и быстрая в использовании функция. «Удалить дубликаты» делает именно то, что вы от нее ожидаете — удаляет повторяющиеся значения в любом выбранном интервале. Мы советуем, устраняя повторы, помещать их на другие листы . Эту команду можно найти в разделе «Работа с данными» во вкладке «Данные» панели инструментов.

Если вы хотите лишь выделить дубликаты, вам поможет команда «Условное форматирование». Комбинация клавиш — Alt+H+L (или вкладка «Главная» > «Условное форматирование» в панели инструментов).

Закрепить области (15 минут на освоение)

Когда приходится прокручивать длинную таблицу с информацией, часто забывается, где какой столбец. Решение проблемы — функция «Закрепить области». Вы можете закрепить только верхнюю строку, первый столбец или любой нужный диапазон. Определите столбцы и строки, которые вы хотите закрепить. Затем выделите ячейку справа от нужных столбцов или под необходимыми строками. В разделе «Окно» вкладки «Вид» выберите «Закрепить области». Комбинация клавиш — Alt+W+F.

F4 (10 минут на освоение)

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

CTRL + клавиши-стрелки (5 минут на освоение)

Если вы хотите добраться до финальных значений таблицы и используете для этого колесико мышки, остановитесь и начните пользоваться комбинацией Ctrl+клавиши-стрелки. Это простое сочетание клавиш быстро перенесет вас в конец столбца или строки. Вы можете также использовать комбинацию Ctrl+Shift+клавиши-стрелки, чтобы за считаные секунды выделить обширные области данных.

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

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

Об авторах

Адам Лейси (Adam Lacey) — управляющий директор компании Excel With Business, которая занимается онлайн-обучением навыкам работы в Excel и Microsoft Office.

Дебора Эшби (Deborah Ashby) — менеджер службы профессионального обучения в Excel With Business и Filtered.com. Сертифицированный тренер по работе в Microsoft Office.