Быстрый анализ в Excel
Предыдущий способ действительно хорош, потому что позволяет составлять реальные прогнозы, основываясь на статистических показателях. Но этот метод позволяет фактически проводить полноценную бизнес-аналитику. Очень классно, что эта возможность создана максимально эргономичной, поскольку для достижения желаемого результата необходимо совершить буквально несколько действий. Никаких ручных подсчетов, записи каких-либо формул. Достаточно просто выбрать диапазон, который будет анализироваться и задать конечную цель.
Как работать
Итак, чтобы работать, нам надо надо открыть файл, в котором содержится тот набор данных, который надо анализировать и выделить соответствующий диапазон. После того, как мы его выделим, у нас автоматически появится кнопка, дающая возможность составить итоги или же выполнить набор других действий. Называется она быстрым анализом. Также мы можем определить суммы, которые автоматически будут проставлены внизу. Более наглядно посмотреть, как это работает, можете на этой анимации.
Функция быстрого анализа позволяет также по-разному форматировать получившиеся данные. А определить, какие значения больше или меньше, можно непосредственно в ячейках гистограммы, которая появляется после того, как мы настроим этот инструмент.
Также пользователь может поставить самые разные маркеры, которые обозначают большие и меньшие значения относительно тех, которые есть в выборке. Так, зеленым цветом будут показываться самые большие значения, а красным – наиболее маленькие.
Очень хочется верить, что эти приемы позволят вам значительно повысить эффективность вашей работы с электронными таблицами и максимально быстро добиться всего, что вы желаете. Как видим, эта программа для работы с электронными таблицами дает очень широкие возможности даже в стандартном функционале. А что уже говорить про дополнения, которых очень много на просторах интернета
Важно только обратить внимание, что все аддоны должны быть тщательно проверены на вирусы, потому что модули, написанные другими людьми, могут содержать вредоносный код. Если же надстройки разработаны компанией Майкрософт, то ее можно использовать смело
Пакет анализа от Майкрософт – очень функциональная надстройка, которая делает пользователя настоящим профессионалом. Она позволяет выполнить почти любую обработку количественных данных, но она довольно сложная для начинающего пользователя. На официальном сайте справки Майкрософт есть детальная инструкция по тому, как использовать разные виды анализа с помощью этого пакета.
Настройки Таблицы
В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.
С помощью галочек в группе Параметры стилей таблиц
можно внести следующие изменения.
— Удалить или добавить строку заголовков
— Добавить или удалить строку с итогами
— Сделать формат строк чередующимися
— Выделить жирным первый столбец
— Выделить жирным последний столбец
— Сделать чередующуюся заливку строк
— Убрать автофильтр, установленный по умолчанию
В видеоуроке ниже показано, как это работает в действии.
В группе Стили таблиц можно выбрать другой формат. По умолчанию он такой как на картинках выше, но это легко изменить, если надо.
В группе Инструменты можно создать сводную таблицу, удалить дубликаты, а также преобразовать в обычный диапазон.
Однако самое интересное – это создание срезов.
Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,
и срез готов. В нем показаны все уникальные значения выбранного столбца.
Для фильтрации Таблицы следует выбрать интересующую категорию.
Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.
Попробуйте сами, как здорово фильтровать срезами (кликается мышью).
Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.
Объединяем несколько условий.
Для описания условия в функции ЕСЛИ Excel позволяет использовать более сложные конструкции. Также можно использовать несколько условий. В этом случае мы также воспользуемся тем, что функции могут быть «вложены» друг в друга.
Чтобы объединить несколько условий в одно, мы используем логические функции ИЛИ и И. Рассмотрим простые примеры.
Пример 1
Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.
= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)
Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.
Пример 2.
Давайте использовать несколько более сложных условий в функции ЕСЛИ.
Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.
= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)
Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:
- Регион — Запад или Юг
- Число больше 100.
Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)
Во-вторых, здесь все очень просто: E2> 100
В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.
В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.
Пример 3.
Конечно, эти несколько условий могут быть более сложными. Ведь логические функции можно «вкладывать» друг в друга.
Например, помимо вышеуказанного условия, скидка предоставляется только на темный шоколад.
Все наши ранее написанные условия, в свою очередь, становятся первым аргументом в новой функции И:
- Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
- В названии шоколада присутствует слово «черный».
В результате мы получаем формулу ЕСЛИ с несколькими условиями:
= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)
Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.
= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)
В результате количество вложенных условий в Excel может быть очень большим
Важно лишь строго соблюдать логическую последовательность их выполнения
Схема данных
Связи между таблицами в СУБД «Майкрософт Аксес» реализуются при помощи вкладки «Схема данных». На появившуюся панель добавляются все необходимые сущности (в нашем случае все три таблицы). Создание связи многие-ко-многим будет происходить при помощи двух отношений один-к-многих между главными («Студенты» и «Преподаватели») и промежуточной таблицы. Для этого необходимо соединить между собой соответствующие первичные ключи.
На иллюстрации выше показано, как выглядит вкладка «Схема данных» (Relathionships). Количество добавляемых на панель таблиц неограниченно. Расположение полностью регулируется пользователем.
Проектирование баз данных на SQL — задача сложнее, чем на «Аксес». Если майкрософтовский продукт полностью адаптирован под офисную среду, имеет огромный и, с каждым выпуском и обновлением все расширяемый, функционал, но в то же время и удобный для простого пользователя интерфейс, то SQL — это отдельный непроцедурный язык программирования, при помощи которого на разных платформах можно работать с базами данных. Известное ПО для данной задачи: Oracle MySQL и DB2 (популярное, но не единственное в своем роде). Несмотря на то, что у каждого из них есть свои тонкости и нюансы, язык SQL их «объединяет». Научившись работать хотя бы с одним из них, разобраться с другим будет гораздо проще.
Создание, заполнение и непосредственно действия над уже имеющейся БД в SQL нужно через специальные коды или скрипты. Однако те, кто уже добрался до раздела «Связь многие-ко-многим», пример которой на данном языке программирования будет предоставлен ниже, должны знать хотя бы основные команды и принципы использования языка SQL.
Студенческий т-тест в Excel
T-критерий является одним из самых основных статистических тестов, и его легко вычислить в Excel с помощью Toolpak. Нажмите на Анализ данных и прокрутите вниз, пока не увидите параметры t-теста.
У вас есть три варианта:
- t-тест: два парных образца для средних следует использовать, когда ваши измерения или наблюдения были спарены. Используйте это, когда вы делали два измерения одних и тех же предметов, например, измеряли артериальное давление до и после вмешательства.
- t-тест: две выборки, предполагающие равные отклонения следует использовать, когда ваши измерения независимы (что обычно означает, что они были сделаны на двух разных предметных группах). Мы обсудим часть «равных отклонений» через минуту.
- t-тест: две выборки, предполагающие неравномерное отклонение также для независимых измерений, но используется, когда ваши отклонения неравны.
Чтобы проверить, равны ли отклонения двух ваших выборок, вам нужно выполнить F-тест. найти F-тест с двумя образцами для отклонений в списке инструментов для анализа выберите его и нажмите Хорошо.
Введите два набора данных в поля ввода диапазона. Оставьте альфа-значение на уровне 0,05, если у вас нет оснований для его изменения — если вы не знаете, что это значит, просто оставьте. Наконец, нажмите Хорошо.
Excel выдаст вам результаты на новом листе (если вы не выбрали Диапазон выхода и ячейка в вашем текущем листе):
Вы смотрите на P-значение здесь. Если это меньше 0,05, у вас есть неравные отклонения. Таким образом, чтобы запустить t-тест, вы должны использовать опцию неравных отклонений.
Чтобы запустить t-тест, выберите соответствующий тест в окне Инструменты анализа и выберите оба набора данных таким же образом, как вы делали для F-теста. Оставьте значение альфа на 0,05 и нажмите Хорошо.
Результаты включают в себя все, что вам нужно сообщить для t-теста: средние значения, степени свободы (df), t-статистику и P-значения для одно- и двусторонних тестов. Если значение P составляет менее 0,05, два образца значительно различаются.
Если вы не уверены, следует ли использовать одно- или двусторонний критерий Стьюдента, ознакомьтесь с этим объяснением в UCLA.
Сортировка и фильтрация
Скорее всего вы уже обратили внимание на то, что в сводной таблице есть две ячейки с кнопками. По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных
Эти инструменты относятся к заголовкам строк и, соответственно, столбцов
По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных. Эти инструменты относятся к заголовкам строк и, соответственно, столбцов.
Если нужно сформировать отчет только по какой-то одной товарной категории (например, «Зелень»), то с помощью фильтра отключаем все ненужные и получаем результат:
То же самое касается и заказчиков. То есть мы можем сократить отчет только до нужной категории товаров, заказанных определенной торговой сетью.
Если кроме категории нужно отфильтровать данные еще и по конкретным товарам, то в меню в выпадающем списке указываем соответствующее поле, а затем делаем фильтрацию по нему.
При применении сортировки или фильтрации значок на кнопке изменяется. По нему можно однозначно определить, что данные в столбце или строке отфильтрованы или отсортированы.
Чтобы удалить фильтры достаточно выбрать соответствующий пункт в меню, однако в случае с вложенными полями удаление фильтра касается только выбранного в выпадающем списке. То есть если фильтрация была произведена по нескольким полям, то для ее удаления нужно будет сначала переключиться на соответствующее поле.
Кроме стандартных возможностей фильтрации мы можем настроить фильтр по произвольному полю. Для этого есть отдельная область, которая так и называется Фильтры.
Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?
Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.
Мы можем выбрать отдельный город, чтобы получить информацию только по нему.
Что же касается сортировки, то в выпадающем меню есть стандартные инструменты, позволяющие отсортировать заголовки строк или столбцов в алфавитном порядке.
Однако намного удобнее пользоваться контекстным меню. Например одной из первых задач у нас было определить, в каком из городов за прошедшее время выручка была максимальной. Мы получили результат в виде данных по всем городам, но чтобы быстро определить нужное значение необходимо отсортировать значения по возрастанию или убыванию. Вызываем контекстное меню на любой ячейке столбца и выбираем нужный вариант.
Аналогично можно отсортировать данные по любому полю или итогам. Просто вызываем контекстное меню на соответствующей ячейке и выбираем нужное направление сортировки.
Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.
Консолидация данных в Excel
Консолидация данных – опция в Excel, которая позволяет объединять данные из разных таблиц в одну, а также объединять листы, расположенные в одном или разных файлах, в один. Чем же так полезна консолидация для интернет-маркетолога?
Разберем два наглядных примера.
Пример №1
У нас есть статистика по ключевым словам из контекстной рекламы с привязкой к основным показателям – кликам, расходам, транзакциям и доходу.
Выгрузив данные в Excel, мы увидим, что в таблице есть строки, которые «по сути» являются дублями.
Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).
То есть, вместо двух строчек доставка цветов москва и +доставка +цветов +москва мы должны получить одну с объединенными данными. Ведь это один ключ и нам нужно оценить его эффективность.
Аналитика не может автоматически просуммировать такие ключи. Не может и Excel, если только не выбирать поочередно строки и смотреть итоговые значения на панели, а потом в соседней таблице вручную сводить их.
Это очень долго, особенно когда у вас много данных. С этой задачей легко справляется Консолидация данных. За 1 минуту и с помощью нескольких щелчков мыши можно автоматически сделать то, что мы привыкли делать вручную.
Но перед этим нужно не забыть воспользоваться функцией Заменить, убрав все + из таблицы.
— переходим на соседний лист (так удобнее);
— выделяем ячейку, в которую хотим вставить данные;
— переходим в Данные — Консолидация
В открывшемся окне нас интересуют следующие настройки:
- Функция – Сумма (поскольку хотим суммировать данные);
- Ссылка – выбираем весь диапазон данных на соседнем листе;
- Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.
Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:
Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:
Видео консолидации примера №1:
Пример №2
Вы каждый месяц для своего клиента готовите отчет по рекламе. Наступает момент, когда нужно свести данные за предыдущие периоды. Например, ежемесячные, чтобы получить годовой отчет. Или 6 месяцев, чтобы построить суммарный отчет за полгода. В общем, любой период, за который вам нужна консолидированная статистика.
Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.
Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.
В открывшемся окне нас интересуют следующие настройки:
- Функция – Сумма (поскольку хотим суммировать данные);
- Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
- Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.
Галочка Создавать связи с исходными данными позволит производить пересчет консолидированного отчета автоматически при изменении данных в исходных файлах (в нашем примере – это листы сентябрь, октябрь, ноябрь). Нажимаем ОК.
Если все сделали правильно, то на текущем листе получите результат в виде объединенных (консолидированных) данных с трех таблиц в одной:
Слева появятся новые значки:
- 1 – свернуть все связи с исходными данными;
- 2 – развернуть все связи с исходными данными.
При нажатии на + раскроется соответствующая строка, в которой будут отображены данные по каждому из исходных листов.
Видео консолидации примера №2:
Таким образом, консолидация данных в Excel позволяет всего в несколько кликов объединить данных из разных файлов, листов и таблиц в единый объект. Еще один шаг на пути к автоматизации и упрощению работы с отчетностью!
Понравился эксперимент с видео? Поставь 5.0 статье ->
Сводные таблицы Excel | Learnit
Кому следует посещать
Этот курс предназначен для пользователей, знакомых со стандартными функциями Excel, но нуждающихся в более эффективных и мощных способах управления своими данными. Другие, возможно, унаследовали сводные таблицы и хотят понять и изменить их.
От $0
Два двухчасовых модуля (с 9:00 до 11:30 по тихоокеанскому времени)
Сводные таблицы — это, пожалуй, самая мощная функция Excel , которая, скорее всего, произведет впечатление на вашего начальника. На первый взгляд, сводные таблицы кажутся довольно загадочными: при быстром перетаскивании ваши данные каким-то образом реорганизуются в интересные группы и полезные итоги. Существует немало организаций, чьи важные бизнес-решения основаны на тщательно разработанных сводных таблицах. К сожалению, из-за того, что они так много делают за кулисами, трудно понять, как на самом деле работают сводные таблицы, и страшно пытаться модифицировать существующие. Вот тут-то и появляются наши инструкторы: они больше всего любят демистифицировать хитрые функции, такие как сводные таблицы. После этого занятия вы будете тем, кто будет создавать сводные таблицы для сбора информации о данных вашей компании. Вот чему вы научитесь:
- Очистка . Данные в вашей электронной таблице должны соответствовать определенным критериям, прежде чем их можно будет превратить в сводную таблицу. Мы покажем вам, как очищать данные, назначать ячейки, которые вы хотите превратить в сводную таблицу, размещать их именно там, где вы хотите, и красиво оформлять их в цвете.
- Манипуляции . Преимущество сводной таблицы заключается в том, что вы можете полностью изменить представление данных всего несколькими щелчками мыши. Например, что полезнее: заказы по городам, выручка по клиентам или заказы по продавцам? Возможность легко переключать эти параметры — в пределах одного рабочего листа — вот что делает сводные таблицы такими полезными.
- Агрегатные функции . Просмотр различных представлений данных полезен только в том случае, если вы можете обобщить (агрегировать) информацию об этих данных: каковы были итоги, средние значения, медианы и подсчеты для каждого отдельного представления данных? Мы покажем вам, как применять эти функции и фильтровать результаты, чтобы найти истинные идеи.
- Сводные диаграммы . Как и в случае с обычными таблицами данных, полезно создавать диаграммы, иллюстрирующие сводные представления данных. Мы покажем вам, как представить данные из сводных таблиц в цветных визуализациях (если вы не предпочитаете черно-белое изображение).
- Большие данные и «мощные» функции . В Excel есть ограничение только в один миллион строк, и если вы когда-либо пытались использовать их все, ваш компьютер, вероятно, замедлился. Войдите в семейство «мощных» функций: Power Query, Power Pivot, Power View, Power Map и Power BI. Эти функции позволяют импортировать сотни миллионов строк данных — даже из нескольких баз данных одновременно — и использовать их в электронной таблице. Мы покажем вам, как получить эти так называемые «большие данные» с помощью Power Query, а затем применить вычисления с помощью Power Pivot. В конечном итоге вы сможете превратить их в сводные таблицы, сводные диаграммы и другие средства визуализации данных.
Идентификатор курса
Excelpbuip
Предстоящие доступные общественные классы
Нет предстоящих общественных занятий
17 июня и 18 июня 2021
9:00:00:30
июня и 17 июня 2021
17 июня и 18 июня 2021 г.
9:00 утра-11:30 утра
17 июня и 18 июня 2021
9:00 утра 11:30
17 июня 2021
9:00–11:30
17 июня 2021 г.
9:00 утра 11:30 утра
17 июня, 2021
9:00 утра 11:30 утра
17 июня 2021
9:00:00:30:30
17 июня 2021
9:00. 11:30
добавить в корзину
Категории
Социальные навыки
Бизнес-приложения
Техническое обучение
Модуль 1: Создание сводных таблиц и управление ими
Результаты обучения:
1. Организация и очистка данных в электронных таблицах для анализа сводной таблицы
2. Управление и обновление исходных данных сводной таблицы для отображения новых значений
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Как использовать функцию ЕСЛИ с И, ИЛИ и НЕ в Excel?
В Excel функция ЕСЛИ весьма полезна для проверки того, соответствует ли ячейка условию, и возвращает разные значения на основе оцененных результатов (Истина и Ложь). А комбинируя функцию ЕСЛИ и функции И, ИЛИ, НЕ, он будет изменять условия проверки ячеек. Здесь я расскажу, как использовать функции ЕСЛИ и И, функции ЕСЛИ и ИЛИ, а также объединить несколько функций ЕСЛИ в Excel.
Предположим, у нас есть таблица продаж, как показано на следующем снимке экрана, и теперь мы хотим оценить значения продаж и сгруппировать их как «Необычный,Низкий,средняяиHigh».
А теперь скопируйте заголовки строк и столбцов в пустой диапазон (диапазон A16: E28 в нашем примере), как показано на следующем снимке экрана.
Как использовать функцию ЕСЛИ в Excel
Основная форма функции ЕСЛИ в Excel показана как: = ЕСЛИ (логический_тест, значение_если истина, значение_если_ ложь)
В нашем случае мы хотим проверить, являются ли объемы продаж Низкими или нет. Если значение в ячейке B2 равно или меньше 80, вернуть текст «Низкий», если значение в ячейке B2 больше 80, вернуть пустое значение.
Введите формулу = ЕСЛИ (B2 <= 80; «Низкий»; «») в ячейку B17, а затем перетащите маркер заполнения в диапазон B17: E28. См. Снимок экрана ниже:
Легко объединяйте несколько листов / книг / файлов CSV в один рабочий лист / книгу
Объединение десятков листов из разных книг в один лист может оказаться утомительным. Но с Kutools for ExcelАвтора Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов!
Полнофункциональная бесплатная пробная версия 30-день!
Как использовать функцию If и функцию И в Excel
Если вы хотите проверить, соответствует ли ячейка нескольким условиям одновременно, вы можете объединить функцию ЕСЛИ и функцию И в Excel. Основная форма функции И в Excel: = И (логическая 1, логическая 2, логическая 3,… и т. Д.)
Теперь в нашем случае мы хотим проверить, что объем продаж одновременно больше 80 и меньше 160, если да, то возвращается текст «Середина», а если нет, то пусто.
Введите формулу = ЕСЛИ (И (B2> 80, B2 <160), «Средний», «») в ячейку B17, а затем перетащите маркер заполнения в диапазон B17: E28. См. Снимок экрана ниже:
Как использовать функцию ЕСЛИ и функцию ИЛИ в Excel
Если вы хотите проверить, соответствует ли ячейка одному из нескольких условий, вы можете объединить функцию ЕСЛИ и функцию ИЛИ в Excel.
Основная форма функции ИЛИ в Excel: = ИЛИ (Логическая 1, Логическая 2, Логическая 3,… и т. Д.)
В нашем примере мы хотим узнать необычные объемы продаж, которые больше 180 или меньше 20. Если объемы продаж неординарные, верните текст «Необычные», если нет возврата пустым.
Введите формулу = ЕСЛИ (ИЛИ (B2> 180; B2 <20); «Необычный»; «») в ячейку B17, а затем перетащите маркер заполнения в диапазон B17: E28. См. Снимок экрана ниже:
Как объединить несколько функций ЕСЛИ в одну формулу в Excel
В нашем примере, если вы хотите оценить все объемы продаж с помощью одной формулы, вы можете применить сложную функцию ЕСЛИ.
Если объем продаж равен или меньше 20, вернуть текст «Необычный»; если объем продаж равен или меньше 80, вернуть текст «Низкий»; если равно или меньше 160, вернуть текст «Средний», а если равно или меньше 200, вернуть текст «Высокий».
Введите формулу = ЕСЛИ (B2 <= 20, «Необычный», ЕСЛИ (B2 <= 80, «Низкий», ЕСЛИ (B2 <= 160, «Средний», ЕСЛИ (B2 <= 200, «Высокий», «»))) ) в ячейку B17, а затем перетащите маркер заполнения в диапазон B17: E28. См. Снимок экрана ниже:
| Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Бесплатная пробная версия |
Как сделать выборку в Excel по условию
Чтобы определить соответствующие значение первому наименьшему числу нужна выборка из таблицы по условию. Допустим мы хотим узнать первый самый дешевый товар на рынке из данного прайса:
Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:
В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.
Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.
Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:
Выборка соответственного значения с первым наименьшим числом:
С такой формулой нам удалось выбрать минимальное значение относительно чисел. Далее разберем принцип действия формулы и пошагово проанализируем весь порядок всех вычислений.