20 вещей, которые нужно сделать в Excel, чтобы стать экспертом

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

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

20 вещей, которые нужно делать в Excel, чтобы стать экспертом

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

1. Таблицы Pivot

Сводные таблицы позволяют обобщать данные. Они особенно полезны при работе с большими таблицами данных. Они избавляют вас от необходимости создавать множество итоговых вычислений. Проще всего создать сводную таблицу с помощью Microsoft Excel: 

• Убедитесь, что таблица или электронная таблица, для которой вы хотите создать разворотную таблицу, выбрана.
• Перейдите на вкладку Вставка
• Выберите Рекомендуемые разворотные таблицы

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

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

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

2. Управление дубликатами

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

Это отличные альтернативы кропотливому и ручному поиску дубликатов самостоятельно. Зная, как управлять дубликатами данных в Excel, вы будете выглядеть настоящим экспертом.  

3. Использование VLOOKUP (вертикальный поиск)

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

Функция VLOOKUP принимает четыре параметра:

• Значение: Значение поиска
• Column_Range: Таблица или диапазон столбцов данных, по которым выполняется поиск.
• Column_Number: Номер столбца, из которого должно быть возвращено соответствующее значение.
• Approximate_Match: Булев аргумент, который указывает функции искать либо точное совпадение (0 или FALSE), либо приблизительное совпадение (1 или TRUE). Это необязательный аргумент, который по умолчанию имеет значение TRUE. 

 Таким образом, окончательная структура функции будет выглядеть следующим образом: VLOOKUP(Value; Column_Range; Column_Number; Approximate_Match)

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

В последних версиях Excel есть мастер, который поможет вам создать такие функции, как VLOOKUP. Все, что вам нужно сделать, это нажать на кнопку формул (fx) и выбрать VLOOKUP из списка.   

Мастер даст вам подробное объяснение каждого требуемого параметра. Хотя ввод функции с нуля может заставить вас выглядеть настоящим экспертом Excel, использование мастера вставки функции гораздо проще. 

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

4. Работа с несколькими файлами Excel

Вы можете одновременно открыть несколько файлов Excel в разных экземплярах Excel, выбрав файлы в проводнике файлов и нажав Enter на клавиатуре. Чтобы быстро переключаться между экземплярами/файлами Excel, просто нажмите CTRL + TAB на клавиатуре.

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

5. Группировка и разгруппировка рабочих листов

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

• Удерживайте кнопку Ctrl на клавиатуре 
• Выберите все электронные таблицы, которые необходимо сгруппировать
• Отпустите кнопку Ctrl

Если вы вставите значение в любую ячейку любой из сгруппированных электронных таблиц, Excel обновит все электронные таблицы с этим значением в той же ячейке.

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

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

6. Использование стоп-панелей

В Excel есть функция стоп-панели, которая позволяет замораживать столбцы и строки. Это облегчает сравнение и сопоставление данных. Чтобы получить доступ к панели замораживания, выполните следующие действия:

• Выберите ячейку/строку под строкой (строками), которую вы хотите заморозить.
• Перейдите на вкладку ПРОСМОТР
• Нажмите на опцию «Панели замораживания
• Выберите Freeze Panes из контекстного меню

Строка над выбранной ячейкой/строкой будет заморожена при прокрутке электронной таблицы. В качестве альтернативы можно выбрать Freeze Top Row или Freeze First Column в меню Freeze Panes. Freeze Top Row заморозит первую строку для вертикальной прокрутки, а Freeze First Column заморозит первый столбец для горизонтальной прокрутки.

Чтобы разморозить панели, нажмите на опцию Unfreeze Panes в меню Freeze Panes.         

7. Функция ЕСЛИ

Функция ЕСЛИ в Excel может сделать ваши данные более динамичными. Это функция, которая указывает Excel ввести или вернуть значение, если оно удовлетворяет определенному условию. Она принимает три параметра:

• Логический_тест: Условие проверки, которое может быть подтверждено как истинное или ложное.
• Значение_если_истина: значение, которое Excel должен использовать, если логический тест истинен.
• Vaue_if_false: Значение, которое Excel должен использовать, если логический тест ложен.

Логический тест сравнивает два значения или ячейки с помощью символов «равно» (=), «больше» (>) или «меньше» (<), например, = ЕСЛИ (C2>C3; «Да», «Нет»).

8. Использование функции «Сумма

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

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

Синтаксис функции Sum может выглядеть следующим образом:

• =Сумма(1;2): Складывает два простых числа. На выходе получится 3.
• =Sum(A1;CB): Складывает содержимое двух ячеек вместе.
• =Sum(A1;2): Добавляет содержимое ячейки к числу. 
• =Sum(A1:CB): Складывает все числа между (и включая) этими двумя индексами.
• =Sum(A1:CB;5): Складывает все числа в диапазоне ячеек и добавляет к сумме еще одно указанное число.
• =Sum(A1:CB; G7:G1): Сложить все числа в двух разных диапазонах индексов ячеек.  

Это лишь некоторые из вариаций. Это удивительно гибкая функция.

Если вы хотите выглядеть еще более внушительно, вы можете использовать функцию SUMIF, которая объединяет функцию SUM с функцией IF.                  

9. Использование функции ИНДЕКС

Функция ИНДЕКС возвращает значение по указанному индексу в таблице или разделе таблицы. Она позволяет искать определенные значения в электронной таблице. Она существует в двух формах:

• Форма массива — в качестве первого аргумента принимает массив. Вторым аргументом является номер строки, а третьим — номер столбца. Массив строится с использованием диапазона ячеек, в котором вы хотите искать в вашей электронной таблице, т.е. A1:C20. Синтаксис функции выглядит следующим образом:
  =INDEX(array;row_num;col_num)
  =INDEX(A1:C20; 2; 3)

• Ссылочная форма — В качестве первого аргумента принимает ссылку. Ссылка позволяет вводить более одного диапазона или набора массивов. Второй аргумент — это номер строки, а третий — номер столбца. Четвертый позволяет выбрать, в каком массиве производить поиск. Синтаксис функции выглядит следующим образом:
  =INDEX((ссылка); row_num; col_num;area_num)
  например.
  =INDEX((A1:C9;D1:F8);2;3;1)

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

10. Использование функции MATCH

Функция MATCH — это еще одна функция поиска, используемая для нахождения индекса значения в одномерном массиве/списке (таблице, состоящей из одного столбца или строки). Она состоит из трех аргументов:

• Поиск_Значения: Значение, индекс которого вы ищете.
• Диапазон_массива: Массив или диапазон ячеек, по которым вы хотите провести поиск — для работы должен состоять из одного столбца или строки, т.е. A1:A9 или A1:E1.
• Match_Type: Необязательный параметр, определяющий, должна ли функция найти точное совпадение для искомого значения. Принимает одно из следующих значений: 
  • -1: Ищет наибольшее значение, которое меньше или равно искомому значению
  • 0: Ищет точное совпадение с искомым значением
  • 1: Ищет наименьшее значение, которое больше или равно искомому значению.

Синтаксис функции выглядит следующим образом: 

=MATCH(Search_Value; Array_Range;Match_Type)
например.
=MATCH(1; A1:A5;0)

11. Условное форматирование

Условное форматирование, как и диаграммы, позволяет визуализировать данные с помощью цветового кодирования, основанного на значении ячейки. Чтобы использовать условное форматирование, сделайте следующее:

• Выделите все ячейки, которые необходимо отформатировать 
• Перейдите на вкладку Главная
• Нажмите на Условное форматирование 

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

12. Использование валидации данных

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

• Выберите ячейку или группу ячеек, для которых необходимо выполнить проверку данных.
• Перейдите на вкладку Данные
• Нажмите кнопку Проверка данных

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

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

13. Использование ручки «Заполнить» для копирования формул

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

Для примера, если я произведу вычисления в ячейке C1, где я умножу содержимое ячейки A1 на содержимое B1 (=A1*B1), и перетащу ручку заполнения в ячейку C2, она умножит содержимое ячейки A2 на B2 (=A2*B2).

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

14. Переключение между относительными, абсолютными и смешанными ссылками на ячейки

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

Однако если вы хотите получить больше контроля над тем, как Excel копирует вычисления, вы можете использовать абсолютные ссылки на ячейки. Это позволит вам диктовать некоторые операнды, которые будут копироваться. Таким образом, можно скопировать динамическую переменную на фиксированное значение. Например, вы можете захотеть, чтобы все ячейки в столбце A были умножены на содержимое ячейки B1 (и только B1).

Для этого нужно использовать знак доллара ($), чтобы заблокировать ячейку. Если вы умножаете каждую ячейку столбца A на B1, синтаксис вычислений будет выглядеть следующим образом (=A1*B$1). Когда вы перетащите ручку заполнения, каждая затронутая ячейка в столбце умножит свое содержимое на B1 (=A2*B$1, =A3*B$1 и т.д.).

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

15. Использование функции «Специальная вставка

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

Чтобы получить доступ к специальной функции вставки, выполните следующие действия:

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

Если вы правильно выполнили описанные выше действия, вас должно встретить это диалоговое окно: 

16. Транспонирование данных в Excel

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

• Функция «Транспонировать
• Функция транспонирования Paste Special 

Самым простым способом является использование опции транспонирующей вставки. Однако освоение функции Transpose может показаться гораздо более впечатляющим.    

17. Использование функции TRIM

Функция TRIM позволяет удалять из данных Excel нежелательные пробелы. Функция Trim требует только один аргумент (текст). Она копирует и вводит транспонированные данные в ячейку.

Синтаксис функции trim выглядит следующим образом:

=TRIM(Text)
например.
=TRIM(A1)
=TRIM(» hello world»)

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

18. Использование макросов

Если вы хотите стать настоящим экспертом Excel, вам необходимо освоить макросы. Макросы позволяют автоматизировать повторяющиеся задачи в Excel путем добавления элементов программирования с помощью кода Visual Basic. Вы даже можете использовать макросы для расширения некоторых функциональных возможностей Excel. 

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

Для подробного описания макросов потребуется целая статья. К счастью для вас, в Интернете есть множество материалов, которые помогут вам достичь базового понимания макросов.   

19. Объединение функций

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

Например, вы можете использовать функцию MATCH в качестве номера строки или столбца функции INDEX, т.е.
=INDEX(Array_Range; MATCH(Search_Value; Array_Range;Match_Type);Column_Number)
=INDEX(A1:C12;MATCH(«Размер здания»;A1:A6);1) 

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

20. Ярлыки клавиатуры

Использование сочетаний клавиш в Excel, несомненно, поможет вам выглядеть экспертом. Однако это не только поможет вам выглядеть круто, но и сэкономит ваше время. 

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

Заключение 

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

В приведенном выше тексте мы рассмотрели 20 вещей, которые можно делать в Excel и которые сделают вас экспертом. Есть ли у вас собственные советы или хитрости? Оставьте их в разделе комментариев ниже. Как всегда, спасибо за чтение.