Горячие комбинации клавиш
Неплохую подсказку даст и сам Excel, если нажать клавишу alt. На панели инструментов над разными кнопками появятся буквы. При нажатии на них будут запускаться те или иные функции, это тоже поможет запомнить некоторые горячие клавиши. Также большинство полезных сочетаний клавиш можно найти на официальной странице программы.
За раз такое количество функций и сочетаний не запомнить, но они будут откладываться в памяти сами, если периодически применять некоторые из них, работая с данными. Так существенно ускорится и облегчится вся работа.
— Видео-курс Excel для начинающих
— Автоматизация работы в Excel
— Как ускорить работу в Microsoft Word
7 ноября 2022
ИНДЕКС
Эта функция возвращает значение конкретной ячейки в указанном диапазоне:
=ИНДЕКС(диапазон; номер_строки; )
Если в диапазон ячеек входит только один столбец, достаточно будет указать только номер строки. Когда в выбранном диапазоне получается два столбца и больше, можно будет выбрать его номер, чтобы точно обозначить ячейку.
Попробуем в нашем примере с бюджетом найти того, кто сделал самую большую покупку. Мы уже знаем номер строки, в которой записана самая большая сумма, и на этой же строке записан тот, кто эту сумму потратил. Используем это:
=ИНДЕКС(B1:B10;E10)
Когда Excel встретит такую формулу, он возьмёт наш диапазон из второго столбца, затем возьмёт номер строки с самой большой покупкой (она у нас в ячейке Е10, нашли в прошлом примере), и определит, что самую крупную покупку сделал Миша.
Восстановление несохраненного файла
На случай наступления конца света.
Для многих пользователей, скорее всего, будет знакома такая ситуация: обрадовались концу рабочего дня настолько, что на заботливый вопрос MS Excel «сохранить последние изменения» кликнули «нет». И труд всех последних часов ушел в небытие. Спасение возможно, надежда есть.
MS Excel 2010. Последовательность команд такая: «Файл» — «Последние» — «Восстановить несохраненные книги» (кнопка внизу справа).
MS Excel 2013. «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохраненные книги». И программа откроет тайный мир всех временных копий книг, которые создавались или изменялись, но не были сохранены.
Спарклайны
Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.
После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.
Из строки в столбец и наоборот
Ситуация, которая случается не часто. Пользователь внес данные в столбик, а они должны находиться в строке. В EXEL есть функция транспонирования диапазона значений. Она помогает экономить время.
Сначала выделить ячейки столбца, потом скопировать − правая кнопка мыши, «Копировать». Следующий шаг − выделить диапазон пустых ячеек строки, тот, куда нужно перенести информацию и транспонировать ее:
- щелкнуть правой кнопкой по выделенному диапазону:
- из контекстного меню выбрать «Специальная вставка»;
- поставить флаг «транспонировать»;
- нажать «ОК».
После этих несложных манипуляций данные из столбца (столбцов) EXEL перенесет в строку (строки) или наоборот из строки (строк) в столбец (столбцы).
Отображение данных из таблицы Excel на карте
В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.
После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.
Приемы и комбинации клавиш ускоряющие работу
Этот прием просто ускорит работу, если EXEL основной рабочий инструмент. Даже на открытии файлов можно экономить время:
- выделить все файлы, находящиеся на рабочем столе;
- нажать кнопку «ENTER».
Помогут в работе полезные комбинации. Работая с несколькими книгами EXEL, можно переходить из одной в другую с помощью двух клавиш, нажатых одновременно. Сочетание − «CTRL ₊ ТАБ».
Кнопки EXEL, которыми часто пользуются, выносят на панель быстрого доступа:
- Файл;
- Параметры;
- Панель быстрого доступа;
- выбрать нужные кнопки.
У EXEL есть еще много возможностей, которые доступны обычным (не продвинутым) пользователям. Использование полезных лайфхаков, делает рутинную работу интересней, сокращает время выполнения, уменьшает количество ошибок.
Заполнить пустые ячейки списка
Это избавит от монотонного ввода одинаковых фраз во множество ячеек. Конечно, можно воспользоваться старым добрым копипастом (копировать ctrl+c, вставить ctrl+v), но, если нужно заполнить не 10 ячеек, а, например, сотню-другую, и местами текст будет разным, – следующая подсказке точно пригодится.
Допустим, есть большущий список дел на всю неделю, и нужно напротив каждой задачи поставить определенный день недели. Не стоит прописывать по 20 понедельников, вторников и пятниц. В столбце ставится каждый день недели на том месте, где должен начаться список дел на этот день. Это должно выглядеть примерно так:
СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Часто бывает нужно посчитать количество строк или заполненных ячеек по каким-то параметрам, например узнать количество работников старше 40 лет, или посчитать все строки, где есть слово «переменная». Для этого используют функцию СЧЁТЕСЛИ:
= СЧЁТЕСЛИ(диапазон;”условие”)
Диапазон — это группа ячеек, в которых мы будем что-то искать, а условие — это по какому параметру мы решим, что ячейка нам подходит.
Допустим мы ведём семейный бюджет и нам нужно узнать, сколько раз у нас были покупки больше 5000 рублей в одном чеке. Для этого используем такое:
=СЧЁТЕСЛИ(C2:C10;»>=5000″)
Теперь посложнее. Если нам нужно посчитать количество ячеек по нескольким условиям одновременно, то используют функцию СЧЁТЕСЛИМН:
= СЧЁТЕСЛИМН(диапазон;условие;…)
То, что в прямоугольных скобках — дополнительные условия, их может быть максимум 127, а сами скобки при этом ставить не нужно
Обратите внимание что для каждого нового условия можно задать свой диапазон. Функция проверит все условия, каждое в своём диапазоне, и если все условия одновременно выполняется — посчитает количество таких ячеек
Для примера найдём все траты больше 5000 в одном чеке, за которые отвечает Миша:
=СЧЁТЕСЛИМН(C2:C10;»>=5000″;B2:B10;»=Миша»)
Теперь у нас первый диапазон отвечает за сумму в чеке, а второй — за имена, поэтому и условия у каждого из них разные.
Найти ошибки в формуле
Бывает, формула не работает, но причина «поломки» непонятна. Иногда, чтобы разобраться в сложной формуле (где как аргумент функции берутся другие функции) или найти в ней ошибку, нужно вычислить только ее часть. Вот две подсказки:
Часть формулы вычисляется прямо в строке формул. Для этого необходимый участок нужно выделить и нажать F9. Все просто, но есть одно «но». Если забыть вернуть все на место, то есть отменить вычисление функции, и нажать enter – посчитанная часть останется в виде числа.
Кликнуть на «Вычислить формулу» во вкладке «Формулы». Откроется окно, где можно вычислять формулу пошагово и тем самым найти момент, где появляется ошибка, если она, конечно, есть.
Несколько полезных лайфхаков EXEL
Мгновенное заполнение
Стандартная ситуация – таблица, содержащая большой список людей. В одной колонке есть фамилия имя отчество полностью, нужна колонка, в которой будет стоять фамилия и инициалы. Понять, как сделать это несколькими кликами просто.
В верхнюю ячейку соседнего столбца внести образец правильного заполнения. Например:
если в ячейке Михаил Юрьевич Горшнев, то в соседней (справа, слева, неважно) – М. Ю
Горшнев;
если в ячейке Горшнев Михайл Юрьевич, то в соседней (справа, слева, неважно) – Горшнев М. Ю.
После ввода образца, достаточно нажать «ENTER» или комбинацию «CTRL ₊ E», чтобы все ячейки, расположенные ниже, заполнились.
Разбить информацию
Если, при работе со списком людей, нужно фамилию, имя и отчество ввести в разные колонки, поможет в работе та же функция мгновенного заполнения EXEL:
- в верхнюю строку первой колонки вбить полную фамилию − Горшнев;
- в соседнюю ячейку вручную ввести имя – Михаил;
- в колонку рядом отчество – Юрьевич.
На этом рутинная работа заканчивается. Пользователь, заполнив образец, жмет комбинацию «CTRL ₊ E». Все ячейки, расположенные ниже, заполняются автоматически.
Склеить текст
Ситуация обратная предыдущей − есть информация в разных ячейках, которую необходимо поместить а одну и дополнить ее определенным текстом. Например, в таблице EXEL есть колонки:
- название города;
- дата;
- номер счета;
- сумма.
Нужен текст – Счет № … от …. на сумму… за отправку товара в г….. Пользователь тратит время на вбивание образцов в верхние 2-3 ячейки колонки, оставляет одну пустую ячейку столбца активной, нажимает волшебную кнопку мгновенного заполнения или «CTRL ₊ E».
Как извлечь из текста число, слово или дату
Еще один пример использования функции Flash Fill (мгновенного заполнения) поможет быстро обрабатывать большие объемы информации. Из строк текста, содержащих слова, даты, цифры, можно вытащить в соседний столбик слово, число или дату:
- ввести образец в две верхние ячейки столбца;
- третью ячейку сделать активной;
- нажать «CTRL ₊ E».
Формат дат в колонке может различаться. Чтобы EXEL отработал правильно при преобразовании даты в Unix-формат, формат итоговой ячейки должен быть текстовым. Его делают текстовым и тогда, когда хотят добавить нужную информацию к телефонному номеру:
- код города;
- префикс оператора в скобках.
Строки, начинающиеся с «+» редактор воспринимает как формулу.
ЕСЛИ
Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.
В общем виде она выглядит так:
=ЕСЛИ(условие; “значение_если_да”;”значение_если_нет”)
В условии может быть что угодно: сравнение ячеек, другие формулы, сравнения и математические команды — всё, что вам нужно проверить.
На практике можно сделать, например, так: пусть Excel проверяет возраст, и пишет документ, который в этом возрасте удостоверяет личность. До 14 лет это свидетельство о рождении, а после — паспорт. Для этого используем такую команду в ячейке:
Программа проверит, что стоит в предыдущей ячейке, и если там число меньше 14, то напишет «Свидетельство о рождении», а если уже есть 14 лет, то «Паспорт»:
Как работать с формулами и функциями
Формулы в Excel — выражения, с помощью которых проводят расчёты со значениями на листе. Пользователи вводят их в ячейки таблицы вручную. Чаще всего их используют для простых вычислений.
Функции в Excel — заранее созданные формулы, которые проводят вычисления по заданным значениям и в указанном порядке. Они позволяют выполнять как простые, так и сложные расчёты.
На тему работы с формулами и таблицами тоже есть видеоурок. В нём Ренат Шагабутдинов показывает:
- как проводить расчёты с помощью стандартных формул и функций;
- как создавать формулы с абсолютными и относительными ссылками;
- как находить ошибки в формулах.
Как создать и настроить диаграммы
Диаграммы используют для графического отображения данных таблиц. Также с помощью них показывают зависимости между этими данными. Сложная информация, представленная в виде диаграмм, воспринимается проще: можно расставлять нужные акценты и дополнительно детализировать данные.
В статье «Как создать и настроить диаграммы в Excel» рассказали:
- для чего подойдёт круговая диаграмма и как её построить;
- как показать данные круговой диаграммы в процентах;
- для чего подойдут линейчатая диаграмма и гистограмма, как их построить и как поменять в них акценты;
- как форматировать готовую диаграмму — добавить оси, название, дополнительные элементы;
- как изменить данные диаграммы.
Подбор (подгонка) результатов расчёта под нужные значения
Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!
Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.
Восстановление несохранённых файлов
Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.
На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).
В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).
В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».
Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.
«Умная» таблица
Если у пользователя и MS Excel разное понимание понятия таблицы, это может лишить его многих удобных функций и растянуть время работы. Начерченную карандашом или с помощью границы – программа посчитает обычным набором ячеек. Чтобы данные воспринимались именно как таблица, это поле нужно отформатировать.
Необходимо выделить нужную область, во вкладке «Главная» нажать кнопку «Форматировать как таблицу». В списке разных форм и цветов вариантов оформления найти подходящий.
Теперь это настоящая таблица, и если в нее хочется добавить новые формулы – вставлять их нужно приграничные ячейки, тогда они автоматически распространятся на весь столбец. Плюс ко всему шапка таблицы всегда будет видна и при прокрутке курсора вниз.
Условное форматирование
Что-то вроде диаграммы, только показатели находятся не в соседней ячейке, а прямо на фоне данных. Эту функцию включают, чтобы выделить некоторые данные в таблице.
Например, учитель в школе делает таблицу средней успеваемости каждого ученика. Тогда условное форматирование прямо внутри ячеек с оценками построит «график», на котором будут выделены средние баллы каждого ученика, к примеру, от низкого к высокому. Или же выделит ячейки, в которых средний балл, например, ниже или выше 6. Параметры могут быть разными.
Чтобы функция заработала, нужно открыть вкладку «Главная», выделить поле с ячейками, в группе инструментов «Стили» найти значок «Условное форматирование» и выбрать подходящий вариант – это может быть гистограмма, цветовая шкала или набор значков. Этой же командой можно самостоятельно установить правила выделения ячеек.
Выпадающий список в ячейке
Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и так далее), то это можно легко организовать при помощи выпадающего списка.
- Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
- Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data → Validation).
- В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
- В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.
