Урок 9. Полезные советы для эффективной работы в Excel

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

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

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

Содержание:

• Горячие клавиши для ускорения работы
• Как защитить данные на листе или в файле с помощью пароля?
• Советы по организации рабочего пространства в Excel
• Часто задаваемые вопросы о работе в Excel
• Проверочный тест

Мы возьмем за основу материал «Сочетания клавиш в Excel» [Microsoft, 2021] и воспользуемся подсказками из материала «Основные задачи в Excel» [Microsoft, 2022].

Горячие клавиши для ускорения работы

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

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

Горячие клавиши для работы с текстом:

• Ctrl + B – полужирный текст.
• Ctrl + I – курсив.
• Ctrl + U – подчеркнутый текст.
• Ctrl + 5 – зачеркнутый текст.
• Ctrl + Shift + F – открытие окна форматирования шрифта.

Горячие клавиши для работы с числами:

• Ctrl + Shift + 1 – формат числа с разделителями (числа после запятой).
• Ctrl + Shift + 2 – очистить ячейку от числа.
• Ctrl + Shift + 3 – формат даты.
• Ctrl + Shift + 4 – формат даты.
• Ctrl + Shift + 5 – формат числа в процентах.
• Ctrl + Shift + 6 – формат времени.

Горячие клавиши для работы с формулами:

• Alt + = для вставки функции «Автосумма».
• Ctrl + ` чтобы показать формулы в таблице вместо результатов.
• Ctrl + Shift + Enter для вставки массива формул (для формул массива).
• F4 для переключения между абсолютными и относительными ссылками в формулах (например, $A$1, A$1, $A1, A1).

Горячие клавиши для работы с листами:

• Shift + F11 – создать новый лист.
• Ctrl + Page Up / Page Down – переключение между листами в книге.
• Alt + O + H + R – переименовать текущий лист.
• Ctrl + Shift + K – создать дубликат листа.

Горячие клавиши для работы с ячейками:

• Ctrl + C – копировать содержимое ячейки.
• Ctrl + V – вставить содержимое.
• Ctrl + X – вырезать содержимое.
• Ctrl + D – скопировать содержимое верхней ячейки в текущую.
• Ctrl + R – скопировать содержимое левой ячейки в текущую.

Горячие клавиши для работы со столбцами и строками:

• Ctrl + Space – выделить весь столбец.
• Shift + Space – выделить всю строку.
• Ctrl + Shift + «+» – вставить столбец или строку.
• Ctrl + «-» – удалить столбец или строку.
• Ctrl + 9 – скрыть выделенные строки.
• Ctrl + 0 – скрыть выделенные столбцы.
• Ctrl + Shift + 9 – показать скрытые строки.
• Ctrl + Shift + 0 – показать скрытые столбцы.

Горячие клавиши для фильтрации и сортировки:

• Ctrl + Shift + L – включить/выключить фильтры.
• Alt + стрелка вниз – открыть выпадающий список фильтрации для текущего столбца.
• Alt + D + F + F – включить автофильтр.
• Alt + D + S – открыть окно сортировки данных.
• Alt + A + S + A – сортировка от А до Я.
• Alt + A + S + D – сортировка от Я до А.

Горячие клавиши для работы с книгами:

• Ctrl + N – создать новую книгу.
• Ctrl + O – открыть существующую книгу.
• Ctrl + S – сохранить текущую книгу.
• Ctrl + W – закрыть текущую книгу.
• Ctrl + P – печать книги.

Горячие клавиши для навигации:

• Ctrl + стрелка (вверх, вниз, влево, вправо) – переместиться к краю области данных.
• Ctrl + Home – перейти в начало листа (ячейка A1).
• Ctrl + End – перейти к последней используемой ячейке.
• Page Up / Page Down – перемещение по экрану вверх/вниз.

Горячие клавиши для отмены действий:

• Ctrl + Z – Отменить последнее действие.
• Ctrl + Y – Повторить отмененное действие.

Кроме того, вы можете настроить свои сочетания клавиш, которые будут удобны лично для вас. Как это сделать, можно узнать из материала «Настройка сочетаний клавиш» [Microsoft, 2023].

Как защитить данные на листе или в файле с помощью пароля?

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

Защита файла эксель паролем:

• Откройте книгу, которую хотите защитить.
• Нажмите вкладку «Файл» или кнопку Office в левом верхнем углу.
• Перейдите в «Сохранить».
• Нажмите кнопку «Сервис».
• Выберите «Общие параметры».
• Назначьте пароль для открытия и/или изменения.
• Нажмите ОК.

Вот так:

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

Уточним, что пароль на открытие – это когда требуется ввести пароль, чтобы открыть файл, а пароль на изменение позволяет открыть файл в режиме «Только для чтения». Пароль в этом случае потребуется, только если кто-то захочет внести изменения.

Защита отдельных листов Excel паролем:

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

Итак, ваш лист защищен, и теперь его нельзя изменять без пароля. Обратите внимание, что рядом с копкой «Защитить лист» есть еще кнопка «Защитить книгу»:

Не следует путать эту функцию с защитой паролем на уровне книги. Тут речь идет о защите определенных элементов файла. Защита отдельных элементов не защищает книгу от несанкционированных действий пользователей полностью.

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

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

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

Советы по организации рабочего пространства в Excel для удобства использования

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

Настройка панели быстрого доступа

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

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

Фиксация строк и столбцов

При работе с большими таблицами важно закрепить заголовки столбца и/или строки в экселе, чтобы они оставались на виду при прокрутке данных. Мы уже изучили закрепление областей в эксель в уроке 5, поэтому сейчас просто кратко напомним, как это сделать:

Вкладка Вид > Закрепить области > Закрепить верхнюю строку

или

Закрепить первый столбец

или

Закрепить области, чтобы настроить область закрепления вручную

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

Использование именованных диапазонов

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

Именованный диапазон можно организовать буквально в пару кликов:

Вкладка Формулы > Присвоить имя

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

Встроенное форматирование

Как задействовать встроенное форматирование:

Вкладка Главная > Форматировать как таблицу

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

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

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

Вкладка Главная >Условное форматирование

Что нужно делать дальше, мы разобрались на прошлом уроке.

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

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

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

Изменение размеров областей интерфейса

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

Движение курсора вправо в сторону значка «+» позволяет увеличить размер и как бы «приблизить» нужную область, а движение влево в сторону значка «–» уменьшает размер и как бы «отдаляет» от вас лист эксель.

Часто задаваемые вопросы о работе в Excel

И напоследок давайте рассмотрим самые часто задаваемые вопросы о работе в Excel. Многие из них мы рассматривали в нашем курсе, поэтому сейчас дадим самые краткие ответы.

Как использовать основные математические функции в Excel?

В Excel можно использовать базовые математические операции, такие как сложение (=A1+B1), вычитание (=A1-B1), умножение (=A1*B1) и деление (=A1/B1). Excel также поддерживает функции, такие как SUM() для сложения диапазонов и AVERAGE() для вычисления среднего значения.

Как сделать фильтрацию данных в Excel?

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

Как сделать автоматическое заполнение ячеек (автозаполнение)?

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

Как объединить несколько ячеек в одну?

Выделите ячейки, которые нужно объединить, перейдите на вкладку «Главная», затем в раздел «Выравнивание», затем нажмите «Объединение ячеек». Там же можно выбрать выравнивание по центру, по правому краю и т.д.

Как использовать условное форматирование?

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

Как создать сводную таблицу?

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

Как убрать дублирующиеся значения?

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

Как разделить текст по столбцам?

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

Как создать диаграмму?

Выделите данные, которые хотите визуализировать, перейдите на вкладку «Вставка» и выберите тип диаграммы (например, столбчатая, линейная, круговая). Настройте диаграмму с помощью инструментов «Конструктор» и «Формат».

Как сделать ссылку на другую ячейку или лист?

Чтобы сослаться на ячейку на другом листе, введите =Лист2!A1, где Лист2 – это название листа, а A1 – это ячейка. Для ссылки на ячейку в той же книге просто введите адрес ячейки.

Как применять формулы к целым столбцам или строкам?

Вы можете применить формулу к целому столбцу или строке, указав диапазон. Например, =СУММ(A:A) суммирует все значения в столбце A.

Как вставить сегодняшнюю дату или текущее время?

Для вставки текущей даты используйте функцию =СЕГОДНЯ(), для текущего времени =ТДАТА(). Выберите ячейку, куда нужно поместить дату или время, и вставьте нужную формулу в строку формул fx. При вводе функции =СЕГОДНЯ() в ячейке отобразится только дата, при вводе =ТДАТА() отобразятся дата и время.

Как использовать функцию ВПР / VLOOKUP?

Функция ВПР() используется для поиска значения в таблице или диапазоне данных, и выглядит следующим образом:

=ВПР(значение_поиска, таблица, номер_столбца, [точное_соответствие])

Например: =ВПР (B3,B2:E7,2,ЛОЖЬ)

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

Можем порекомендовать вам крутую подборку из 73 бесплатных уроков Excel. Эти «Уроки Microsoft Excel» содержат более глубокое объяснение изученных вами основ и разбор более сложных примеров, к которым вы уже вполне готовы, если смогли освоить и понять простые примеры, разобранные в нашем базовом курсе.

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

• «Функция ЕСЛИ (IF)» (урок 5).
• «Диаграммы и графики» (урок 21).
• «Макросы» (урок 22).
• «Условное форматирование» (урок 23).
• «Сводные таблицы» (урок 26).

Кроме того, здесь вы сможете найти информацию и освоить возможности эксель, выходящие за рамки базовых навыков работы с таблицами:

• «Функция вертикального поиска ВПР / VLOOKUP» (урок 13).
• «Функция горизонтального поиска ГПР / HLOOKUP» (урок 14).
• «Зависимые выпадающие списки» (урок 16).
• «Рассылка писем и автоматизированная подготовка документов из таблицы Excel» (урок 67).

Заметим, что эти уроки максимально адаптированы к реалиям российской экономики, и вы можете, что называется, «брать и делать» многие вещи так, как объясняется в этих уроках. Дадим для примера несколько ссылок, где разъясняются наиболее востребованные в реальном секторе экономике действия в эксель:

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

Проверьте свои знания

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

А теперь предлагаем пройти экзамен по материалу всего курса.

Ольга Обломова