Что можно делать в экселе список

Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».

Работа с раскрывающимся списком

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

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

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

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

17 функций и 6 хитростей Excel, которые помогут упростить работу

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

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

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

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

Таблица 1. 17 полезных функций Excel

Позволяет складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трёх видов.

=СУММ(Число 1;Число 2;. Число n)

=СУММ(А1;B1;C1) — сумма значений в ячейках

Перемножает все числа, переданные как аргументы, и возвращает произведение.

=ПРОИЗВЕД(Число 1; Число 2;. ;Число n)

=ПРОИЗВЕД(А1;B1;C1) — произведение значений в ячейках

Считает среднее арифметическое числовых значений.

=СРЗНАЧ(Число 1; Число 2;. ;Число n)

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

У функции возможны два результата (ИСТИНА и ЛОЖЬ).

Функцию ЕСЛИ можно настроить сразу по нескольким критериям.

=ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])

Например, если в таблице нужно определить значения меньше 100, то значение 96 будет отмечено функцией как истинное, а значение 125 — как ложное.

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

=СУММЕСЛИ(ячейки которые нужно проверить на условие; условие; какие ячейки складывать при удовлетворении условию)

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

= СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчёт)

Рассчитывает условное среднее значение.

=СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; условие; для каких ячеек рассчитывать среднее при удовлетворении условию)

Возвращает наименьшее значение в списке аргументов.

=МИН(Число 1; Число 2;. ;Число n)

Возвращает максимальное значение в списке аргументов (функция обратная МИН).

=МАКС(Число 1; Число 2;. ;Число n)

Используется для получения минимального значения из заданного диапазона ячеек (возвращает k-ое наименьшее значение из массива данных).

В ячейках А1;A5 находятся числа 1;3;6;5;10.

=НАИМЕНЬШИЙ (A1;A5) при разных k:

k=1; результат =1 (первое наименьшее значение)

k=2; результат=2 (второе наименьшее значение)

k=3; результат=5 (третье наименьшее значение)

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

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

В ячейках А1;A5 находятся числа 1;3;6;5;10.

= НАИБОЛЬШИЙ (A1;A5) при разных k:

k=1; результат = 10 (первое наибольшее значение)

k=2; результат = 6 (второе наибольшее значение)

k=3; результат = 5 (третье наибольшее значение)

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

Возвращает заданное количество знаков из указанной строки слева.

=ЛЕВСИМВ(адрес ячейки; количество знаков)

Возвращает заданное количество знаков из указанной строки справа.

=ПРАВСИМВ(адрес ячейки; количество знаков)

Возвращает знаки из текстовой строки, начиная с указанной позиции.

=ПСТР(адрес ячейки; начальное число; число знаков)

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

Например, по номеру товара можно найти его цену или по идентификатору найти имя сотрудника.

=ВПР(искомое значение; таблица; номер столбца; тип совпадения)

Тип совпадения может быть приблизительным или точным. Для передачи точного значения в формуле нужно прописать 0 или ЛОЖЬ, для передачи приблизительных значений указывается 1 или ИСТИНА

Конкатенация

Позволяет объединить в одной ячейке данные из двух и более ячеек.

Самый простой способ — вставить между адресами ячеек амперсанд (&).

=ячейка 1&ячейка 2. &ячейка n (=A1&A2&A3)

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

1. Чем полезно умное форматирование

Чтобы систематизировать данные, привести таблицы в понятный и презентабельный вид можно использовать условное форматирование (рисунок 1). Это целый массив способов обработки данных.

Методы основаны на цветном выделении ячеек в зависимости от различных критериев:

• ранжирования по диапазону значений с помощью гистограмм\
• сравнения с константой
• различных значков

Благодаря динамике, данные корректируются при каждом изменении.

Рисунок 1. Условное форматирование

2. Как создать умную таблицу

Список данных в Excel можно преобразовать в умную таблицу (рисунок 2), у которой есть масса полезных функций:

• шапка таблицы автоматически закрепляется при прокрутке, включаются кнопки фильтра для отбора и сортировки;
• при дописывании новых столбцов и строк таблица автоматически растягивается;
• появляется дополнительная вкладка «Конструктор» с дополнительными инструментами анализа и настроек;
• введённые формулы автоматом копируются на весь столбец.

Рисунок 2. Умная таблица

3. Как визуализировать данные с помощью спарклайнов

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

Чтобы создать спарклайн, нажмите «Вставка» → группа «Спарклайны» → кнопка «График» или «Гистограмма» (рисунок 3).

Рисунок 3. Спарклайны

4. Как перенести большую формулу

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

Замените знак «=» на «!». Формула превратится в символьную строку, которая перемещается без изменений. После того как формула полностью вписана в нужную ячейку, поменяйте знак обратно на «=».

5. Как ускорить и упростить работу с помощью Power Query

Представьте, что вам нужно составлять еженедельный отчёт. Вы готовите таблицы в Excel. А исходные вы получаете в виде CSV-файлов. Нужно каждый раз искать в них только необходимую вам информацию, вставлять данные в Excel, обновляя сводные данные и графики. Всё можно сделать намного проще, воспользовавшись Power Query.

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

Надстройка умеет собирать данные из фалов почти 40 различных форматов (например, TXT, XLSX, HTML, CSV, JSON, XML). Помогает менять регистр на правильный, приводить цифры к числовому формату, заполнять пробелы, исправлять заголовки таблиц, разделять текстовые фрагменты на столбцы и склеивать их снова в единый текст, удаляет пустые столбцы и строки, выполняет многие другие полезные операции.

Power Query представлена в двух вариантах:

• отдельным модулем, доступным для скачивания с официального сайта Microsoft (для Excel 2010-13).
• как сервисная функция в составе редактора (рисунок 4).

В большинстве последних версий Excel надстройка находится на вкладке «Данные» → Получить и преобразовать.

Рисунок 4. Power Query

6. Как восстановить несохранённые файлы

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

• Excel 2010: «Файл» → «Последние» и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги».
• Excel 2013: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги».
• Для последующих версий Excel: «Файл» → «Сведения» → «Управление книгой».

Здесь вы найдёте временные копии созданных, изменённых, но несохраненных книг.

Что можно делать в экселе список

Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).

Интерфейс

Настраиваем панель быстрого доступа

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

Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.

Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:

Перемещаемся по ленте без мышки

Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:

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

Ввод данных

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

Автозамена

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

Прогрессия

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

Протягивание

Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:

Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).

Проверка ошибок

Проверка данных позволяет избежать ошибок при вводе информации в ячейки.

Какие бывают типовые ошибки в Excel?

• Текст вместо чисел
• Отрицательные числа там, где их быть не может
• Числа с дробной частью там, где должны быть целые
• Текст вместо даты
• Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

Инструмент проверки данных

Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:

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

Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.

Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:

Удаление пробелов

Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).

Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:

Дата и время

За любой датой в Excel скрывается целое число. Датой его делает формат.

Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.

Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:

С датами можно производить операции вычитания и сложения.

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

Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.

Поиск и подстановка значений

Функция ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).

=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])

У нее есть два режима работы: интервальный просмотр и точный поиск.

Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).

В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.

Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX

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

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

Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:

=ПОИСКПОЗ (что ищем; где ищем ; 0)

На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).

ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.

=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)

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

Получается следующая конструкция:

=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))

Оформление

Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».

На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:

А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.

На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →

Фишки Экселя: возможности и полезные функции для работы в Excel

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

Содержание скрыть

• Создание таблицы в Microsoft Excel
• Основные элементы редактирования
• Использование функций Excel
  • Как сделать «ВПР»
  • Сводные таблицы
  • Создание диаграмм
  • Формулы в Excel – самые полезные функции и интересные фишки
  • Функция «ЕСЛИ»
  • Макросы
  • Условное форматирование
  • Функция ПЛТ
  • Подбор параметра
  • Формула «ИНДЕКС»
  • Использование «Защиты ячеек»
  • Закрепление заголовков строк и столбцов
  • Использование «Специальной вставки» для транспонирования
  • Использование «Мгновенного заполнения»

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

  При запуске программы нам предложат создать новую книгу – так называют все файлы, имеющие расширение .xlsx или .xls.

  

  Далее откроется пустой лист с сетчатой разметкой: горизонтальная шкала обозначена цифрами (от 1 до 1048576), а вертикальная – латинскими буквами (от A до XFD).

  Преимущества такого элемента как умная таблица (УТ) заключаются в том, что:

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

  Если у вас уже есть набор данных, преобразовать их в искомое значение будет не трудно. Достаточно выделить любую область и выбрать на панели вкладку «Главная» → «Стили» → «Форматировать как таблицу» или применить горячие клавиши Ctrl + T.

  Чтобы создать классическую УТ с нуля, открываем на верхней ленте раздел «‎Вставка» и выбираем соответствующий инструмент. Зажимаем левый курсор мыши и растягиваем пунктирную рамку. В диалоговом окне вам предложат подтвердить создание элемента в определенном месте, которое будет обозначено через координаты верхней левой ячейки и нижней правой.

  

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

  

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

  • зажимаем ЛК мыши;
  • выделяем нужную область;
  • вызываем контекстное меню правой кнопкой;
  • выбираем пункт «Формат ячеек»;
  • во вкладке «Границы» нажимаем на «Внешние» и «Внутренние».

  

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

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

  

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

  

  Справа от поля имени находится строка функции (fx), куда вводятся формулы для расчетов.

  

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

  

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

  Оставить заявку

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

  Делать табличные отчеты можно во многих редакторах, однако программа Excel выгодно отличается тем, что имеет много интересных расширенных возможностей и фишек. Она располагает большим количеством готовых вычислительных выражений, которые вызываются двумя способами: через инструмент «Вставить функцию» в разделе «Формулы» или при нажатии на «fx». Ниже мы рассмотрим несколько фишек, которые сильно упростят для вас подсчеты и аналитику и сберегут вашу нервную систему.

  Как сделать «ВПР»

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

  

  Для начала добавим в первую табличку столбики для категорий Цена и Стоимость. После этого выделяем верхнюю ячейку столбца, куда следует перенести сведения (у нас это C2), кликаем на значок «fx» и выбираем ВПР. Выскочит окно для заполнения аргументов, а рядом с fx появится =ВПР().

  

  • В «Искомое значение» вводим диапазон, по которому будет проводиться поиск в прайсе. У нас это наименования игрушек, то есть A2:A6.
  • В следующей графе указываем программе, где находится источник, из которого будет подтягиваться информация (в нашем случае E2:F6), и фиксируем эту ссылку значками $, чтобы выражение корректно сработало для всех строк и данные не смещались. Получится $E$2:$F$6
  • Далее пишем номер столбика в прайсе. В нашем примере это 2.
  • В последнем пункте указываем «ЛОЖЬ» или 0, поскольку нам требуются точные, а не приближенные к какому-то числу или дате значения.

  

  Кликаем «OK» и распространяем правило для всех остальных элементов, потянув за крестик в правом нижнем углу. В результате Эксель перенес все цены из одного списка в другой, несмотря на то, что предметы в них шли в разном порядке. Готово, вы восхитительны. Теперь, если в прайсе изменится цена, то это также автоматически произойдет и в другой сводке.

  

  

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

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

  

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

  

  А так это будет выглядеть, если перетащить «Регион» в область для строк.

  

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

  Создание диаграмм

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

  Для этого есть несколько типов диаграмм:

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

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

  

  При выборе откроется выпадающий список с вариантами шаблонов.

  

  После этого диаграмма сразу появится рядом. Редактирование стилей и структуры происходит достаточно интуитивно через «Конструктор» и «Формат» или по клику правой кнопки мыши прямо на макете. Также график можно перенести на отдельный лист. По такому же принципу работаем со всеми остальными графическими макетами.

  

  Содержание диаграммы настраивается через инструмент «Выбрать данные» в контекстном меню.

  Формулы в Excel – самые полезные функции и интересные фишки

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

  • МАКС и МИН – первая находит наибольшее число в диапазоне, а вторая – наименьшее. Синтаксис: =МАКС/МИН (координаты ячеек).

  

  

  • СРЗНАЧ – складывает все выделенные числа и делит результат на их количество. Синтаксис: = СРЗНАЧ(координаты).

  

  • СУММ – вычисляет сумму элементов. Синтаксис: = СУММ(диапазон)

  

  • СЧЁТ – поможет подсчитать в выбранном промежутке количество числовых значений. Синтаксис: =СЧЁТ(координаты элементов).

  

  Функция «ЕСЛИ»

  Здесь речь идет уже не о простейших вычислениях, а о проверке на соблюдение определенных условий. Если они выполняются, Эксель воспринимает содержимое ячейки как истину, а если нет, то как ложь. Нам необходимо прописать не только условие, но и то, какие данные выдавать в каждом из сценариев. Синтаксис такой: =ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]).

  Например, премия сотруднику выдается только в случае выполнения плана продаж и составляет 5000 рублей. В строку «лог_выражение» мы введем условие, у нас это E5

  

  Макросы

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

  Обычно макросы скрыты по умолчанию, поэтому правой кнопкой мыши кликаем по ленте и выбираем «Настройку панели быстрого доступа». В открывшемся окне переносим из левого столбика пункт «Макросы» или «Разработчик» в некоторых версиях.

  

  После этого на ленте появится соответствующая закладка или раздел в категории «Вид». Давайте теперь решим, какой алгоритм следует автоматизировать.

  Например, мы хотим:

  • добавить к сводке с наименованиями столбик с итоговой стоимостью;
  • перемножить количество и цену;
  • найти наибольшую статью расходов среди получившихся цифр.

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

  

  

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

  

  

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

  

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

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

  

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

  

  

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

  

  Функция ПЛТ

  С её помощью можно быстро рассчитать простые кредитные задачи. Рассмотрим на примере: Вика планирует взять в банке 300 000 рублей и вернуть их в течение 2 лет. При этом кредитная ставка на эту сумму составляет 7% годовых. Чтобы выяснить, сколько предстоит платить банку, чтобы уложиться в срок, составим наглядную таблицу.

  

  Заходим в «Формулы» или пишем вручную =ПЛТ().

  В окно с аргументами вносим:

  • для «Ставки» координаты годового процента и делим на 12 месяцев;
  • для «Креп» срок выплаты;
  • для «ПС» общую сумму;
  • для «БС» нуль или ЛОЖЬ.

  

  Итоговое число должно получиться отрицательным. Тут же можно узнать, какая переплата будет по кредиту при таких условиях. Для этого достаточно перемножить получившийся взнос и значение для «Креп», а после прибавить размер кредита. В нашем случае выражение будет выглядеть вот так: =ПЛТ(D5/12;D6;D7;0)*D6+D7. Переплата при ежемесячном взносе в 13 432 рубля составит 22 363.

  

  Подбор параметра

  Он входит в блок «Анализ “что если”» во вкладке «Данные» и используется для поиска неизвестной, которую нужно ввести в одиночную формулу, чтобы получить желаемый (известный) результат.

  

  Рассмотрим, как это работает на классическом примере. Рассчитаем процентную ставку, если мы знаем размер кредита (2 миллиона) и срок выплаты (2 года).

  

  В строку с ежемесячным платежом вставляем ПЛТ, о которой рассказывали выше. Выходит, что при нулевой ставке следует в течение двух лет выплачивать каждый месяц почти 84 тысячи. Но мир не идеален, поэтому банк требует оплату в размере не ниже 90 000. Чтобы выяснить процент, выбираем одну из трех функций анализа «Подбор параметра». В первом поле открывшегося окна вводим координаты места, где находится формула для расчета, у нас это C5. Во втором указываем сумму, которую будет забирать банк (не забудьте про минус). А в третьем даем координаты, где должно находиться ставка.

  

  Эксель выдал нам результат 7,5%. Это значит, что при этом значении и ежемесячном взносе в 90 тысяч за два года можно выплатить кредит в два миллиона.

  

  Формула «ИНДЕКС»

  Это классный способ быстро отыскивать данные по их координатам. В базовом виде здесь всего два аргумента: диапазон и порядковый номер в нём. Например, у нас есть ТОП самых популярных песен на радио, а нас просят найти, какая из них занимает 5 строчку. Вызываем функцию и в первой диалоговой рамке выбираем 1-ый пункт.

  

  Для аргумента «массив» выделяем исследуемый промежуток, у нас это D3:D12, вписываем номер искомой строки, для столбца в нашем случае указываем 0 или ЛОЖЬ.

  

  Если же мы, напротив, хотим выяснить, на каком месте ТОПа находится, например, песня Феллини, то добавляется еще одна формула – ПОИСКПОЗ. Для этого начинаем снова с ИНДЕКСА, только в качестве массива поиска указываем столбец с местами в чарте.

  Далее добавляем ПОИСКПОЗ и вписываем аргументы уже для неё:

  • искомое значение (в нашем случае Феллини);
  • просматриваемый массив, в котором оно находится ( столбик с песнями);
  • нуль или ЛОЖЬ для точного типа сопоставления.

  

  Использование «Защиты ячеек»

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

  • С помощью пункта «Формат ячеек» в контекстном меню по клику правой кнопки мыши. Откроется окно, в котором нужно выбрать вкладку «Защита». Там мы видим подсказку, как активировать инструмент.

  

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

  

  Теперь при попытке изменить что-то, программа выдаст вот такое сообщение.

  

  • Если нужна защита не всей страницы, а только какого-то участка, то выделяем его и нажимаем в той же вкладке «Разрешить изменение диапазонов» и выбираем «Создать» в появившемся окошке.

  

  

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

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

  Оставить заявку

  Закрепление заголовков строк и столбцов

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

  

  

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

  Работает это так:

  • выделяем на числовой или буквенной шкале место, перед которыми находятся закрепляемые ячейки;
  • кликаем на нужный пункт для закрепления.

  

  

  Использование «Специальной вставки» для транспонирования

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

  • Сначала выделите вашу таблицу и скопируйте через контекстное меню или горячие клавиши Ctrl + C.
  • Далее определите на листе место, куда будет помещаться транспонированный объект. Выделите его.
  • Вызовите контекстное меню и выберите пункт «Специальная вставка».
  • Отметьте галочкой «транспонировать».
  • В более новых версиях транспонирование изображено в виде пиктограммы, как указано на рисунке ниже.

  

  Вот, как в итоге заголовки поменялись местами.

  

  Использование «Мгновенного заполнения»

  Эта функция встречается в версиях 2013 года и позже.

  Крайне полезная фича, позволяющая:

  • вытягивать из массивов текста нужные слова или цифры;
  • переводить слова-числительные в числа;
  • преобразует даты в UNIX формат;
  • менять части текста местами;
  • исправлять регистр и формат и т.д.

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

  

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

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

  Поменять формат документа можно в один клик. Для этого находим в правом нижнем углу иконки около ползунка с масштабом.

  

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

  

  Существует также отдельный подраздел «Разметка страницы» с группами:

  • темы;
  • параметры;
  • вписать;
  • упорядочить.

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

  

  Переключение между таблицами

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

  • Вариант номер один – горячие клавиши Ctrl + Page Up/Page Down. При нажатии этих комбинаций происходит перелистывание на одну страницу вперед или назад. Способ годится для не очень больших книг. Для объемных документов подойдут следующие два лайфхака.
  • Переключение с помощью полосы прокрутки. В нижней части рабочего пространства, слева от ярлычков с номерами страниц находятся стрелки. Если кликнуть по ним правой кнопкой мыши, откроется оглавление, по которому можно быстро перемещаться.

  

  • И третий способ – создание гиперссылок, которые ведут с одного листа на другой. В этом нам помогут уже описанные выше ИНДЕКС и ПОИСКПОЗ. Например, у нас есть сетка на одной странице и табличка к ней на другой.

  Чтобы выставить правильно гиперссылки необходимо:

  • Добавить столбец на первой странице и ввести туда =ИНДЕКС(диапазон, из которого будем извлекать данные). У нас это первый столбик.

  

  

  • Теперь необходимо вычислить порядковый номер ячейки в этом столбце и для этого нам понадобится ПОИСКПОЗ, в котором всего три аргумента: что ищем, где и насколько тщательно. В нашем случае найти требуется название продукта, в первой колонке, совпадение точное. Получится вот такое выражение.

  

  • Чтобы преобразовать получившийся текст в ссылку на табличку, заворачиваем наше правило в функцию ЯЧЕЙКА и выбираем адрес в выпавшем списке. Не забудьте закрыть скобку в конце.

  

  • Вместо названий теперь будут стоять указания координатов. Приводим их к привычному виду с помощью формулы ГИПЕРССЫЛКА. У неё два аргумента: адрес, который у нас уже есть, и название. У нас будет «посмотреть в сводной».

  

  • Чтобы Эксель корректно понял все выражение, перед ЯЧЕЙКА добавляем “#” и знак склейки &.

  

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

  

  Применение окна контрольного значения

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

  Эта проблема решается следующим образом:

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

  

  • В открывшейся рамке нажимаем на соответствующий пункт в первой вкладке.

  

  • Теперь этот элемент постоянно находится в поле зрения поверх рабочего пространства.

  

  Заключение

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

  Похожие публикации:

  1. Где windows 10 хранит обновления
  2. Как включить все ядра на пк
  3. Как удалить аккаунт яндекс музыка на телефоне
  4. Что определяет внешняя характеристика усилителя