Магия Excel: 10 самых полезных «фишек» для работы с таблицами
Дарья Гордеева
Таблицы 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: 10 формул для работы в офисе
1 СУММ: Сложение
Сумма — самая популярная функция Excel, которой пользуются абсолютно все – и школьники, и серьезные бухгалтеры, и аналитики. Как следует из названия, функция складывает значения в указанном диапазоне. Все просто – поставьте в ячейку знак равно, выберете функцию СУММ, выделите область, в которой находятся числа для сложения, и нажмите «ОК». В ячейке появится сумма выбранных значений.
Функция может сложить как два, так и миллион чисел — настоящий супер-калькулятор.
2 ЕСЛИ: Условие
Если вы хотите задать условие, используйте формулу ЕСЛИ. Эта полезная операция может стать главным помощником при работе с таблицами в Excel. ЕСЛИ играет роль логической функции, которая производит вычисления по заданным вами критериям.
Аргументами функции являются:
- Лог_выражение — здесь надо задать параметр, по которому будут отбираться значения.
- Значение_если_истина — то, что будет на экране, если логическое условие верно.
- Значение_если_ложь — имя значения, которое не соответствует критерию, указанному в логическом выражении.
Вот небольшой пример: необходимо рассортировать данные таблицы с товаром. Зададим логическое выражение: «если стоимость больше 50». Если выражение верно, будем считать это значение как «Много», если нет — как «Мало». Нажмем «ОК».
Теперь напротив каждой ячейки со стоимостью будет указана ее характеристика. Это очень простой пример, но освоив ЕСЛИ вы сможете выполнять и сложные логические операции в Excel . Кроме того, функция понимает вложенные условия, а значит, с ее помощью можно решать действительно трудные задачи.
3 МАКС: Максимум
Как найти максимум в Excel? Используйте функцию МАКС. Это одна из самых популярных операций в офисной программе.
Для использования данной функции выберите ячейку, в которой хотите увидеть результат, поставьте в нее равно и выберете МАКС. Выделите ячейки, среди которых необходимо найти максимальное значение: помните, диапазон должен содержать не больше, чем 255 чисел.
По тому же принципу работает функция для поиска минимума – МИН.
4 СРЗНАЧ: Среднее значение
Данная формула служит для поиска среднего значения среди множества ячеек таблицы. Называется она довольно очевидно – СРЗНАЧ. Аргументом данной функции является диапазон, в котором вы хотите найти среднее арифметическое.
5. СЧЕТ: Подсчет количества значений
Функция СЧЕТ подсчитывает количество ячеек в указанном диапазоне, которые содержат числа. Например, у вас есть 9 позиций товаров, а надо узнать, сколько из них было куплено. Выделяем необходимую область со значениями и нажимаем «ОК» — функция выдаст результат. В нашем случае все можно было посчитать и вручную, а что делать, если таких значений сотни? Верно — использовать операцию СЧЕТ.
Если же вам необходимо посчитать заполненные ячейки, используйте функцию СЧЕТЗ. Она работает по аналогичному принципу, но возвращает количество ячеек со значениями в заданном диапазоне.
6 СЧЕТЕСЛИ: Подсчет определенных значений
Также в Excel есть функция СЧЕТЕСЛИ — она позволяет подсчитывать ячейки по заданному критерию. Аргументами в таком случае является не только диапазон, но и условие подсчета.
Для более сложных операций существует функция СЧЕТЕСЛИМН — в ее аргументах можно задать несколько критериев счета.
7 СУММЕСЛИ: Сложение при условии
Функция СУММЕСЛИ служит для того, чтобы складывать избранные числа из выделенного диапазона. По сути, это сочетание СУММ и ЕСЛИ. Аргументами операции являются диапазон условия, критерий и область суммирования.
Например, в таблице с товарами есть фрукты по разной цене. Нужно посчитать, сколько было куплено товаров стоимостью меньше 2.
Для этого выделим диапазон цен, укажем условие «<2» и область сложения — столбец с количеством. Функция поддерживает и более сложные условия, например, вложенные. Полностью раскрыть ее возможности можно при работе с большими таблицами в бухгалтерии или экономике.
8. СУММЕСЛИМН: Сложение по многим критериям
Если вам необходимо сложить значения, которые нельзя описать одним условиям, используйте функцию СУММЕСЛИМН. Она позволяет задать до 127 разных критериев. Данная операция — одна из самых востребованных в экономике, так как открывает действительно широкие возможности для работы с таблицами в Excel.
8 СЦЕПИТЬ: Объединение текстовых значений
Очень часто в текстовых таблицах хранится много отдельных значений, которые удобно читать вместе. Например, в таблице с ФИО. Для объединения текста служит операция СЦЕПИТЬ. Все просто: выделите необходимые строки, которые хотите соединить, и нажмите «ОК». Но помните, что пробелы придется добавить самому — сразу в функции или в строках с текстом.
10. ВПР: Поиск значений
В работе с несколькими таблицами пригодится функция ВПР. Например, у вас есть два списка — в одном номера товаров, его заказчики, цена и количество, а в другом — те же номера товаров и их описание. Такие таблицы могут быть очень большими, а ID предметов обычно стоят не по порядку и повторяются. Чтобы узнать, какой товар скрывается под определенным номером, используйте функцию ВПР.
В Excel ВПР описали так: ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки. То есть, она может найти информацию в одной таблице, опираясь на подобное значения из другой.
Аргументами ВПР являются искомое значение, таблица, номер столбца, из которого надо вытащить информацию, и интервальный просмотр. В последнем аргументе необходимо задать ЛОЖЬ, если вы ищете точное значение, и ИСТИНА для поиска приблизительного.
ВПР считается сложной функцией. Она может обработать большое количество данных и в считанные секунды найдет нужное вам значение.
Читайте также:
Как закрепить строку в Excel при прокрутке
Как выполнять расчеты времени в Excel
Shruti M
Старший аналитик-исследователь
Shruti — инженер и технофил. Она работает над несколькими трендовыми технологиями. Ее хобби включают чтение, танцы и изучение новых языков. В настоящее время она изучает японский язык.
Popular from Shruti M
Статья
25 лучших формул Excel, которые вы должны знать [обновлено]
В этой статье мы обсудим различные функции и формулы Microsoft Excel.
By
Shruti MLПоследнее обновление
12 января 2023 г.
Видеоруководство
60 лучших вопросов и ответов для интервью с аналитиками данных за 2023 год
В этой статье вы познакомитесь с самыми популярными вопросами для интервью с аналитиками данных.
Автор:
Shruti MLПоследнее обновление
12 января 2023 г.
Статья
Как использовать функцию ВПР в Excel? Пошаговое руководство
В этой статье рассмотрено руководство по использованию функции ВПР в Excel для точного и приблизительного совпадения.
Автор:
Shruti MLПоследнее обновление:
12 января 2023 г. .
Автор:
Shruti MLПоследнее обновление:
29 декабря 2022 г.
Видеоруководство
80 лучших вопросов и ответов из интервью с Hadoop
В этой статье перечислены наиболее распространенные вопросы, задаваемые в ходе интервью с разработчиками Hadoop, и типовые ответы.
Автор
Shruti MLПоследнее обновление
12 декабря 2022 г.
Статья
Как удалить дубликаты в Excel? Пошаговое руководство
В этой статье мы обсудим четыре простых подхода к удалению дубликатов в Excel.
By
Shruti MLПоследнее обновление
28 октября 2022 г.
Видеоруководство
10 лучших профессий в сфере кибербезопасности в 2023 году: информация о карьере и зарплате вы можете начать свою карьеру.
Автор:
Shruti MLПоследнее обновление:
24 января 2023 г.
Статья
Ваш путеводитель по лучшему набору идей для выпускных проектов по информатике
В этой статье представлены лучшие идеи проектов по информатике, которые вы можете реализовать в твой последний год.
Автор:
Shruti MLПоследнее обновление
7 октября 2022 г.
Статья
Как создать абсолютную ссылку в Excel?
В этой статье мы расскажем вам о создании абсолютной ссылки в Excel, ее использовании и многом другом.
By
Shruti MLПоследнее обновление
9 ноября 2022 г.
Статья
Все, что вам нужно знать об очистке данных Excel
Узнайте, как очистить данные с помощью Excel.
Автор:
Shruti MLПоследнее обновление
24 января 2023 г. Глубокое обучение.
By
Shruti MLПоследнее обновление
15 ноября 2022 г.
Видеоруководство
Установка виртуальной машины Cloudera Quickstart — лучший способ
установить его.
Автор:
Shruti MLПоследнее обновление:
17 января 2023 г.0003
Shruti MLПоследнее обновление
21 июля 2022 г.
Видеоруководство
Лучшее руководство по лучшим вопросам для интервью по кибербезопасности
Эта статья о вопросах для интервью по кибербезопасности познакомит вас с набором из 50 лучших вопросов и ответов.
Автор:
Shruti MLПоследнее обновление
17 января 2023 г.
Статья
Что такое СЧЁТ в Excel и как использовать функцию СЧЁТ?
В этой статье мы обсудим функцию СЧЁТ в Excel.
Автор:
Shruti MLПоследнее обновление:
27 октября 2022 г.
Статья
Советы по выпускному проекту, которые спасут положение
В этой статье мы дадим вам семь жизненно важных советов, которые помогут вам успешно закончить последний год проект.
By
Shruti MLПоследнее обновление
8 ноября 2022 г.
Статья
ОБНОВЛЕНИЕ SQL: Урок по обновлению таблиц базы данных
В этой статье о команде SQL Update мы узнаем, как выполнить обновление существующие записи через SQL.
By
Shruti MLПоследнее обновление
16 сентября 2022 г.
Статья
Как сортировать данные в Excel: лучшее руководство
В этой статье вы узнаете, как сортировать данные в Excel.
Кроме того, вы также узнаете, как фильтровать данные.
Автор:
Shruti MLПоследнее обновление
29 июня 2022 г.
Видеоурок
Учебник по Mapreduce: все, что вам нужно знать
В этой статье объясняется концепция MapReduce.
By
Shruti MLПоследнее обновление
12 сентября 2022 г.
Статья
Как транспонировать данные Excel? Три метода, которые необходимо знать
В этой статье вы узнаете о различных способах транспонирования данных Excel.
By
Shruti MLПоследнее обновление
13 сентября 2022 г.
Видеоурок
Введение в случайный лес в R
В этой статье мы рассмотрим алгоритм случайного леса в R с нуля.
на
Shruti Mlast, обновленный
15 февраля, 2022
Статья
Учебник форматов файлов Hadoop
Узнайте все о различных типах данных в Hadoop
на
2022
Статья
Анализ «что, если» в Excel: универсальное решение для управления сценариями в Excel
Вот все, что вам нужно знать об анализе «что, если» в Excel.
By
Shruti MLПоследнее обновление
15 июля 2022 г.
Видеоурок
Учебник по пряже
YARN — это аббревиатура от Yet Another Resource Negotiator. Хотите узнать больше? Посмотрите этот урок!
By
Shruti MLПоследнее обновление
11 октября 2022 г.
Видеоруководство
Ваше универсальное решение для изучения слияния писем в Excel и его реализации
Узнайте все о слиянии писем в Excel.
By
Shruti MLПоследнее обновление
13 сентября 2022 г.
Статья
Параллельная обработка Spark
Изучите наиболее важные элементы Spark, то есть параллельную обработку.
Автор:
Shruti MLПоследнее обновление
12 декабря 2022 г.
Статья
Типы данных в MongoDB: распространенные типы данных MongoDB
Узнайте о некоторых наиболее часто используемых типах данных в MongoDB на примерах.
По
Shruti MLПоследнее обновление
4 октября 2022 г.
Видеоруководство
Ваше универсальное решение, которое поможет вам научиться конвертировать PDF в Excel
Узнайте, как конвертировать PDF в Excel и сохранить данные в таблице данных Excel
By
Shruti MLПоследнее обновление
12 сентября 2022 г.
Статья
Ваше универсальное решение для изучения двухъядерных очередей в структуре данных с нуля
Изучите двухуровневую структуру данных, чтобы понять ее функции и приложения
By
Shruti MLПоследнее обновление
14 октября 2021 г.
Видеоруководство
Как установить Hadoop на Ubuntu
Этот блог представляет собой руководство по установке Hadoop на Ubuntu.
Автор:
Shruti MLПоследнее обновление
2 июня 2021 г.
Видеоурок
PHP Вопросы для интервью
Вот 40 лучших вопросов и ответов на PHP-интервью, которые помогут вам пройти собеседование.
По
Shruti MLПоследнее обновление
4 ноября 2022 г.
Видеоруководство
Лучшие компьютерные взломы всех времен
Вы познакомитесь с самыми смертоносными кибератаками, с которыми когда-либо сталкивалось человечество.
By
Shruti MLПоследнее обновление
5 марта 2021 г.
Видеоруководство
HBase Tutorial
HBase — это система управления базами данных.
В этом руководстве по HBase кратко рассказывается, что такое HBase и зачем его использовать.
By
Shruti MLПоследнее обновление
27 сентября 2022 г.
Статья
Лучшее руководство по созданию информационной панели Excel
В этой статье мы объясним, как создать информационную панель Excel.
Автор:
Shruti MLПоследнее обновление:
20 сентября 2022 г.
Статья
Учебное пособие по Apache Storm: Введение0003
Shruti MLПоследнее обновление
12 декабря 2022 г.
Статья
Документ обновления MongoDB
Узнайте, как обновить документ в MongoDB.
By
Shruti MLПоследнее обновление
16 декабря 2022 г.
15 самых распространенных функций Excel, которые вы должны знать, и как их использовать
Перейти к содержимому 15 наиболее распространенных функций Excel, которые вы должны знать, и как их использоватьMicrosoft Excel — одно из самых известных компьютерных приложений. Это изменило то, как люди и компании работают с данными.
Таким образом, изучение Excel может помочь как в карьере, так и в личных потребностях.
Excel работает с использованием функций, а их около 500! Они варьируются от базовой арифметики до сложной статистики.
Если вы новый пользователь Excel, это количество может быть довольно пугающим.
Итак, мы здесь, чтобы помочь вам! 🤝
Мы собрали 15 наиболее распространенных и полезных функций Excel , которые вам необходимо изучить.
Мы также подготовили для вас практическую рабочую тетрадь с примерами. Загрузите его здесь.
Начнем!
Содержание
Что такое функции Excel?
Excel используется для вычисления и обработки чисел и текста. Для этого вы используете формул !
Формулы — это выражения, которые сообщают Excel, что вы хотите делать с данными. Они начинаются с символа равенства (=) , за которым следует комбинация операторов и функций .
Что такое операторы?
Это символы, которые определяют тип вычисления, которое вы хотите выполнить для элементов формулы.
Например, чтобы сложить два числа, введите в ячейку «=1+1». Как только вы нажмете Введите , Excel запустит формулу и вернет результат, равный 2.
Вот несколько примеров распространенных операторов:
Excel автоматически обрабатывает содержимое ячеек, начинающихся с (=), как формулы.
Это также применимо, когда вы начинаете ячейку с символов плюс (+) или минус (-) .
Вы можете обойти это, добавив начальный апостроф (‘) . Вот как вы можете отображать формулы в виде текста, как в таблице выше.
Порядок работы и использование круглых скобок в формулах Excel
Как правило, Excel следует PEMDAS при расчете формул. PEMDAS означает сначала скобки , затем показатели степени, затем умножение и деление, затем сложение и вычитание .
Что такое функции?
Это предварительно определенные процессы в Excel. Каждая функция в Excel имеет уникальных имен и специальные входы . Функция принимает эти входные данные и выполняет соответствующие вычисления.
Входные данные или аргументы функции Excel всегда заключаются в круглые скобки.
Например, это синтаксис для функции MAX :
=MAX(число1, [число2], …)
Список чисел, для которых вы хотите найти максимальное значение, заключен в круглые скобки.
.
Использование ячейки или диапазона в качестве ввода
Когда вы узнаете больше об Excel, вы обнаружите, что формулы Excel редко состоят из отдельных чисел, только как в формуле «=1+1».
Таким образом, ссылок на ячейки важны в Excel, и вы можете узнать больше, нажав здесь.
Хорошо! Вы только что узнали, как работает функция в Excel.
Давайте погрузимся прямо в список! 🤿
Мы начнем с основных функций Excel, а затем перейдем к более продвинутым функциям.
Основные математические функции (начальный уровень ★☆☆)
1. СУММ
Это первая функция в Excel, которая нужна большинству новых пользователей. Как следует из названия, функция СУММ суммирует все значения в указанной группе ячеек или диапазоне.
Синтаксис: =СУММ(число1, [число2], …)
Попробуйте это в рабочей тетради.
Если вы хотите получить общий балл за тест для каждого учащегося, вы можете использовать функцию СУММ.
В этом случае входной диапазон будет представлять собой все четыре балла за тест для каждого учащегося.
1. Введите эту формулу в ячейку F2 :
=СУММ(B2:E2)
E2)» намного проще.
2. Нажмите Введите . Затем Excel оценивает формулу, и ячейка возвращает общее число, равное 360.
3. Скопируйте это для остальных учащихся или перетащите вниз маркер заполнения .
Обратите внимание, что функция СУММ игнорирует ячейки, содержащие текст . (“X” означает, что учащийся не смог пройти тест)
Большинство основных математических функций в Excel игнорируют нечисловые значения, такие как текст, дата и время.
2. СЧЁТ
Следующая функция СЧЁТ . Он возвращает количество ячеек, содержащих числовые значения в пределах входного диапазона.
Синтаксис : =COUNT(значение1, [значение2], …)
1.
Чтобы получить количество тестов, пройденных каждым учащимся, используйте эту формулу в ячейке G2 :
=СЧЕТ(B2:E2)
2. Нажмите Введите и заполните строки ниже.
Если вы хотите включить в подсчет нечисловые значения, вы можете использовать функцию COUNTA . Чтобы подсчитать количество пустых ячеек, вы можете использовать функцию СЧИТАТЬПУСТОТЫ .
Узнайте больше о функции COUNT и ее вариантах здесь.
3. СРЕДНЕЕ
Среднее значение списка чисел равно сумме, деленной на количество чисел в этом списке.
Это достаточно просто, чтобы подсчитать баллы за викторину. У вас уже есть СУММА и СЧЕТЧИК тестов для каждого учащегося.
Но еще проще использовать функцию СРЗНАЧ в Excel.
Синтаксис : =СРЗНАЧ (значение1, [значение2], …)
1. Введите это в ячейку h3 :
=СРЗНАЧ(B2:E2)
и заполните поле Enter ряды ниже.
Функция СРЗНАЧ также имеет варианты для более сложных вычислений. Кликните сюда, чтобы узнать больше.
Логические функции (средний уровень — ★★☆)
Давайте немного повысим уровень сложности.
Логическая функция в Excel позволяет выполнять сравнения и использовать результаты для изменения способа вычисления формулы.
4. ЕСЛИ
Функция ЕСЛИ — очень популярная функция в Excel, и ее довольно легко освоить.
Синтаксис : =IF(логическая_проверка, значение_если_истина, [значение_если_ложь])
Эта функция проверяет, является ли логический тест либо ИСТИНА, либо ЛОЖЬ. Затем он возвращает указанное значение на основе результата.
Используя средний балл каждого учащегося, попытайтесь поставить оценки «ПРОШЕЛ» или «НЕ ПРОШЕЛ». Предположим, что проходной балл для этого класса равен 60.
1. Начните формулу в ячейке C2 с «=ЕСЛИ(»
больше или равно (>=) проходному баллу 60.
2. Итак, формула принимает вид:
=ЕСЛИ(B2>=60,
Если сравнение возвращает ИСТИНА , то формула должна вернуть текст «ПРОШЕЛ». Таким образом, аргумент значение_если_истина должен
И если он возвращает FALSE , то аргумент value_if_false должен быть «FAIL»
3. Таким образом, формула принимает следующий вид: ”“FAIL”)
4. Нажмите Enter и заполните строки ниже.
Что, если бы вам нужно было выставлять оценки по шкале, а не просто «ПРОШЕЛ» и «НЕ ПРОШЕЛ»?
Для этого необходимо использовать несколько критериев или логических тестов. Хотя это возможно с помощью вложенных функций ЕСЛИ , это может очень быстро запутаться. Вместо этого вы можете использовать функцию IFS .
5. Функция IFS
Функция IFS была введена в Excel 2016 для замены вложенных функций IF .
Эта функция работает путем оценки первый логический тест или критерии . Он возвращает соответствующее значение, если оно TRUE. Но если оно FALSE, функция переходит к оценке вторых критериев и так далее.
📖 Другими словами, функция ЕСЛИ выводит значение, соответствующее первому заданному критерию, который является истинным .
Синтаксис : =IFS(лог_выражение1, значение_если_истина1, [лог_выражение2], [значение_если_истина2],..)
1. Сначала формула должна проверить, соответствует ли средний балл (столбец B) больше или равен 90. Если да, он должен вернуть «A» .
=IFS(B2>=90,”A”,
2. Если нет, то следует проверить, больше ли средний балл или равен 80. Если да, он должен вернуть “B” , Если вы сделаете это до уровня D, формула станет следующей:
=IFS(B2>=90,A,B2>=80,B,B2>=70,C,B2>= 60, «D»,
3.
За последнюю оценку «F» поставить «ИСТИНА» для логического теста
Функция IFS будет оценивать последний указанный критерий только в том случае, если все предыдущие логические значения были ЛОЖНЫ. Таким образом, вы можете установить последний критерий всегда равным TRUE , что сделает его оператором « поймать все ».
Тогда окончательная формула будет следующей:
=IFS(B2>=90,A,B2>=80,B,B2>=70,C,B2>=60,D, ИСТИНА, “F”)
PRO-СОВЕТ:
Вы можете использовать абсолютных ссылок на ячейки и справочной таблицы при работе с длинными формулами.
Таким образом, вам не нужно пересматривать все аргументы в формуле, если вам нужно изменить некоторые значения.
Например, с помощью приведенной ниже таблицы и формулы можно легко изменить используемую шкалу оценок.
=ЕСЛИ(B2>=$H$2,$F$2,B2>=$H$3,$F$3,B2>
=$H$4,$F$4,B2>=$H$5,$ F$5,TRUE,$F$6)
Текстовые функции (средний уровень — ★★☆)
В следующем разделе вы увидите, как можно использовать Excel для работы с текстом.
В рабочем листе «Список классов» рабочей тетради полное имя каждого учащегося указано в столбце A . Ваша цель — переставить их из «имя фамилия» в «фамилия_имя» в столбце F .
Для этого сначала нужно извлечь имя и фамилию из столбца A.
6. НАЙТИ
Имена разделены пробелом » « . Таким образом, вы должны определить положение пробела в каждой текстовой строке в столбце A.
Функция НАЙТИ в Excel возвращает номер или позицию указанного символа или подстроки в другой текстовой строке.
Синтаксис : =НАЙТИ(найти_текст, внутри_текста, [начальный_номер])
Чтобы получить позицию пробела ” “ , введите эту формулу:
=НАЙТИ(” “,A2)
Далее рассмотрим функцию ДЛСТР .
7. LEN
Эта функция возвращает количество символов в текстовой строке.
Синтаксис : =LEN(текст)
Чтобы получить количество символов в имени каждого учащегося:
=LEN(A2)
Теперь вы можете перейти к извлечению имени и фамилии с помощью MID функция в Экселе.
8. MID
Эта функция извлекает заданное количество символов из середины текстовой строки.
Синтаксис : = MID(text, start_num, num_chars)
Это одна из трех текстовых функций, которые используются для извлечения текста. Два других — LEFT и RIGHT — извлекают текст из начала и конца текстовой строки соответственно.
Имя начинается с самого первого символа текстовой строки. Итак, вы извлекаете, начиная с позиции 1 . Тогда длина имени задается позицией пробела минус 1 .
Итак, формула для извлечения имени или первого слова из текстовой строки:
=MID(A2,1,B2-1)
Или вы можете выразить ее напрямую, используя формулу НАЙТИ ранее.
=MID(A2,1,FIND(” “,A2)-1)
Для фамилии вы можете извлечь ее, начиная с позиции символа пробела плюс 1 . Его длина равна длине всей текстовой строки за вычетом позиции пробела 9.0446 .
=СРЕД(A2,B2+1,C2-B2)
Или, используя формулы НАЙТИ и ДЛСТР ранее: ,ДЛСТР(A2)-НАЙТИ(” “,A2))
Теперь вы можете объединить фамилию и имя в нужном порядке, используя функцию СЦЕП .
9. СЦЕПИТЬ
Подобно IFS , СЦЕПИТЬ — еще одна недавно введенная функция в Excel 2016. Она заменила старую функцию СЦЕПИТЬ .
Синтаксис : =CONCAT(текст1, [текст2],…)
Объедините фамилию и имя с запятой и символом пробела «,» между ними.
=CONCAT(E2″, “,D2)
PRO-TIP:
В приведенном выше примере вы использовали вспомогательные столбцы для FIND , LEN для построения 45 MID 904 окончательную формулу и визуализировать, как она работает.
В реальных приложениях вы можете использовать одну длинную формулу, чтобы получить такие результаты:
=СЦЕПИТЬ(СРЕДН(A2,НАЙТИ(” “,A2)+1,ДЛСТР(A2)
-НАЙТИ(” “,A2)),”, “,СРЕД(A2,1,НАЙТИ(” “, A2)-1))
Функции поиска и ссылок (продвинутый уровень — ★★★)
В этом заключительном разделе мы сосредоточимся на функциях, которые позволяют вам искать определенные точки данных и ссылаться на них.
Взгляните на рабочий лист «Расписание» .
10. СТОЛБЦ
Функция СТОЛБЦ в Excel возвращает номер столбца данной ячейки.
Синтаксис : =СТОЛБЦ([ссылка])
Давайте попробуем назначить конкретные даты для каждого теста. Например, вы можете захотеть, чтобы викторины проводились каждый понедельник. Это означает, что первая дата викторины должна смещаться на 1 неделю или 7 дней для каждой последующей даты викторины.
Вы можете использовать номер столбца для умножения 7-дневного смещения для каждой недели следующим образом:
=$B$2+(COLUMN()-2)*7
Два (2) вычитается из номера столбца так что последовательность начинается с 1.
Вы также можете получить этот результат, используя гораздо более простой «=B2+7» , поскольку вы добавляете только фиксированное количество дней к каждой дате. 🤔
Но, используя функцию КОЛОННА , можно создавать сложные узоры.
Возьмем, к примеру, этот шаблон:
Викторины по-прежнему проводятся каждый понедельник. Но каждую третью неделю они проводятся в среду.
Вот формула для этого шаблона:
=$B$2+(COLUMN()-2)*7+IF(MOD(COLUMN()-1,3)=0,2,0)
Функция MOD в Excel возвращает остаток после деления числа на заданный делитель. Это часть Math & Trig группы функций.
В эту группу входят другие забавные функции, такие как ABS , которая возвращает абсолютное значение числа, и ROUND , которая округляет число до указанного количества цифр.
Узнайте больше о функциональных группах в конце этой статьи!
Каспер Лангманн , специалист по Microsoft Office
11.
ROWДалее рассмотрим функцию ROW . Он работает точно так же, как COLUMN , но вместо этого возвращает номер строки.
Синтаксис : =СТРОКА([ссылка])
В следующем примере вы назначите планы рассадки. Вы можете попробовать разные варианты рассадки, используя функцию ROW .
Предположим, что R1C1 — это место, ближайшее к столу учителя.
1. Вы можете рассадить студентов одно за другим и в две колонки:
=CONCAT(“R”,MOD(ROW()-6,3)*2+1″,C”,INT( (СТРОКА()-6)/3)*2+1)
2. Или они могут располагаться в строках по 3 и в столбцах по 2
=CONCAT(“R”,MOD(ROW()-6, 2)*2+1″,C”,INT((ROW()-6)/2)*2+1)
3. Вы также можете посадить их в самые дальние ряды:
=CONCAT(” R», MOD(ROW()-6,2)*4+1»,C»,INT((ROW()-6)/2)*2+1)
4. Или в самых дальних столбцах:
=CONCAT(“R”,MOD(СТРОКА()-6,3)*2+1″,C”,ЦЕЛОЕ((СТРОКА()-6)/3)*4+1)
Вручную создание шаблонов сидения для небольших наборов, подобных этому, легко.
Но формула, подобная показанной выше, определенно помогает, особенно для больших наборов, таких как 50, 100 или даже больше.
Функции COLUMN и ROW редко используются сами по себе. Как и IF и IFS , вы используете их с другими функциями, чтобы изменить способ вычисления формулы.
12. ПОИСКПОЗ
Теперь откройте рабочий лист «Поиск» .
В следующих нескольких примерах вы создадите функцию поиска , которая позволит учащимся искать свои имена. Затем они могут увидеть свои баллы за прошлые викторины и назначенные им места для следующих викторин.
Для начала вы будете использовать функцию ПОИСКПОЗ . Он ищет указанный элемент в заданном диапазоне ячеек. Затем он возвращает относительную позицию первого совпадения.
Синтаксис : =ПОИСКПОЗ(искомое_значение, искомое_массив, [тип_совпадения])
- искомое_значение — это элемент, который вы хотите найти.
Итак, установите это на ячейку B2 . - lookup_array — это диапазон или массив таблиц, в которых вы хотите выполнить поиск. Используйте F2:F7 из рабочего листа «Список классов» .
- Для match_type установите это значение равным нулю, чтобы функция искала точное соответствие . (Подробнее о ПОИСКПОЗ и различные типы соответствия в этой статье)
Итак, установите это на ячейку B2 .Тогда формула будет выглядеть так:
=ПОИСКПОЗ(B2,’Список классов’!F2:F7,0)
Однако она работает только правильно если имя введено точно так, как оно написано в столбце F списка классов .
Чтобы исправить это, вы можете использовать подстановочный знак звездочки «*» , чтобы работал поиск по имени или фамилии.
Вы также можете заключить формулу в Функция IFNA .
Таким образом, если формула не может найти данное имя в таблице, она вернет фразу типа «Результат не найден».
13. ИНДЕКС
Функция ИНДЕКС извлекает значение из заданного массива таблиц на основе предоставленных номеров строк и столбцов.
Синтаксис : =ИНДЕКС (массив, номер_строки, [номер_столбца])
Подобно примеру ПОИСКПОЗ , вам необходимо указать, где находится диапазон или массив для поиска.
На row_num , вы можете использовать более ранний результат MATCH в ячейке B5 . Затем для col_num используйте 1 для First Name :
=INDEX(‘Class List’!D2:E7,B5,1)
И установите col_num на 2 для Last Name .
=ИНДЕКС(‘Список классов’!D2:E7,B5,2)
Вот так у вас есть рабочая формула поиска 🔍!
Это всего лишь небольшой пример бесчисленных возможностей использования ИНДЕКС и СОВПАДЕНИЕ Комбинация .
Нажмите здесь, чтобы увидеть больше примеров!
14. ВПР
Функция ВПР в Excel работает аналогично комбинации ИНДЕКС и ПОИСКПОЗ . Он быстрее настраивается, но менее универсален. VLOOKUP также работает, только если ваш массив поиска находится в крайнем левом углу справочной таблицы.
Синтаксис : = ВПР (искомое_значение, табличный_массив, индексный_номер столбца, [диапазон_просмотра])
На этот раз вы будете использовать результат «Имя» (ячейка B6) в качестве lookup_value . Используйте это и VLOOKUP для получения результатов данного учащегося из рабочего листа «Оценки викторины» .
=ВПР($B$6,’Оценки викторины’!$A$2:$E$7,COLUMN(),FALSE)
Для назначения места используйте результат Фамилия, за которым следует подстановочный символ звездочки.
=ВПР($B$7&”*”,Расписание!$A$6:$E$11,СТОЛБЦ(),ЛОЖЬ)
15.
ДВССЫЛПоследняя функция, о которой вы узнаете сегодня, также является одной из самых мощных в Excel.
ДВССЫЛ позволяет указывать ссылки на ячейки с помощью текстовых строк.
СИНТАКСИС : =ДВССЫЛ(ref_text, [a1])
Например, вместо ввода «=A1» можно ввести «=ДВССЫЛ(«A»&1) . Это означает, что вы можете динамически изменять ссылки.
Возьмем ИНДЕКС И СООТВЕТСТВУЙТЕ 9Формула 0446, которую вы использовали для получения фамилии. Вы можете получить тот же результат, используя эту формулу:
=ДВССЫЛ(“‘Список классов’!”&”E”&(B5+1))
Функция ДВССЫЛ открывает так много возможностей с динамическими ссылками в Экселе. Я высоко ценю эту статью как подробный учебник по INDIRECT.
Вот и все – Что теперь?
Как вы только что узнали, Excel предлагает множество различных функций на выбор. К счастью, Excel объединил их всех в 
Вы можете найти функцию Excel, используя ключевые слова для поиска, или вы также можете выбрать из раскрывающихся списков категорий.
Например, щелкните группу Финансы , чтобы найти функции, которые могут помочь вам рассчитать такие элементы, как чистая приведенная стоимость, будущая стоимость, совокупные уплаченные проценты, совокупная уплаченная основная сумма и т. д.
Вы также можете щелкнуть Дополнительные функции что открывает еще больше возможностей для расширенных формул Excel.
Например, группа Statistical полезна, если вам нужно вычислить статистическое значение. Сюда входят функции для максимального значения, минимального значения, прогнозируемого значения, значения гамма-функции и т. д. Вы можете вставить кумулятивную функцию распределения и другие полезные инструменты для анализа данных .
Узнайте, как использовать эти формулы и многое другое, подписавшись на мой бесплатный онлайн-курс Excel.