20 секретов excel которые помогут упростить работу

Обновлено: 01.07.2024

Так получилось, что когда я начала рассказывать о специфике работы аналитика, я не смогла обойтись без статей об excel и поэтому на моем канале появилось несколько статей про excel (и их количество растет!). Excel это основа основ для работы с цифрами, инструмент гибкий и must have для большинства специалистов разных сфер.

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

Теперь есть еще одна статья подборка по публикациям в формате видеороликов - Фишки Excel - подборка видеороликов .

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

Если вы знаете, на какую тему вам бы хотелось увидеть статьи - прошу, сообщите об этом в комментариях и я постараюсь раскрыть тему максимально подробно!

✔ Общие статьи про excel:

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

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

3. Горячие клавиши в Excel - оптимизируй свою работу . Привожу полезные сочетания клавиш, которые ускорят вашу работу в excel (копировать, вставить, отменить последнее действие и пр.)

4. Джон Уокенбах - настоящий профессионал в мире Excel . Статья об одном из самых больших профессионалов в мире excel, авторе более 50 книг про эту программу и описание нескольких из них. Своих героев надо знать в лицо :)

6. Защита файла excel и "Инспектор документов" . В статье описаны виды защиты файла от просмотра и изменения.

✔ Визуализация в excel:

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

2. Сила условного форматирования в Excel . Условное форматирование часто недооценивают. На простых примерах покажу, что с его помощью можно сделать.

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

4. Спарклайны: мини-график в ячейке Excel . Показываю как создать спарклайн, а также рассказываю о возможности форматирования спарклайнов.

5. Приемы необычной визуализации в диаграммах Excel . Создание многоуровневой диаграммы и добавление символов подписи данных.

6. Создание собственного числового формата в Excel. Рассказываю про структуру формата ячеек при создании своего формата и показываю как его создать.

7. Как создать динамическую диаграмму . Создание динамической диаграммы на базе нескольких формул.

8. Диаграммы в Excel . Показываю для чего можно использовать основные виды диаграмм и их примеры.

9. Гистограмма в Excel . Рассказываю, для чего используются гистограммы и как их строить, а также редактировать.

✔ Power Query и Power Pivot:

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

2. Power Query - загрузка данных из интернета . Power Query способен напрямую скачать любую таблицу со страницы в интернете - показываю как это сделать.

3. Введение в Power Pivot . Вводная статья о том, для чего предназначен Power Pivot, как загрузить в него данные и установить связи между таблицами.

✔ Работа с таблицами и полезные советы:

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

2. Плюсы и минусы сводных таблиц . Показываю, на что необходимо обратить внимание при использовании сводных таблиц.

3. Срезы и временная шкала в сводных таблицах Excel . Показываю как создать сводную таблицу и подключить срез и временную шкалу.

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

5. Форма для ввода данных в Excel . Расскажу про инструмент для альтернативного ввода данных в таблицу.

7. Проверка данных - часть 2 . Это вторая часть статьи про Проверку данных, в которой показаны несколько интересных примеров использования этого инструмента.

9. Альтернатива для примечаний в Excel - как оставить пояснение к ячейке . Показываю, как не используя примечания, оставить комментарий к ячейке или формуле.

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

11. Умные таблицы в Excel . Как создать "умную таблицу", в чем ее преимущества и как ее отформатировать.

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

13. Объединенные ячейки: как найти и избавиться . Показан простой способ отыскать объединенные ячейки и разбить их.

14. Добавление водяного знака на лист Excel . Покажу как добавить водяной знак в виде изображения на лист.

✔ Работа с функциями и формулами в excel:

1. Как пользоваться ВПР в Excel . Без ВПР мы никуда, это мощная функция для поиска данных и соединения нескольких таблиц. Просто и понятно на примере колл-центра показываю, как она работает.

2. Абсолютные и относительные ссылки в Excel . Показано, как работают абсолютные, относительные и смешанные ссылки.

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

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

5. Просто о функциях ИНДЕКС и ПОИСКПОЗ . Понятно демонстрирую, как работают эти функции по отдельности и на что они способны вместе.

6. Непопулярные, но такие полезные функции Excel для работы с текстом . Показываю, как можно использовать такие непопулярные функции как ЕТЕКСТ, ТИП и ПРОПНАЧ (включая то, как перевести формат текста из "зЕЛЕный Чай" в такой "Зеленый чай" за одну формулу).

7. Как объединить ячейки в Excel: конкатенация (&) и СЦЕПИТЬ . Несколько способов как соединить текст из нескольких ячеек в одну.

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

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

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

11. Вычисление определенного дня года в Excel . Как быстро определить порядковый номер дня в году и рассчитать остаток количества дней относительно конкретной даты.

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

13. Скрытая, но мощная функция в Excel . Статья о скрытой функции РАЗНДАТ, которая позволяет рассчитать количество дней, месяцев и лет между двумя датами.

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

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

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

17. МОДА: подсчет наиболее часто встречающихся значений в Excel . Расскажу, как использовать функцию МОДА для подсчета часто встречающихся значений.

18. Циклическая ссылка в Excel . Что такое циклическая ссылка и как ее найти.

19. Виды ошибок в формулах Excel . Перечислены основные виды ошибок, которые возникают при расчете формулы и причины, их вызывающие.

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

21. Генератор случайных чисел в Exce l . Покажу как просто сгенерировать список случайных чисел в excel.

22. Изменение регистра текста в ячейке Excel . Описано несколько простых способов изменения регистра текста в ячейке.

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

Выделение всех ячеек одним кликом

Все ячейки можно выделить комбинацией клавиш Ctrl + A, которая, кстати, работает и во всех других программах. Однако есть более простой способ выделения. Нажав на кнопку в углу листа Excel, вы выделите все ячейки одним кликом.

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

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

Перемещение по файлам Excel

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

Добавление новых кнопок на панель быстрого доступа

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

Перейдите в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа». Теперь можно выбрать любые кнопки, которые вам нужны.

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

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

Добавление в таблицу пустых строк или столбцов

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

Скоростное копирование и перемещение информации

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

Быстрое удаление пустых ячеек

Пустые ячейки — это бич Excel. Иногда они появляются просто из ниоткуда. Чтобы избавиться от них всех за один раз, выделите нужный столбец, перейдите на вкладку «Данные» и нажмите «Фильтр». Над каждым столбцом появится стрелка, направленная вниз. Нажав на нее, вы попадете в меню, которое поможет избавиться от пустых полей.

Расширенный поиск

Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск — за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.

Если же вам нужно найти вопросительный знак или астериск и вы не хотите, чтобы вместо них Excel искал неизвестный символ, то поставьте перед ними «

Копирование уникальных записей

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

Создание выборки

Быстрая навигация с помощью Ctrl и стрелки

Нажимая Ctrl + стрелка, можно перемещаться в крайние точки листа. К примеру, Ctrl + ⇓ перенесет курсор в нижнюю часть листа.

Транспонирование информации из столбца в строку

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

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

Как скрывать информацию в Excel

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

Объединение текста с помощью «&»

Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».

Изменение регистра букв

С помощью определенных формул можно менять регистр всей текстовой информации в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» — строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.

Внесение информации с нулями в начале

Если вы введете в Excel число 000356, то программа автоматически превратит его в 356. Если вы хотите оставить нули в начале, поставьте перед числом апостроф «’».

Ускорение ввода сложных слов

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

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

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

Переименование листа с помощью двойного клика

Это самый простой способ переименовать лист. Просто кликните по нему два раза левой кнопкой мыши и введите новое название.

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

Пользуетесь ли вы Excel? Мы выбрали 20 советов, которые помогут вам узнать его получше и оптимизировать свою работу с ним.

Выделение всех ячеек одним кликом

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

Перемещение по файлам Excel

Добавление новых кнопок на панель быстрого доступа

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

Добавление в таблицу пустых строк или столбцов

Скоростное копирование и перемещение информации

Быстрое удаление пустых ячеек

Расширенный поиск

Копирование уникальных записей

Создание выборки

Быстрая навигация с помощью Ctrl и стрелки

Транспонирование информации из столбца в строку

Как скрывать информацию в Excel

Объединение текста с помощью «&»

Изменение регистра букв

Внесение информации с нулями в начале

Ускорение ввода сложных слов

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

Переименование листа с помощью двойного клика

Признаюсь честно, я не большой специалист в Exel. Очень многие, даже простейшие процессы автоматизации в нем меня ставят в тупик. Поэтому данная статья мне была очень любопытна и полезна. Может быть и вы узнаете что то новое для себя. Итак читаем …

Сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов — автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓

Быстрое добавление новых данных в диаграмму

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

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

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

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

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

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

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

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

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

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

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

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

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

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

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

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

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

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

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

Продвинутые трюки из той же серии: выпадающий список с наполнением, связанные выпадающие списки, выпадающий список с фотографией и т. д.

«Умная» таблица

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

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

Спарклайны

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

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

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

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

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

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

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

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

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

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

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

Ну и еще некоторые более простые полезность про Exel:

Выделение всех ячеек одним кликом

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

Перемещение по файлам Excel

Добавление новых кнопок на панель быстрого доступа

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

Добавление в таблицу пустых строк или столбцов

Скоростное копирование и перемещение информации

Быстрое удаление пустых ячеек

Расширенный поиск

Копирование уникальных записей

Создание выборки

Быстрая навигация с помощью Ctrl и стрелки

Транспонирование информации из столбца в строку

Как скрывать информацию в Excel

Объединение текста с помощью «&»

Изменение регистра букв

Внесение информации с нулями в начале

Ускорение ввода сложных слов

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

Переименование листа с помощью двойного клика

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