Использование логических функций в excel

Сложение с несколькими критериями в MS EXCEL (Часть 1.Введение)

​ более вероятно, что​ из которых соответствует​,​ соответствующей ячейке на​ задать​ мы забыли учесть​ табличка небольшая, кажется,​ версией команды СУММ,​: Не нужен дополнительный​Предположим, что у нас​Все задачи сложения чисел​. Если второе условие​

​ «меньше или равно​ в отдельном столбце,​ 7, 8 и​ синтаксис:​

1. ​Как посчитать сумму в​ у вас в​ определенной торговой точке.​«плохо»​
2. ​ листе. Как видим,​«Номер индекса»​ одну сотрудницу, которая​ что все можно​

​ которая называется СУММЕСЛИ,​ столбец, решение легко​ есть база данных​с несколькими​

​ истинно, то формула​​ 25».​​ как показано ниже.​ 9 (июль, август​

• ​CHOOSE(index_num,value1,value2,…)​​ Экселе​ списке этого наименования​ Выручка указана отдельно​,​ координаты автоматически отобразились​, для которого нет​​ работает продавцом. Мы​ сосчитать и вручную,​ существенно расширяются возможности​ масштабируется на большее​ по ценам товаров​​критериями можно классифицировать​​ возвращает значение «Хорошо»,​​Функция​Внутри функции​
• ​ и сентябрь) должно​​ВЫБОР(номер_индекса;значение1;значение2;…)​Как видим, функция​ не окажется. В​ за определенную дату​​«удовлетворительно»​ и в поле​​ соответствующего аргумента​​ можем добавить еще​ но при работе​ данной операции.​ количество условий (до​ за разные месяцы:​​ следующим образом:​ а если ложно,​​ЕСЛИ​​SUM​ стоять число 1.​index_num​ВЫБОР​ этом случае нужно​
• ​ построчно. Наша задача​​,​ окна аргументов.​​«Значение»​ одну строчку через​ со списками, в​По названию команды можно​​ 127), быстро считает.​​Нужно найти и вытащить​Условие И​ то «Плохо».​является очень гибкой​(СУММ) в первую​=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)​(номер_индекса) должен быть​​при правильном её​ кликнуть по позиции​ — сделать так,​«хорошо»​

​После этого нам предстоит​​, то оператор будет​ правую кнопку мыши​ которых по несколько​

excel2.ru>

Описание

Функция ВЫБОР Microsoft Excel возвращает значение из списка значений на основе заданной позиции.

Функция ВЫБОР – это встроенная функция Excel, относящаяся к категории Функция поиска/справки. Его можно использовать как функцию рабочего листа (WS) и функцию VBA (VBA) в Excel. В качестве функции рабочего листа функцию ВЫБОР можно ввести как часть формулы в ячейку рабочего листа. Как функцию VBA, вы можете использовать эту функцию в коде макроса, который вводится через редактор Microsoft Visual Basic.

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

Способы фильтрации значений в Excel

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

1. Фильтр по значению

   Один из самых простых способов фильтрации – это использование фильтра по значению. Для этого нужно выделить столбец со значениями, в котором хотите установить фильтр, и выбрать вкладку «Данные» > «Фильтр». Появится набор фильтров, по которым можно отобрать нужные значения и скрыть остальные.

2. Фильтр по формату

   Если вам нужно отобразить только определенные значения, соответствующие определенному формату, вы можете использовать фильтр по формату. Выделите столбец с данными, выберите вкладку «Данные» > «Фильтр» и выберите «Фильтр по формату». Затем установите необходимое условие, например, фильтр по цвету ячеек или значению.

3. Фильтр по условию

   Фильтр по условию позволяет отбирать значения на основе заданных условий. Для этого выделите столбец с данными, выберите вкладку «Данные» > «Фильтр» и выберите «Фильтр по условию». Затем задайте условие, например, «Больше», «Меньше», «Равно» и т.д., и укажите значение, которому должны соответствовать отфильтрованные данные.

4. Расширенный фильтр

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

5. Фильтр по тексту

   Если вам нужно отобразить только значения с определенным текстом или содержащие определенное слово или фразу, вы можете использовать фильтр по тексту. Выделите столбец с данными, выберите вкладку «Данные» > «Фильтр» и выберите «Фильтр по тексту». Затем введите текст или слово, по которому хотите отфильтровать значения.

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

Выпадающий список в Excel с подстановкой данных

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

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

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
5. Сохраняем, установив тип файла «с поддержкой макросов».
6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Пример 4. Порядок столбцов для ВПР

Для функции ВПР искомое значение должно находиться в первом столбце слева от диапазона поиска. В этом примере функция ВЫБОР объединяет столбцы в другом порядке, поэтому формула ВПР может возвращать результат из столбца слева от столбца поиска.

Вот справочная таблица со значениями для поиска — баллы — в столбце D. Мы хотим получить описание из столбца C, используя приблизительное соответствие для балла.

Функция ВЫБОР позволит нам сделать это, не меняя структуру таблицы.

ВПР и ВЫБОР

Под таблицей поиска в ячейку C9 вводится оценка.

Чтобы найти соответствующую оценку, в ячейку C10 вводится следующая формула, объединяющая функцию ВПР и ВЫБОР.

Аргумент index_num для ВЫБОР имеет два числа, заключенных в фигурные скобки —

=ВПР(C9,ВЫБРАТЬ( ,D3:D7,C3:C7),2,ИСТИНА)

Если в качестве оценки введено значение 65, результат будет удовлетворительным.

Как это работает

Функция ВЫБОР создает массив поиска с двумя столбцами для функции ВПР. Для этого в этой формуле в качестве первого аргумента используется массив — — вместо одного числа.

Массив помещает баллы (D3:D7) в первый столбец, а описание (C3:C7) — во второй столбец.

Затем функция ВПР возвращает значение из второго столбца этого массива (C3:C7), используя приблизительное совпадение.

Чтобы увидеть массив, созданный CHOOSE:

1. Выберите функцию ВЫБОР в строке формул.
2. Нажмите клавишу F9, чтобы оценить эту часть формулы. Каждая пара оценок/описаний оценивается.
3. Нажмите клавишу Esc, чтобы выйти из формулы без сохранения изменений.

Представление данных в виде диаграмм

Выполнив задания этой темы, вы научитесь:- создавать различные типы диаграмм;
— форматировать объекты диаграммы;
— изменять размеры диаграммы.

Создание и редактирование диаграмм

Для создания диаграммы необходимо:    выделить диапазон ячеек, содержащий данные для ее построения;
    нажать кнопку Мастер диаграмм на панели инструментов;
    выбрать тип диаграммы из предлагаемого набора;
    задать основные и дополнительные параметры диаграммы.

Редактирование диаграммы производится с помощью команд контекстного меню, позволяющих изменять ее параметры: заголовки, легенду, подписи рядов и данных. Можно добавлять новые данные для построения диаграммы или удалить построенные ранее диаграммы.

Форматирование диаграмм

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

Задание 4.10

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

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

Технология работы

1. Запустите табличный процессор.

2. Откройте документ Расписание.xls.

3. Создайте столбчатую диаграмму распределения времени стоянок по станциям:    выделите диапазон ячеек С5:С10;
    выберите команду меню Вставка ► Диаграмма;
    в появившемся окне Мастера диаграмм выберите тип диаграммы — Гистограмма, вид — объемный;
    щелкните на кнопке Далее.

4. На следующем шаге Мастера диаграмм задайте подписи по оси X:    на вкладке Диапазон данных установите переключатель Ряды в строках;
    перейдите на вкладку Ряд;
    щелкните в поле Подписи по оси X и выделите в таблице диапазон ячеек А5:А10;
    задайте подпись ряда, щелкнув в поле Имя и выделив в таблице ячейку СЗ;
    щелкните на кнопке Далее.

5. На следующем шаге Мастера диаграмм задайте подписи осей X и У:    перейдите на вкладку Заголовки;
    в поле Название диаграммы введите Распределение стоянок;
    в поле Ось X (категорий) введите Станция’,
    в поле Ось Y (значений) введите Время’,
    перейдите на вкладку Легенда;
    сбросьте флажок Добавить легенду;
    щелкните на кнопке Далее.

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

7. Измените шрифт заголовка диаграммы:    щелкните мышью на заголовке диаграммы — вокруг текста появится рамка с маркерами выделения;
    щелкните на заголовке правой кнопкой мыши и выберите в контекстном меню команду Формат заголовка диаграммы;
    перейдите на вкладку Шрифт;
    в появившемся диалоговом окне выберите полужирное начертание шрифта, размер — 16, цвет — синий;
    щелкните на кнопке ОК.

8. Создайте диаграмму распределения времени в пути:    выделите диапазон ячеек Е5:Е11;
    выберите команду меню Вставка ► Диаграмма;
    в появившемся окне Мастера диаграмм выберите тип диаграммы — Круговая, вид — объемный;
    щелкните на кнопке Далее.

9. Задайте подписи по оси X:    на вкладке Диапазон данных установите переключатель Ряды в столбцах;
    перейдите на вкладку Ряд;
    щелкните в поле Подписи по оси X и выделите в таблице диапазон ячеек А5:А11;
    задайте подпись ряда, щелкнув в поле Имя и выделив в таблице ячейку ЕЗ;
    щелкните на кнопке Далее.

10. На следующем шаге мастера задайте подписи данных на диаграмме:    выберите вкладку Подписи данных;
    установите флажок Включить в подписи — значения;
    перейдите на вкладку Легенда;
    установите флажок Добавить легенду, выберите переключатель Размещение — справа;
    щелкните на кнопке Далее.

11. Задайте расположение диаграммы, выбрав переключатель Поместить диаграмму на отдельном листе, и щелкните на кнопке Готово.

12. Сохраните документ и закройте табличный процессор командой Файл ► Выход.

Задания для самостоятельной работы

Задание 4.11.

Представьте данные созданной в предыдущей теме таблицы в виде диаграмм. Используйте несколько типов диаграмм. Задайте им различное оформление.

Задание 4.12.

Создайте таблицу успеваемости нескольких учеников по разным предметам. Отобразите эти данные на нескольких диаграммах. Используйте различные возможности оформления диаграмм.

Шаг 1: Определение условий для поиска данных в Excel

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

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

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

Интеграция функций

Существует очень важный момент при составлении формул. Интеграция функций подразумевает то что функция работает с другой функцией одновременно в одной ячейке. Например перед нами стоит задача, если в N-ой ячейке не будет прописано число 5 то вывести информацию “Число 5 не написано в этой ячейке”, а если прописано вывести информацию на экран “В ячейке число 5”. Строение формулы будет именно таким

Рассмотрим другой вариант. Перед нами будет стоять такая задача если в ячейке стоит число меньше 5 то вывести сообщение на экран “В ячейке прописано число меньше 5”, а если в ячейке ввести число больше 5 вывести сообщение на экран “В данной ячейке число больше 5”. В случаи в ячейке отсутствует какая-либо информация вывести сообщение “Введите число”.

Строение формулы будет следующим образом

Пример 3: общий объем продаж для выбранного магазина

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

На листе есть поле со списком «Управление формой», в котором отображается список из трех магазинов. Поле со списком связано с ячейкой C2, в которой отображается номер выбранного вами элемента — 1, 2 или 3.

Если выбрано Store103, отображается сумма для ячеек E6:E8 — 1774.

Как это работает

Вместо списка элементов функция ВЫБОР имеет список ссылок на диапазоны — C6:C8, D6:D8, E6:E8. Эти диапазоны содержат ежемесячные продажи для каждого из трех магазинов.

При выборе Store103

• ВЫБОР вычисляется первым и возвращает ссылку на 3-й диапазон: E6:E8
• СУММ возвращает общее значение для этого диапазона: 1774.

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

Как сделать выборку в Excel из списка по формуле массива

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

1. На том же листе для будущей выборки создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.
2. Выделяем все пустые ячейки первой колонки новой таблицы Excel. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула для будущей выборки будет выглядеть следующим образом: =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);»»);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1)) Естественно, в каждом конкретном случае адрес ячеек и диапазонов будет свой.
3. Так как это формула массива в файле Excel, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. В результате мы продвинулись на пути создания будущей выборки.
4. Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение: =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);»»);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1)) Жмем сочетание клавиш Ctrl+Shift+Enter. РИС 5
5. Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания: =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);»»);СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1)) Опять набираем сочетание клавиш Ctrl+Shift+Enter.
6. Теперь таблица Excel заполнена данными и выборка почти готова, но внешний вид её не совсем привлекателен, к тому же, значения даты заполнены в ней некорректно. Нужно исправить эти недостатки. Некорректность даты связана с тем, что формат ячеек Excel соответствующего столбца общий, а нам нужно установить формат даты. Выделяем весь столбец, включая ячейки с ошибками, и кликаем по выделению правой кнопкой мыши. В появившемся списке переходим по пункту «Формат ячейки…».
7. В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы»выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».
8. Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!». По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми, выборку было бы проще читать. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная»кликаем по кнопке «Условное форматирование», которая находится в блоке инструментов «Стили». В появившемся списке выбираем пункт «Создать правило…». РИС 7
9. В открывшемся окне выбираем тип правила «Форматировать только ячейки, которые содержат». В первом поле под надписью «Форматировать только ячейки, для которых выполняется следующее условие»выбираем позицию «Ошибки». Далее жмем по кнопке «Формат…».
10. В запустившемся окне форматирования файлов Excel переходим во вкладку «Шрифт»и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK». РИС 6
11. На кнопку с точно таким же названием жмем после возвращения в окно создания условий.

В результате нами получена готовая выборка в программе Excel.

Синтаксис функции ВЫБОР()

ВЫБОР ( номер_индекса ; значение1 ;значение2;…), где

• Номер_индекса — номер выбираемого аргумента-значения. Номер_индекса должен быть числом от 1 до 254. Индекс можно ввести формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254;
• Если номер_индекса равен 1, то функция ВЫБОР() возвращает значение1; если он равен 2, возвращается значение2 и так далее;
• Если номер_индекса меньше 1 или больше, чем номер последнего значения в списке, то функция ВЫБОР возвращает значение ошибки #ЗНАЧ!
• Если номер_индекса представляет собой дробь, то он усекается до меньшего целого;
• сами значения — значение1 ;значение2; . могут быть числами, текстовыми строками и ссылками на диапазоны ячеек. Если в качестве значений введены конкретные значения, то функция возвращает одно из этих значений в зависимости от индекса, а если в качестве значений введены ссылки на ячейки, то функция возвращает соответственно ссылки.

Пример 2: Дата следующего понедельника

Функция ВЫБОР может быть объединена с другими функциями, такими как ДЕНЬ НЕДЕЛИ, для расчета предстоящих дат. Например, если вы проводите собрание сотрудников каждый понедельник, используйте формулу, чтобы найти дату следующего понедельника.

Сегодняшняя дата + ?? days = дата следующего понедельника

Формула добавит определенное количество дней к сегодняшней дате, и функция ВЫБОР выберет это число.

Сколько дней добавить

Чтобы составить список элементов для ВЫБОРА, нам нужно знать сегодняшнее число ДНЯ НЕДЕЛИ.

• Дни нумеруются с воскресенья (1) по субботу (7), если вы используете настройки по умолчанию для ДНЯ НЕДЕЛИ.
• Если сегодня воскресенье, номер ДНЯ НЕДЕЛИ равен 1.
• Если сегодня пятница, число ДНЯ НЕДЕЛИ равно 6.

В этой таблице показаны числа ДНЕЙ НЕДЕЛИ с названиями дней и количеством дней до следующего понедельника.

Числа в столбце «Добавить» будут использоваться в функции ВЫБОР — 1,7,6,5,4,3,2.

• Если сегодня воскресенье, добавьте 1 день, чтобы получить дату следующего понедельника.
• Если сегодня пятница, добавьте 3 дня, чтобы получить дату следующего понедельника.

Формула даты следующего понедельника

Вот формула для расчета даты следующего понедельника, если сегодняшняя дата находится в ячейке C2:

=C2+ВЫБРАТЬ(ДЕНЬ НЕДЕЛИ(C2), 1,7,6,5,4,3,2 )

Функция ВЫБОР в Excel: примеры

Функция ВЫБОР решает задачи по представлению значений из списка в Excel. Например, диапазон А2:А8 содержит номера недели от 1 до 7. Необходимо отобразить день недели прописью, то есть «понедельник», «вторник», «среда», «четверг», «пятница», «суббота», «воскресенье».

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

Теперь рассмотрим можно склонять слова с помощью Excel. Например, слово «рубль»: «0 рублей», «1 рубль», «2 рубля», «3 рубля», «4 рубля», «5 рублей» и т.д.

С помощью функции ВЫБОР можно вернуть ссылку на диапазон. Это позволяет делать вычисления над массивами данных по заданному пользователем критерию. Рассмотрим пример суммирования выручки в заданном пользователем магазине.

Имеются данные по выручке в нескольких торговых точках:

Формула рассчитывает выручку в магазине, заданном пользователем. В ячейке А8 можно изменить номер торговой точки –ВЫБОР вернет для функции СУММ ссылку на другой интервал. Если поставить в ячейке А8 цифру 2, формула подсчитает выручку для второго магазина (результат СУММ для диапазона В2:В5).

С помощью функции ВЫБОР можно задать аргумент для функции СУММ так, чтобы получить результат подсчета 2, 3, 4 и т.д. первых значений диапазона:

Формула суммирует диапазон А1:А4. Вторая часть диапазона функции СУММ задана с помощью функции ВЫБОР.

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

Таблица с номерами месяцев и кварталов:

Так как финансовый год начался в апреле, месяцы 4, 5 и 6 попали в первый квартал. При введении аргументов функции, номера кварталов необходимо вводить в том порядке, в каком они находятся в таблице.

В ячейку D8 пользователь вводит номер месяца. В ячейке D9 функция ВЫБОР вычисляет номер финансового квартала.

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

В первом столбце вспомогательной таблицы – номера дней недели. В третьем столбце – количество дней, которое нужно прибавить к текущей дате, чтобы получить следующий вторник. Например, к понедельнику необходимо добавить 1 день, ко вторнику – 7 дней (до следующего вторника).

В ячейку F2 запишем текущую дату (СЕГОДНЯ()). А в ячейку F3 – формулу для расчета даты следующего вторника:

Индекс определяется с помощью функции ДЕНЬНЕД, которая возвращает для заданной даты соответствующего дня недели.

В этой статье описаны синтаксис формулы и использование функции ВЫБОР в Microsoft Excel.

Функция 05: CHOOSE (ВЫБОР)

Функция CHOOSE (ВЫБОР) возвращает значение из перечня, выбирая его в согласовании с числовым индексом.

Как можно применять функцию CHOOSE (ВЫБОР)?

Функция CHOOSE (ВЫБОР) может возвратить позицию из перечня, находящуюся под определенным номером, к примеру:

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

Синтаксис CHOOSE (ВЫБОР)

Функция CHOOSE (ВЫБОР) имеет последующий синтаксис:

• index_num (номер_индекса) должен быть меж 1 и 254 (либо от 1 до 29 в Excel 2003 и наиболее ранешних версиях).
• index_num (номер_индекса) быть может введён в функцию в виде числа, формулы либо ссылки на другую ячейку.
• index_num (номер_индекса) будет округлен до наиблежайшего наименьшего целого.
• аргументами value (значение) могут быть числа, ссылки на ячейки, именованные спектры, функции либо текст.

Ловушки CHOOSE (ВЫБОР)

В Excel 2003 и наиболее ранешних версиях функция CHOOSE (ВЫБОР) поддерживала только 29 аргументов value (значение).

Делать поиск по списку еще удобнее на рабочем листе, чем заносить все элементы в формулу. При помощи функций VLOOKUP (ВПР) либо MATCH (ПОИСКПОЗ) Вы сможете ссылаться на списки значений, расположенные на листах Excel.

Пример 3: Покажем сумму продаж для избранного магазина

Вы сможете применять функцию CHOOSE (ВЫБОР) в сочетании с иными функциями, к примеру, SUM (СУММ). В этом примере мы получим итоги продаж по определённому магазину, задав его номер в функции CHOOSE (ВЫБОР) в качестве аргумента, также перечислив спектры данных по любому магазину для подсчёта итогов.

В нашем примере номер магазина (101, 102 либо 103) введён в ячейке C2. Чтоб получить значение индекса, такое как 1, 2 либо 3, заместо 101, 102 либо 103, используйте формулу: =C2-100.

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

Снутри функции SUM (СУММ) в первую очередь будет выполнена функция CHOOSE (ВЫБОР), которая возвратит требуемый спектр для суммирования, соответственный избранному магазину.

Это пример ситуации, когда еще эффективнее применять остальные функции, такие как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ). Дальше в нашем марафоне мы увидим, как они работают.

Примеры использования

Теперь рассмотрим несколько примеров использования ПОИСКПОЗ. Ниже представлены основные сценарии, когда может пригодиться команда.

Поиск по одному критерию

Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:

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

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

3. Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.

4. Жмем клавишу Enter и видим готовый результат.

5. Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.

Подобным образом функция работает и для текстовых значений. Видим таблицу с месяцами года. Допустим, хотим узнать расположение августа относительно данного массива. Так же указываем ссылку на ячейку с названием месяца, затем выделяем массив и добавляем тип сопоставления. Если речь идет о текстовых значениях, то необходимо ставить 0.

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

Поиск по двум критериям

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

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

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

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Для вычисления позиции необходимо воспользоваться комбинацией Ctrl+Shift+Enter. Так вы запустите выполнение функции в массиве.

Топ 20 самых важных формул MS Excel

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

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

Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами