25 лучших навыков работы с EXCEL – все уровни (базовый | средний | продвинутый) + тест

В наши дни независимо от того, кем вы работаете – бухгалтером, аналитиком данных, менеджером по персоналу, менеджером по продукту, – владение навыками работы в Excel дает огромные преимущества.

Когда мы говорим о навыках работы в Excel, мы можем разделить их на 3 уровня:

• Базовые навыки Excel
• Промежуточные навыки Excel
• продвинутые навыки Excel

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

Базовые навыки Excel

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

1. Сохранение и открытие рабочей книги

Сохранение и открытие рабочей книги Excel происходит точно так же, как и в любом другом приложении.

Когда вы щелкаете на вкладке “Файл”, появляется возможность сохранить файл.

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

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

Таким же образом, если вы хотите открыть файл, который сохранен в системе, вы можете перейти на вкладку ФАЙЛ и нажать на кнопку открыть.

Появится диалоговое окно открытия, в котором вы можете найти файл и открыть его.

2. Управление рабочими листами

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

Чтобы вставить новый рабочий лист, просто нажмите на кнопку с плюсом или воспользуйтесь сочетанием клавиш SHIFT + F11.

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

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

3. Форматирование ячеек

В Excel на главной вкладке есть множество опций для форматирования.

1. Группа шрифтов дает возможность форматировать шрифт, делая его полужирным, курсивным и подчеркиванием. Вы можете изменить размер шрифта, цвет шрифта и цвет ячеек.
2. В группе выравнивания можно задать выравнивание текста, добавить отступ, объединить и разделить ячейки, а также обернуть текст.
3. В группе номеров можно применить формат учета, формат текста, формат номеров с запятой или без нее и т.д.

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

4. Printin

SUM: С помощью этой функции можно суммировать значения из нескольких ячеек, а также вводить значения непосредственно в функцию.

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

СЧЁТЕСЛИ: Эта функция возвращает среднее значение числовых значений. Вы можете ссылаться на ячейки, в которых находятся значения, или просто вставить их в функцию.

ВРЕМЯ: Возвращает действительный порядковый номер времени в соответствии с форматом времени Excel. Необходимо указать часы, минуты и секунды.

1. ДАТА: возвращает действительную дату (порядковый номер даты) в соответствии с форматом времени Excel, используя указанные день, месяц и год.
2. СЛЕВА: Извлекает определенные символы из ячейки/строки, начиная слева (начало). Необходимо указать текст и количество символов для извлечения.
3. ПРАВЫЙ: Извлекает определенные символы из строки, начиная с правого (последнего). Необходимо указать текст и количество символов для извлечения.
4. VLOOKUP: ищет значение в столбце и может вернуть это значение или значение из соответствующих столбцов, используя тот же номер строки.
5. IF: Функция IF (проверяет условие) возвращает значение, если определенное условие TRUE, и возвращает другое значение, если условие FALSE.
6. СЕЙЧАС: Возвращает текущую дату и время (с использованием настроек вашей системы) в ячейке, куда вы ее вставили.
7. А вот 100 лучших функций Excel, чтобы вы узнали больше.
8. 6. Диаграммы
9. Как только вы начинаете работать с данными, вероятно, вам нужно их кому-то представить, и диаграмма – один из лучших способов сделать это.
10. В Excel у вас есть целый список диаграмм, которые вы можете создать. Перейдя на вкладку Вставка, в группе диаграмм вы найдете все диаграммы, которые можно использовать.

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

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

Но вы также можете использовать линейный график, если хотите показать тенденцию за 12 месяцев.

Но ниже у вас есть данные по отдельным продуктам.

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

Вы также можете использовать кнопку “Рекомендуемые диаграммы”, чтобы Excel рекомендовал тип диаграммы в соответствии с имеющимися данными.

Вам просто нужно выбрать данные и нажать на кнопку рекомендуемой диаграммы.

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

7. Сортировка данных

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

Теперь вам нужно определить 3 параметра:

Сортировать по : Столбец, который будет использоваться в качестве основы для сортировки.

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

Порядок : Порядок сортировки или пользовательский порядок.

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

1. 8. Поиск и замена
2. Как и любое другое приложение, Excel имеет свою собственную опцию поиска и замены. Клавиша быстрого доступа для открытия опции поиска – Control +F, а для поиска и замены – Control + R .
3. В строке ввода “Найти что” введите значение, которое вы хотите найти, а в строке “Заменить на” введите значение, на которое вы хотите заменить.

Если вы хотите заменить или найти все значения, используйте кнопки “Заменить все” или “Найти все”, в противном случае вы можете использовать кнопки “Найти далее” и “Заменить” для одного значения.

После нажатия на кнопку “Параметры” вы увидите, что есть некоторые дополнительные параметры, которые вы можете использовать.

Выполнить поиск и замену с учетом регистра.

Поиск и замена во всей рабочей книге.

Поиск и замена с использованием формата ячеек.

Также можно найти значения из комментариев и примечаний.

1. 9. Специальная опция вставки
2. Специальная опция вставки дает вам полный контроль над тем, как вы хотите вставить значения в ячейку.
3. Чтобы открыть специальную опцию, нужно перейти на вкладку Главная, затем щелкнуть на выпадающем списке “Вставить” и нажать на специальную опцию вставки (Вы также можете открыть специальную опцию вставки из меню правой кнопки мыши).
4. В диалоговом окне “Специальная вставка” у вас есть несколько вариантов, которые вы можете использовать.

Допустим, у вас есть ячейка, в которой есть формула, но вы хотите скопировать и вставить только значение из этой ячейки. В этом случае просто скопируйте эту ячейку и используйте опцию “Значения” из специальных опций вставки в целевую ячейку.

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

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

Начиная с Excel 2007 и заканчивая Excel 2019, вы можете найти сочетание клавиш, нажав клавишу ALT. При ее нажатии отображаются сочетания клавиш для опций, которые находятся на ленте, как показано ниже.

Отсюда вы можете скачать (“Шпаргалка по сочетаниям клавиш Excel”), в которой описаны восемьдесят два лучших сочетания клавиш для Excel.

Другие уроки по базовым навыкам Excel

Промежуточные навыки работы в Excel

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

11. Перейти к специальному

Опция GO TO SPECIAL помогает перейти к определенной ячейке или диапазону ячеек в рабочем листе. Чтобы открыть его, нужно перейти на вкладку Главная ➜ Редактирование ➜ Найти и выбрать ➜ Перейти к специальному.

Промежуточные навыки Excel

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

Таким же образом, если вы хотите выбрать ячейки, в которых есть формулы и возвращенные числа, вам нужно выбрать формулы, затем отметить галочкой числа, а затем нажать OK.

12. Pivot Table

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

Сначала перейдите на вкладку Вставка и нажмите на кнопку Сводная таблица.

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

После нажатия кнопки OK появится боковая панель, как показано ниже, где вы можете определить строки, столбцы и значения для поворотной таблицы, которые можно просто перетаскивать. А теперь добавьте “Возраст” в строки, “Образование” в столбец и “Имя” в значения.

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

• Подробнее о разворотных таблицах

• 13. Именованный диапазон

• Именованный диапазон – это присвоение имени ячейке или диапазону ячеек. В Excel каждая ячейка имеет свой адрес, который представляет собой комбинацию строки и столбца.

Но с помощью именованного диапазона вы можете дать этой ячейке или диапазону ячеек определенное имя (Generic), а затем использовать это имя для ссылки на нее.

Допустим, у вас есть процент налога в ячейке A1, и теперь вместо использования ссылки вы можете дать ему имя и затем использовать это имя в каждом расчете.

Чтобы создать именованный диапазон, вам нужно перейти на вкладку Формулы ➜ Определить имена ➜ Определить имя.

Теперь в диалоговом окне “Определить имя” нужно задать следующие параметры:

Имя диапазона.

Область использования диапазона во всей рабочей книге или только в рабочем листе.

• Комментарий, если вы хотите его добавить.

• И затем адрес ячейки или диапазона.

1. После нажатия кнопки OK Excel присвоит это имя ячейке A1, и вы сможете использовать его в формулах для ссылки на ячейку A1.
2. Таким же образом можно создать именованный диапазон для диапазона ячеек и затем ссылаться на него в формулах.
3. 14. Выпадающие списки
4. Выпадающий список – это, по сути, предопределенный список значений, который поможет вам быстро ввести данные в ячейку. Чтобы создать выпадающий список, нужно перейти на вкладку Данные ➜ Инструменты данных ➜ Проверка данных ➜ Проверка данных ➜ Проверка данных.

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

В конце нажмите кнопку OK

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

Подробнее о выпадающих списках

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

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

Допустим, вы хотите выделить все дублирующиеся значения из диапазона ячеек, вам просто нужно перейти на вкладку Главная ➜ Условное форматирование ➜ Правила выделения ➜ Дублирующиеся значения.

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

16. Таблица Excel

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

Чтобы преобразовать обычные данные в таблицу Excel, достаточно воспользоваться сочетанием клавиш Control + T или перейти на вкладку Вставка ➜ Таблица.

17. Кнопка “Идея

Если вы используете Office 365, вы можете получить доступ к новой кнопке Idea Button, представленной Microsoft, которая поможет вам легко анализировать данные, рекомендуя возможные способы их создания:

Таблицы Pivot

Диаграммы трендов

Диаграмма распределения частот

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

• Это займет несколько секунд, чтобы проанализировать данные и затем показать вам список возможных результатов.
• 18. Использование Sparklines
• Линии искр – это маленькие диаграммы, которые можно вставить в ячейку на основе диапазона данных.

Чтобы вставить линию искр, нужно перейти на вкладку Вставка ➜ Линии искр.

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

При нажатии на кнопку “Сверка” отображается ди

Посмотрите на приведенную ниже таблицу, где есть столбец с именами, а между именем и фамилией стоит пробел.

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

Сначала перейдите на вкладку Данные и нажмите на Текст в столбец.

Теперь в диалоговом окне выберите разделитель и нажмите кнопку Далее.

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

В завершение нажмите кнопку Далее, а затем кнопку Готово.

После того как вы нажмете кнопку “Готово”, он преобразует один столбец полных имен в два разных столбца (имя и фамилия).

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

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

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

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

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

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

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

Таблица: На этой вкладке можно вставить поворотную таблицу с выбранными данными, а также применить к ней таблицу Excel.

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

Расширенные навыки Excel

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

• 21. Продвинутые формулы Excel

• СЛОЖНАЯ ФОРМУЛА EXCEL (список формул Excel) означает объединение различных функций для вычисления определенного значения, которое невозможно вычислить иным способом. Ниже приведен список некоторых наиболее важных из них:

• Примеры расширенных формул

• 22. Расширенные диаграммы Excel

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

продвинутые навыки Excel

Примеры расширенных диаграмм

23. Visual Basic для приложений

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

Учебные пособия по VBA

24. Power Query

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

Power Query и не забудьте научиться объединять несколько файлов Excel.

25. Power Pivot

Power Pivot – это техника моделирования данных, которую вы можете использовать для импорта данных из миллионов строк из нескольких источников и последующего выполнения вычислений (DAX).

клик-поворот-таблица

insert-pivot-table-dialog-box

pivot-table-field-list

новая поворотная таблица

отчет-предложение-идея-кнопка-1

learn-power-query