Функция ПРОСМОТР

Салют!
Сегодня добьём тему простых функций для поиска значений по одному условию.
Основа в виде ВПР и ГПР уже пройдена. Осталось дополнить их компанию функцией ПРОСМОТР.

Материал: 4.4 Функция ПРОСМОТР
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы
#ПРОСМОТР

​​Всем привет!
Сегодня легкий тест по функции ПРОСМОТР.

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

#тест

Привет, привет!
Сразу 3 темы в этом посте.

1.Ответ
Правильный ответ на тест↑ - Фрукты; Стоимость.
Сначала указываем диапазон с условиями, потом указываем диапазон вытягиваемых значений.

2.Материал о функции СУММЕСЛИ
ВПРить научились и это - огонь 🔥. Теперь посложнее. Мы продаем автомобили.
Год прошел, и мы хотим понять сколько автомобилей ниже 15 000 долларов мы продали. Плюс узнать все продажи Теслы за год. А кроме знаменитой машины Илона Маска мы и жигулями приторговывали.
Как быстро просуммировать по условиям: “<15000 USD” и “Тесла”?

Встречайте, функция СУММЕСЛИ. И материал о ней.

Материал: 4.5 Функция СУММЕСЛИ
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы
#СУММЕСЛИ

3.Анонс
Вечером будет информация для тех, кто хочет повысить свое мастерство в Excel.
Прошу вас поучаствовать в опросе↓. Stay tuned.

Усредняй по условиям

Если есть сумма по условиям, то найдется и среднее значение.
Сегодня материал по функции СРЗНАЧЕСЛИ.

И спасибо вам за обратную связь по курсу. Количество откликов говорит о том, что тема интересна. Я продолжаю работу над материалом.
Как говорится: будут песни, будут и пляски. Завтра - пост по теме курса.

Материал: 4.6 Функция СРЗНАЧЕСЛИ
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы
#СРЗНАЧЕСЛИ

Яблочный или машина?

На днях я перешел с iOS на Android.
Ну что сказать, в нирвану я не попал, но и третья рука у меня не выросла. Обе системы и радуют, и косячат.

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

Мораль сей басни: смотрите на ситуацию с разных точек зрения)
А чем пользуетесь вы?

Буэнос ночес!

Думал я, делать или не делать полноценный материал по СЧЁТЕСЛИ. Но всё-таки сделал. Всё работает по аналогии с СУММЕСЛИ и СРЗНАЧЕСЛИ.
Только синтаксис проще.

Материал: 4.7 Функция СЧЁТЕСЛИ
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень4
#формулы
#СЧЁТЕСЛИ

Добрый вечер!

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

▫️ СУММЕСЛИМН
▫️ СРЗНАЧЕСЛИМН
▫️ СЧЁТЕСЛИМН

Синтаксис у них одинаковый, поэтому решил сделать 3-в-1.
Область применения функций широка: от банального составления поисковых запросов и до автоматизации работы между таблицами.

Функции просты в освоении и предлагают широкий функционал, но к сожалению, работают только с одномерными массивами (строка, столбец).
Об этом нужно помнить при выборе инструментов работы.

Более подробно в материале.

Материал: 4.8 Сумма, среднее и количество по множеству условий
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы

Мой подписчик недавно подкинул отличную идею: “Неплохо бы иметь краткую энциклопедию по функциям с синтаксисом. То есть с сухой информацией, если вдруг что забыл”

Я за любую инициативу)
Поэтому сделал Краткий каталог функций. Здесь информация высушена до синтаксиса функций. И только.

Забыли для чего нужен диапазон условий в СУММЕСЛИ? Зашли в краткий каталог и посмотрели - в этом его основная функция.

Все новые предложения и вопросы по основному материалу можете написать сюда: @excelstudybot.

Краткий каталог функций

Запасная ссылка

Ссылка на каталог добавлена в навигацию канала.
#каталогфункций

Алоха!

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

Ждите в скором времени.
Сегодня же у нас материал по трем логическим функциям:

▫️ Функция И
▫️ Функция ИЛИ
▫️ Функция НЕ

Этот материал тоже 3-в-1, как и прошлый.
Функции составляют костяк логических операторов. Их основная область работы - проверка условий и возвращение значений “истина” или “ложь”.
Читайте об этом в материале↓

Их самостоятельное применение не так эффективно, как совместно с другими функциями. Поэтому следующий материал будет о связке ЕСЛИ + (И / ИЛИ / НЕ).

Материал: 4.9 Логические функции И / ИЛИ / НЕ
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень4
#формулы

Привет!

В прошлый раз я обещал материал о логической связке. Сегодня о ней и поговорим.

Тема дня: логическая связка ЕСЛИ + (И / ИЛИ / НЕ).
Внутри вас ждут лаконичные видеопримеры и немного Газманова.

Материал: 4.10 Связка ЕСЛИ + И / ИЛИ / НЕ
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень4
#формулы

Новая рубрика - Кейсы

Всем отличного понедельника!

Материалов на канале накопилось достаточно и пора бы уже использовать эти знания.

Но простые примеры “посчитай количество пони в загоне” или “ВПР по продажам ракет SpaceX" - уже не фонтан.

Поэтому сегодня я запускаю новую рубрику на канале - Кейсы.

▫️ Каждый кейс - это интересное и нетривиальное применение, уже знакомых нам, функций. И чаще всего с нетривиальными задачами.

▫️ Кейс - это небольшой пост на канале. В сопровождении с фото/видео. Без статей в Телеграфе.

▫️ Кейсы будут выходить в промежутках между основными материалами.

Первый кейс уже здесь↓

#кейсы

​​Кейс №1. Поиск последней заполненной ячейки

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

В таких случаях может помочь ВПР/ГПР.
Вот это поворот!

В Excel есть хак.
Заключается он в том, что если в аргументе “искомое значение” ВПР/ГПР написать нереально большое число (9 999 999) и включить интервальный/приблизительный поиск, то функция будет искать последнюю непустую ячейку.

Это фишка работает с числами. Если хотите искать последнюю заполненную текстовую ячейку, то в искомое значение поставьте “ЯЯЯЯЯЯЯ”.

Пробелы для этой формулы не страшны. Но если в столбцах/строках будет одновременно и текст, и числа, то формула отработает некорректно.

Посмотрите пример в видео.
В первой таблице, последние непустые ячейки ищет ГПР, во второй - ВПР.

1. =ГПР(9999999;B2:G6;1)

2. =ВПР(9999999;A12:E17;1)

И не забывайте включать интервальный поиск: либо пишите последний аргумент “1”, либо вообще не пишите последний аргумент (как в примере).

#кейсы
#ВПР
#ГПР

Ну что, соскучились?

Сегодня у нас блиц-материал. Почти как в “Что, где, когда”.
Блиц-материал - это быстро, кратко и доступно.

Одна статья содержит 10 функций: группа функций Е и ЕСЛИОШИБКА.

▫️ Функции Е часто используются в логическом выражении ЕСЛИ. Необходимы для проверки значений на признак текста/числа/ссылки/ошибки.
▫️ ЕСЛИОШИБКА - отличный чистильщик, заменяет ошибки на другое значение.

Must use.

Материал: 4.11 Функции Е и ЕСЛИОШИБКА
Если не открывается, используйте запасную ссылку
Сложность: 3/10
Связь: @excelstudybot

#уровень4
#формулы

Кейс №2. Генератор случайных чисел

Салют всем!
Сегодня кейс о полном рандоме.

▫️Нужно провести конкурс и в случайном порядке рассчитать места?
▫️Нужны случайные числа для математической модели?
▫️Или ваяешь примеры для Excel?)

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

Функция СЛУЧМЕЖДУ генерируют случайные числа в указанных рамках.

= СЛУЧМЕЖДУ (нижняя граница; верхняя граница)

Всё, что вам нужно сделать - это указать нижнюю и верхнюю числовую границу. Максимально просто. Даже синтаксис объяснять не требуется.

Плюс этой функции в том, что повторное нажатие на Enter генерирует новый массив случайных чисел.

Помните, что генерировать случайные числа вручную - отстой. СЛУЧМЕЖДУ отвечает💪🏻

#кейсы

Всем привет!

Сегодня мы оставляем функции и переходим к визуализации данных в Excel.
Don’t worry. К функциям мы еще вернемся. Не хватайтесь за сердце.

Визуализация в Excel - это условное форматирование, диаграммы, именованные списки и прочие визуальные прелести. Всё то, что помогает увидеть главное.

Какой-то особой магии в визуальной части Excel нет.
Ключевой навык в этом деле - видеть и лаконично выделять самые важные показатели. Чувство меры здесь ценится превыше всего.
Не все об этом знают и начинают выделять все подряд: графики по каждому показателю, форматирование всех продаж и т.д.

Когда важная информация грамотно выделена на фоне остальных, то и решения принимаются быстрее. Проверено.

Сделаем же первый шаг в сторону визуализации данных.
Тема дня: Условное форматирование ячеек↓

#уровень5

Условное форматирование - это набор инструментов для автоматического форматирования ячеек по правилам.

Этот набор поможет изменять формат ячеек с содержимым больше 0, меньше 100, а может одновременно по двум условиям >50 и <150. Залить цветом, поменять шрифт и т.д.

Или покрасить в зеленый все значения выше среднего. Как вам такое?
Если не нашли подходящее правило, то можете создать свое правило с помощью формулы.
Штука достаточно гибкая.

Заходите в статью telegraph.

Материал: 5.1 Условное форматирование
Если не открывается, используйте запасную ссылку
Сложность: 4/10
Связь: @excelstudybot

#уровень5
#форматирование
#визуализация

Кейс №3. Конкатенация. Часть 1

Конкатенация - склеивание различных строк в одну. В случае с Excel можно склеить значения из разных ячеек в одну строку.

2 способа, чтобы добиться склеивания значений.

1. Функция = СЦЕПИТЬ (текст 1; текст 2….)
Через точку с запятой можно указывать текст в кавычках, пробелы или адреса ячеек со значениями.
На выходе мы получаем объединенную строку значений.

2. Оператор конкатенации - амперсанд (&). Это тот самый знак, который означает английское “and” (и).

Если записать = В2 & “Тест” & F10, то результатом будет объединенная строка из значения ячейки В2, Тест и значения из F10.

У двух инструментов один и тот же эффект, но есть отличия в порядке применения.
Когда дело касается математики, то СЦЕПИТЬ имеет преимущество перед амперсандом.

Что это значит?

▫️ Если написано = 4 & 8 х 8, то результатом будет 464. Сначала умножение, потом склеивание.
▫️ Если написано = СЦЕПИТЬ (4;8) х 8, то результатом будет 384. Сначала СЦЕПИТЬ, потом умножение.

Завтра будет мощный кейс о том, как дуэт Амперсанд + ПРОСМОТР ищет значения по нескольким критериям.

See you soon!

#кейсы

​​Кейс №4. Конкатенация. Часть 2

Всем добрый вечер!
Склеивание проявляется не только в объединении А и Б. Его можно использовать в создании дополнительных условий для поиска.

Сегодня поговорим о работе функции ПРОСМОТР совместно с амперсандом (&).
Оказывается, ПРОСМОТР может искать сразу по нескольким критериям.

Базовый синтаксис функции выглядит так:

=ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов)

А вот синтаксис с амперсандом:

= ПРОСМОТР (искомое значение1 & искомое значение2; просматриваемый вектор1 & просматриваемый вектор2; вектор результатов)

С помощью амперсанда можно добавить дополнительное искомое значение и столбец, где оно находится.

В этом способе есть 2 особенности.

1. ПРОСМОТР работает в режиме приблизительного поиска. Если не найдет, то может вернуть что-то другое.

2. Перед тем как использовать ПРОСМОТР + Амперсанд, необходимо отсортировать по возрастанию столбцы с искомыми значениями. Делайте это через настраиваемую сортировку.

Посмотреть как работает этот прием вы можете на видео ниже↓

Завтра выйдет следующий материал по визуальной составляющей Excel.
До встречи!

#кейсы

Всем отличной пятницы💃

Вчера не успел выложить пост, исправляюсь)
Лейтмотив на сегодня: “Лучше один раз увидеть, чем сто раз услышать”.
Да, да. Это первая часть материала о диаграммах в Excel.

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

Внутри материала:

▫️ Как создавать диаграммы?
▫️ Ключевые понятия
▫️ Как сделать 2 диаграммы на 1 поле?
▫️ Как форматировать диаграммы?

Материал: 5.2 Диаграммы. Часть 1
Если не открывается, используйте запасную ссылку
Сложность: 5/10
Связь: @excelstudybot

#уровень5
#диаграммы
#визуализация

​​Кейс №5. Фильтрация и сортировка по цвету

Салют!
Фильтрация и сортировка отлично улучшают читаемость таблиц.
В Excel вы можете фильтровать и сортировать данные не только по величине значений, но и по цвету.
Особенно эффективно использовать это совместно с правилами условного форматирования (подробнее тут: https://t.me/exstudy/123 )

Фильтрация

Выделяем шапку таблицы и ставим фильтр. По всей шапке появляются ярлыки раскрываемых списков в виде стрелок. Нажимаем и выбираем пункт “Фильтр по цвету”.

Остается лишь выбрать цвет, по которому хотите отфильтровать данные.

Сортировка по цвету

Выделили фрагмент таблицы для сортировки. Заходим на главной вкладке в пункт “Сортировка и фильтр” - “Настраиваемая сортировка”.
В открытом окне заполняем следующие параметры:

▫️Столбец - выбираем сортируемый столбец.

▫️Сортировка - режим сортировки. Выбираем “цвет ячейки”.

▫️Порядок - выбираем конкретный цвет заливки.

▫️Расположение - Сверху/Снизу. Параметр определяет где будут располагаться цветные значения таблицы: вверху или внизу.

После выбора, цветные ячейки будут расположены вместе.
Если добавить еще один уровень в “Настраиваемой сортировке”, то можно определить порядок сортировки по цветам.

Видео↓

#кейсы