10 наиболее полезных функций при анализе данных в Excel
Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.
Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:
— Искомое значение — это то значение, которое мы будем искать в таблице с данными
— Таблица — диапазон данных, в первом столбце которого мы будем искать искомое значение
— Номер столбца — этот параметр обозначает, на какое количество столбцов надо сдвинуться вправо в таблице для получения результата
— Интервальный просмотр — Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.
Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью «Функция ВПР в Excel».
[expert_bq id=»1570″]Но все делается проще кликнуть на ячейку, в правом нижнем углу появится маркер заполнения, нажать на него и, не отпуская кнопку мышки, потянуть вниз. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 7. Ежемесячно я подаю своему шефу данные о продажах единиц продукции в графической форме. Каждый месяц мне требуются новые данные о количестве проданного товара. Мне хотелось бы, чтобы график автоматически обновлялся. Есть ли простой способ реализации этого?Excel. Примеры использования функции СМЕЩ (OFFSET)
В статье мы рассмотрели основы работы с Excel, с того как начать писать формулы. Привели примеры самых распространенных формул, с которыми очень часто приходится работать большинству, кто работает в Excel.
Вам также могут быть интересны следующие статьи
СПАСИБО!
Хоть кто-то нормально разъяснил как эта полезная функция работает.
СПАСИБО!
Огромная благодарность за очень доступное объяснение этой функции для простых людей.
Думаю, не один я «сломал» голову, разбираясь с ней на офф. сайте «мелкомягких». Так держать!
Ренат, а какой софт вы используете для записи видео с монитора и создания гиф анимации?
Спасибо, большое.
Частенько вставала проблема динамических диапазонов. Теперь есть простое решение. Сайт Ваш очень понравился. Побольше бы статей в нем еще, было бы вообще прекрасно.
Ренат, а вот если нам необходимо посчитать не среднюю сумму за последнюю неделю, а сумму по первому и последнему дню, не поможете решить такую вот задачку?
Если в рамках последнего примера, то вот что пришло первое на ум:
=СУММ(СМЕЩ(B2;СЧЁТЗ(B2:B300)-7;0);СМЕЩ(B2;СЧЁТЗ(B2:B300)-1;0))
В данном случае будет основное ограничение в том, что дни должны идти по порядку один за другим.
Ренат добрый день,
столкнулся с задачей, задать в Excel следующее условие:
если в ячейке A1 стоит «да», то в ячейке А2 должно стоять «нет», а если в ячейке А1 стоит «нет» то в ячейке А2 должен появиться выбор из списка «да» или «нет».
Есть ли решение без программирования в VBA ?
Добрый день, Родион. Без VBA на ум приходит только вариант с наложением двойного условия на ячейку A2 и на диапазон выпадающего списка. Чтобы список менял свое содержимое, в зависимости от значения ячейки A1. Большой недостаток, данного метода, что после использования выпадающего списка в ячейке A2, формулы слетают. Пример в прикрепленном файле с условиями на выпадающий список
Есть данные продаж — по строкам список магазинов, по столбцам — название товара, внутри соответственно количество проданного товара.
Необходимо данные предоставить по строкам — Магазин, товар, кол-во.
Коллеги показывают использование функции СМЕЩ.
Как на ваш взгляд — это наиболее рациональный подход?
Добрый день,
Думаю, использование функции СМЕЩ — хороший вариант, но в вашем случае, я бы использовал функцию ИНДЕКС
Пришлите, пожалуйста, файл с примером, посмотрим. Судя по тому, что вы написали, все должно работать
Ренат, это одна из хитростей Excel. Функция ЧСТРОК позволяет сказать количество строк, которые находятся на пересечении двух массивов (в нашем случае массива и ячейки). Пробел указывает на то, что нам необходимо именно пересечение
Добрый день, чтобы определить положение последнего элемента в диапазоне, необходим непрерывный массив (без пустых ячеек). Если все таки в диапазоне имеются пробелы, роль такого массива может сыграть столбец с датами. Тогда формула примет вид: =СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(A2:A300)-7;0;7;1))
Здравствуйте, Ренат! Спасибо за помощь, новая формула прекрасно работает,
все правильно считает, в том числе и при наличии пустых ячеек. Как раз то, что мне
и было нужно. Ещё раз благодарю за вашу помощь.
Владислав, если вам нужно найти следующую после максимальной величину, воспользуйтесь функцией НАИБОЛЬШИЙ, которая возвращает требуемый наибольший элемент в массиве. Подробнее про эту функцию можно прочитать в статье про функцию НАИБОЛЬШИЙ/ НАИМЕНЬШИЙ
Здравствуйте. Помогите с проблемой.
Необходимо, чтобы когда менеджер выбирает день месяца — в соседнем столбце в выпадающем списке были доступны только те машины, которые отметили как «доступные» в этот день (и еще надо, чтобы пустых строк не было в выпадающем перечне)
Изучаем функцию СМЕЩ | Exceltip
- Ссылка: отправная точка, эта ячейка, относительно которой необходимо смещаться
- Смещение по строкам и столбцам: Указывает, на какое количество строк или столбцов необходимо сместиться от отправной точки. Может иметь положительные и отрицательные значения, а также равняться нулю.
- Высота и ширина: Это размер диапазона, который вы хотите вернуть. К примеру, 4X3 вернет диапазон содержащий 4 строки в высоту и 3 столбца в ширину.
Ещё одна удивительная особенность — формула ИНДЕКС возвращает ссылку на ячейку, а не значение ячейки. Внимательный читатель, конечно же, запальчиво воскрикнет, что, мол за ерунда, почему тогда предыдущий пример не вернул нам С3 , а вернул 9? Дело в том, что данный факт маскируется оператором присваивания (знак равно), с которого начинается любая формула.
Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)
Довольно часто-встречающаяся задача: подсчитать не сумму значений в ячейках, а количество таких ячеек, удовлетворяющих определенным условиям. Иногда, условий очень много.
В этой же примере попробуем посчитать количество наименования товара с ценой больше 90 (если окинуть взглядом, то и так можно сказать, что таких товаров 2: мандарины и апельсины).
Для подсчета товаров в нужной ячейке написали следующую формулу (см. выше):
B2:B5 — диапазон, по которому будут проверять, по заданному нами условию;
«>90» — само условие, заключается в кавычки.
Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.
Здесь все осталось таким же, кроме еще одного условия (C2:C6;»). Кстати, таких условий может быть очень много!
Понятно, что для такой маленькой таблицы никто не будет писать такие формулы, а вот для таблицы из нескольких сотен строк — это уже совсем другое дело. Для примера же эта таблица — более чем наглядна.
[expert_bq id=»1570″]Excel программируемый калькулятор, где можно без труда вычислить максимальные и минимальные значения, средние показатели, проценты и многое другое. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В первую очередь нужно активизировать любую ячейку, осуществив двойной клик по ней. Также можно использовать верхнюю строку. Ввод формулы обязательно начинается со знака «=», далее вписывается числовое значение и нажимается Enter. В выбранной ячейке отображается результат.Как сделать формулу в Excel: пошаговая инструкция
В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле ЕСЛИ(B2>500;»Да»;»Нет») первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр («Да») — функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.
Как работает функция?
Программа изучает заданный пользователем критерий, после переходит в выделенную ячейку и отображает полученное значение.
С одним условием
После проверки ячейки А1 оператор сравнивает ее с числом 70 (100). Это заданное условие. Когда значение больше 50 (130), появляется правдивая надпись «больше 50». Нет – значит, «меньше или равно 130».
Пример посложнее: необходимо из таблицы с баллами определить, кто из студентов сдал зачет, кто – идет на пересдачу. Ориентир – 75 баллов (76 и выше – зачет, 75 и ниже – пересдача).
В первой ячейке с результатом в правом углу есть маркер заполнения – протянуть полученное значение вниз для заполнения всех ячеек.
С несколькими условиями
Обычно в Excel редко решаются задачи с одним условием, и необходимо учитывать несколько вариантов перед принятием решения. В этом случае операторы ЕСЛИ вкладываются друг в друга.
Здесь проверяется два параметра. Когда первое условие верно, оператор возвращает первый аргумент – ИСТИНУ. Неверно – переходит к проверке второго критерия.
Нужно выяснить, кто из студентов получил «отлично», «хорошо» и «удовлетворительно», учитывая их баллы:
- В выделенную ячейку вписать формулу =ЕСЛИ(B2>90;»Отлично»;ЕСЛИ(B2>75;»Хорошо»;»Удовлетворительно»)) и нажать на кнопку «Enter». Сначала оператор проверит условие B2>90. ИСТИНА – отобразится «отлично», а остальные критерии не обработаются. ЛОЖЬ – проверит следующее условие (B2>75). Если оно будет правдиво, то отобразится «хорошо», а ложно – «удовлетворительно».
Также формула может иметь вид =ЕСЛИ(B2>90;»Отлично»;ЕСЛИ(B2>75;»Хорошо»;ЕСЛИ(B2>45;»Удовлитворительно»))), где каждый критерий вынесен отдельно.
Можно делать любое количество вложений ЕСЛИ (до 64-х), но рекомендуется использовать до 5-ти, иначе формула будет слишком громоздкой и разобраться в ней будет уже очень сложно.
С несколькими условиями в математических выражениях
Логика такая же, как и в формуле выше, только нужно произвести математическое действие внутри оператора ЕСЛИ.
Есть таблица со стоимостью за единицу продукта, которая меняется в зависимости от его количества.
Цель – вычесть стоимость для любого количества продуктов, введенного в указанную ячейку. Количество – ячейка B8.
Формула для решения данной задачи принимает вид =B8*ЕСЛИ(B8>=101;12;ЕСЛИ(B8>=50;14;ЕСЛИ(B8>=20;16;ЕСЛИ(B8>=11; 18;ЕСЛИ(B8>=1;22;»»))))) или =B8*ЕСЛИ(B8>=101;B6;ЕСЛИ(B8>=50;B5;ЕСЛИ(B8>=20;B4;ЕСЛИ(B8>=11;B3;ЕСЛИ(B8>=1;B2;»»))))).
Было проверено несколько критериев и выполнились различные вычисления в зависимости от того, в какой диапазон суммы входит указанное количество продуктов.
С операторами «и», «или», «не»
Оператор «и» используется для проверки нескольких правдивых или нескольких ложных критериев, «или» – одно условие должно иметь верное или неверное значение, «не» – для убеждения, что данные не соответствуют одному условию.
Операторы «и», «или» теоретически могут проверить до 255 отдельных критериев, но такое количество сложно создавать, тестировать и изменять, поэтому лучше использовать до 5-ти. А «нет» – только один критерий.
Для проверки ячейки на наличие символов
Иногда нужно проверить ячейку, пустая она или нет. Требуется это для того, чтобы формула не отображала результат при отсутствии входного значения.
Пустые двойные кавычки в формуле означают «ничего». То есть: если в A2 нет символов, программа выводит текст «пустая», в противном случае будет «не пустая».
Для проверки ячейки ЕСЛИ часто используется в одной формуле c функцией ЕПУСТО (вместо пустых двойных кавычек).
Когда один из аргументов не вписан в формулу
Второй аргумент можно не вводить, когда интересующий критерий не выполняется, но тогда результат будет отображаться некрасиво.
Как вариант – можно вставить в ячейку пустое значение в виде двойных кавычек.
Функция ЕСЛИ в Excel: описание, принцип работы
— Диапазон условия 1 — Диапазон ячеек, которые проверяются на соответствие определенному условию.
— Условие 1 — Условие, которое определяет какие ячейки надо учитывать при подсчете.
Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.
Excel. Примеры использования функции СМЕЩ (OFFSET)
Впервые я применил функцию СМЕЩ когда начал использовать динамические диапазоны. Поясню. Если вы строите графики или сводные таблицы на основе данных, которые периодически обновляются, использование динамических диапазонов позволяет до минимума сократить ручной труд, связанный с обновлением таблиц и графиков (см., например, Автоматическое обновление сводной таблицы).
Формально функция СМЕЩ (OFFSET) применяется для создания ссылки на прямоугольный диапазон, которая представляет собой заданное количество строк и столбцов, отстоящих от ячейки или диапазона ячеек (см. Help MS Excel). Неплохую статью для первого знакомства с функцией СМЕЩ написал Ренат Лотфуллин (рекомендую!)
Рис. 1. Пример использования функции СМЕЩ; диапазон суммирования, возвращаемый функцией СМЕЩ подсвечен
Функция СМЕЩ очень полезна, поскольку ее можно копировать, как и любую другую функцию Excel, и заданное число строк и столбцов, на которое вы удаляетесь от ссылки, подсчитывается посредством других функций Excel. [1]
СМЕЩ(ссылка;смещение по строкам;смещение по столбцам,[высота],[ширина])
- Ссылка (reference) — это ячейка или диапазон, от которого начинается смещение. Если указывается диапазон ячеек, они должны быть смежными.
- Смещение по строкам (rows moved) — количество строк, отделяющее начало смещаемого диапазона от ссылки (верхняя левая ячейка в смещенном диапазоне). Если указано отрицательное число, вы перемещаетесь вверх от ссылки; если положительное — вниз. Например, если ссылка равна С5, а смещение по строкам равно –1, вы перемещаетесь к строке 4 в ячейку С4. Если смещение по строкам равно +1, вы перемещаетесь к строке 6. Если смещение по строкам равно 0, вы остаетесь на строке 5.
- Смещение по столбцам (columns moved) — количество столбцов, отделяющее начало смещаемого диапазона от ссылки (верхняя левая ячейка смещенного диапазона). Если указано отрицательное число, вы перемещаетесь влево от ссылки; если положительное — вправо. Например, если ссылка равна С5, а смещение по столбцам равно –1, вы перемещаетесь к столбцу В. Если смещение по столбцам равно +1, вы перемещаетесь к столбцу D. Если смещение по столбцам равно 0, вы остаетесь в столбце С.
- Высота (height) — необязательный аргумент, который задает число строк в смещенном диапазоне.
- Ширина (width) — также необязательный аргумент, который задает число столбцов в смещенном диапазоне. Если высота или ширина опущена, функция СМЕЩ создает диапазон, для которого значения высоты и ширины будут равны высоте и ширине первого параметра функции – ссылки. Если ссылка – это одна ячейка, то функции СМЕЩ создаст диапазон из одной ячейки.
Рис. 2. Выборка по правому столбцу с помощью комбинации функций ПОИСКПОЗ и СМЕЩ
4. Можно ли создать формулу, которая всегда будет выбирать данные, касающиеся определенной страны, если в исходных данных эта страна может располагаться в разных строках?
Рис. 3. Функцию СМЕЩ можно использовать в вычислениях при работе с данными, положение которых не фиксировано в электронной таблице
5. Каждое лекарство создается моей компанией в три этапа. У меня есть перечень затрат по месяцам для каждого лекарства, и я также знаю, сколько месяцев длится тот или иной этап разработки каждого лекарства. Можно ли создать формулы, вычисляющие для каждого лекарства полные затраты, необходимые на каждом этапе разработки?
Рис. 4 содержит данные о ежемесячных издержках, связанных с разработкой пяти лекарств. Каждое лекарство разрабатывается в три этапа. Для каждого лекарства указано количество месяцев, необходимое на завершение каждого этапа.
Рис. 4. Использование функции СМЕЩ для вычисления затрат на этапы 1–3 при разработке лекарств
6. Я управляю небольшим магазином видеопродукции. Мой бухгалтер занес в электронную таблицу название каждого фильма и количество его копий на складе. К сожалению, эти данные указаны в одной ячейке. Как извлечь количество копий каждого фильма в отдельную ячейку?
Рис. 5 содержит перечень фильмов на складе, а также количество копий каждого фильма на складе.
Один из способов решить данную проблему — выбрать в меню Данные команду Текст по столбцам и поместить каждое слово названия и число копий в отдельные столбцы. Затем можно использовать функцию СЧЁТЗ, чтобы определить для каждого фильма общее количество слов в названии, считая число копий за одно слово. После этого можно с помощью функции СМЕЩ отобрать число копий.
Рис. 6. Мастер распределения текста по столбцам, шаг 1
Рис. 7. Мастер распределения текста по столбцам, шаги 2 и 3
7. Ежемесячно я подаю своему шефу данные о продажах единиц продукции в графической форме. Каждый месяц мне требуются новые данные о количестве проданного товара. Мне хотелось бы, чтобы график автоматически обновлялся. Есть ли простой способ реализации этого?
Рис. 9 содержит данные об объемах продаж товара нашей компании в единицах. Как вы видите, график продаж — это точечная диаграмма со значениями, соединенными сглаживающими линиями. Начиная со строки 19, мы будем загружать новые данные о продажах. Есть ли простой способ гарантировать, что график будет автоматически отражать новые данные?
Рис. 9. Функция СМЕЩ позволяет автоматически обновлять данный график
Рис. 10. Создание динамического диапазона Количество_проданных_единиц
Затем аналогичным образом создайте динамический диапазон Месяцы для месяцев, введенных в столбце В. Для этого введите в поле Формула =СМЕЩ(‘Рис. 9’!$B$3;0;0;СЧЁТ(‘Рис. 9’!$B:$B);1).
Переходим к нашему графику и правой кнопкой мыши щелкаем любую точку на нем. В контекстном меню щелкаем Выбрать данные. В окне Выбор источника данных, щелкаем на ряд Количество проданных единиц и на кнопку Изменить (рис. 11).
В открывшемся окне Изменение ряда заменяем диапазоны значений Х и Y на именованные динамические диапазоны (рис. 12). Слева – до изменения, справа – после.
Рис. 12. Вместо ссылок на диапазоны в качестве источника данных выбираем имена диапазонов
Введите какие-нибудь новые данные, и вы увидите, что они появятся на графике.
1. В Excel-файле на листе Задание_1 хранятся данные о количестве проданных единиц для 11 товаров за 1999—2003 гг. Напишите формулу, используя функции ПОИСКПОЗ и СМЕЩ, чтобы отобрать объемы продаж конкретного товара за определенный год. Можете ли вы решить эту задачу, не используя функции ПОИСКПОЗ и СМЕЩ?
3. Допустим, вы постоянно вводите ежемесячные данные о продажах в столбец С:
Продажи первого месяца введены в ячейку С2. Предполагается, что при вводите данные последовательно в каждую следующую ячейку столбца С. Таким образом, если данные о продаже последнего месяца находятся в ячейке С8, данные о продаже следующего месяца будут введены в ячейку С9 и так далее. Напишите формулу, которая всегда будет возвращать данные о продажах за последний месяц.
2. =СМЕЩ($C10;-ЛЕВСИМВ(D$3);0). Функция ЛЕВСИМВ возвращает число месяцев, на которые нужно сместить значение в столбце С. А знак «–» говорит о том, что нужно смещаться вверх.
3. =СМЕЩ(C1;СЧЁТ(C:C);0). Функция СЧЁТ() возвращает количество ячеек в столбце С, содержащих числа. Функция СМЕЩ смещается по столбцу С от ячейки 1 на число ячеек с числами; в нашем случае на 7, попадая на ячейку С8, смещения по столбцам нет, выбирается одна ячейка, так как 4-й и 5-й аргументы функции СМЕЩ опущены (по умолчанию они равны единице).
Формулы в Excel: сложение, подсчет количества строк, поиск и подстановка значений из одной таблицы в другую
Помните, что в описании синтаксиса ИНДЕКС первый параметр назывался диапазон(ы). Пришло время поговорить вот про эту букву » ы «. Выходит диапазонов может быть несколько? Да, их может быть несколько, когда они перечислены через уже упоминаемый выше оператор объединения, — «;» и взяты в скобки. Вот так:
