Как стать гуру эксель

Обновлено: 05.07.2024

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

Раздел 1: Основы Excel

Знакомство с Excel
Создание и открытие рабочих книг
Сохранение книг и общий доступ
Основы работы с ячейками
Изменение столбцов, строк и ячеек
Форматирование ячеек
Основные сведения о листе Excel
Разметка страницы
Печать книг

Раздел 2: Формулы и функции

Простые формулы
Сложные формулы
Относительные и абсолютные ссылки
Формулы и функции

Раздел 3: Работа с данными

Управление внешним видом рабочего листа
Сортировка данных в Excel
Фильтрация данных в Excel
Работа с группами и подведение итогов
Таблицы в Excel
Диаграммы и спарклайны

Раздел 4: Расширенные возможности Excel

Работа с примечаниями и отслеживание исправлений
Завершение и защита рабочих книг
Условное форматирование
Сводные таблицы и анализ данных

Раздел 5: Продвинутая работа с формулами в Excel

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

Раздел 6: Дополнительно

Хотите узнать об Excel еще больше? Специально для Вас мы припасли целых два простых и полезных самоучителя: 300 примеров по Excel и 30 функций Excel за 30 дней.

Каждый человек, который работает за компьютером, ответит утвердительно, если спросить его о том, умеет ли он работать в Excel. Однако если поставить вопрос иначе и попытаться узнать, насколько хорошо, у большинства это вызовет недоумение. А что там можно уметь особенного? На самом деле, эта программа хранит в себе огромное количество секретов, на которые вы попросту не обращаете внимания. Но если вы о них узнаете, они тут же станут вашими самыми любимыми. Итак, в данной статье вы узнаете про двадцать различных приемов в "Экселе", которые стоит знать каждому.

Выделение всего одним кликом

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

Открытие нескольких файлов

Переход между файлами

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

Пункты меню

Диагональная линия в ячейке

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

Добавление нескольких рядов или столбцов

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

Быстрый перенос данных

Что делать, если вам нужно быстро перенести одну колонку данных? Для этого подведите курсор к краю колонки, пока он не примет форму крестика в форме стрелок, после чего свободно перемещайте содержимое колонки.

Удаление пустых ячеек

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

Неточный поиск

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

Создание уникального значения

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

Конкретизация информации

Далеко не все знают, что вы можете ввести ограничения на то, какую информацию будут вводить в ячейку. Например, если вы создаете список приглашенных людей на мероприятие, и их возраст может колебаться от 18 до 60 лет (только целые числа), то оба этих параметра вы можете задать в соответствующем пункте меню "Данные", и тогда иные значения попросту не будут приняты.

Быстрая навигация

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

Преобразование

Иногда бывает очень нужно превратить колонку в ряд или наоборот. На самом деле, сделать это можно очень просто. Для этого вам нужно скопировать нужную информацию, а затем вставлять ее через меню, выбрав пункт "Преобразовать" - и ваш ряд будет скопирован в виде столбца.

Как спрятать информацию?

В "Экселе" есть возможность прятать информацию, но сделать это можно разными методами. Можно кликнуть по ячейке и выбрать пункт "Спрятать", но это даст далеко не самый лучший результат. Поэтому рекомендуется использовать форматирование ячеек в формате Type ;;; - он позволяет вам максимально надежно спрятать информацию от посторонних глаз.

Совмещение данных из ячеек

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

Смена строчных на прописные

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

Данные, начинающиеся с 0

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

Автоисправление

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

Больше информации

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

Переименование страницы

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

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

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

Создание таблицы в Microsoft Excel

Конечно, в первую очередь необходимо затронуть тему создания таблиц в Microsoft Excel, поскольку эти объекты являются основными и вокруг них строится остальная работа с функциями. Запустите программу и создайте пустой лист, если еще не сделали этого ранее. На экране вы видите начерченный проект со столбцами и строками. Столбцы имеют буквенное обозначение, а строки – цифренное. Ячейки образовываются из их сочетания, то есть A1 – это ячейка, располагающаяся под первым номером в столбце группы А. С пониманием этого не должно возникнуть никаких проблем.

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

Задайте для нее необходимую область, зажав левую кнопку мыши и потянув курсор на необходимое расстояние, следя за тем, какие ячейки попадают в пунктирную линию. Если вы уже разобрались с названиями ячеек, можете заполнить данные самостоятельно в поле расположения. Однако там нужно вписывать дополнительные символы, с чем новички часто незнакомы, поэтому проще пойти предложенным способом. Нажмите «‎ОК» для завершения создания таблицы.

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

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

Основные элементы редактирования

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

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

Использование функций Excel

По сути, создать ту же таблицу можно практически в любом текстовом или графическом редакторе, но такие решения пользователям не подходят из-за отсутствия средств автоматизации. Поэтому большинство пользователей, которые задаются вопросом «‎Как научиться работать в Excel», желают максимально упростить этот процесс и по максимуму задействовать все встроенные инструменты. Главные средства автоматизации – функции, о которых и пойдет речь далее.

Если вы желаете объявить любую функцию в ячейке (результат обязательно выводится в поле), начните написание со знака «=», после чего впишите первый символ, обозначающий название формулы. На экране появится список подходящих вариантов, а нажатие клавиши TAB выбирает одну из них и автоматически дописывает оставшиеся символы.

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

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

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

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

Вставка диаграмм

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

Элементы разметки страницы

Сохранение и переключение между таблицами

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

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

Если для построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

2. Мгновенное заполнение (Flash Fill)

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

3. Копирование без нарушения форматов

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

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

4. Отображение данных из таблицы Excel на карте

В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.

После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

5. Быстрый переход к нужному листу

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

6. Преобразование строк в столбцы и обратно

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

Выделите диапазон.
Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose). В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

7. Выпадающий список в ячейке

Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и так далее), то это можно легко организовать при помощи выпадающего списка.

8. Умная таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

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

9. Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.

10. Восстановление несохранённых файлов

Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

11. Сравнение двух диапазонов на отличия и совпадения

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

Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
Выберите на вкладке «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

12. Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

Читайте также: