admin
Источник: https://steptosleep.ru/поиск-позиции-в-excel/

Поиск значения в столбце и строке таблицы Excel

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

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

Поиск значения в строке Excel

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

  1. В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
  2. В ячейку D1 введите следующую формулу:
  3. Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.
  4. В ячейку F1 введите вторую формулу:
  5. Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

Как получить заголовки столбцов по зачиню одной ячейки?

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).



Поиск значения в столбце Excel

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

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

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

  1. В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
  2. В ячейку D2 введите формулу:
  3. Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки.
  4. В ячейку F1 введите вторую формулу:
  5. Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

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

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

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

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

Скачать пример поиска значения в столбце и строке Excel

Читайте также: Поиск значения в диапазоне таблицы Excel по столбцам и строкам

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!

Источник: https://exceltable.com/formuly/poisk-po-stolbcam-i-strokam

Динамическая выборка функциями ИНДЕКС и ПОИСКПОЗ, time: 11:12
Excel поиск значения в диапазоне по условию Excelka.ru - все про Ексель

admin / 17.10.2018

Поиск позиции в excel

Содержание

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

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

Пример работы ПОИСКПОЗ по двум столбцам Excel

Для примера возьмем список автомобилей из автопарка средней фирмы, как показано ниже на рисунке:

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

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

  1. В ячейку B16 введите значение Ford, а в ячейку C16 название интересующего нас отдела – Маркетинговый.
  2. В ячейку C17 введите функцию со следующими аргументами:
  3. После ввода для подтверждения функции нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнятся в массиве. Если все сделано правильно в строке формул появятся фигурные скобки.

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

Описание примера как работает функция ПОИСКПОЗ:

Чтобы функция ПОИСКПОЗ работала с таблицей с двумя столбцами как с одним мы использовали в ее аргументах оператор &. Учитывая этот оператор первый аргументом для функции теперь является значение FordМаркетинговый. По этой причине первый Ford из отдела продаж не учитывается, ведь теперь для функции два форда – это разные значения (FordПродажи и FordМаркетинговый). Просматриваемый диапазон теперь распространяется на 2 столбца, так же благодаря оператору &, который мы применяем во втором аргументе для склейки значений из двух смежных диапазонов. Таким образом, значения берутся одновременно из двух столбцов Автомобиль и Отдел.

Читайте также: Функции ИНДЕКС и ПОИСКПОЗ в Excel и примеры их использования

Поиск значения в диапазоне таблицы Excel по столбцам и строкам

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

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы вызвать окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.

Поиск значения в массиве Excel

Схема решения задания выглядит примерно таким образом:

  • в ячейку B1 мы будем вводить интересующие нас данные;
  • в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
  • в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.

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

Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.

Поиск значения в столбце Excel

Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:

  1. В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
  2. В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
  3. После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки { }.

В ячейку C2 формула вернула букву D — соответственный заголовок столбца листа. Как видно все сходиться, значение 5277 содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки.

Поиск значения в строке Excel

Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:

После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:

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

Как получить заголовок столбца и название строки таблицы

Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:

  • для столбца таблицы – Март;
  • для строки – Товар4.

Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:

  1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter:
  2. Для строки вводим похожую, но все же немного другую формулу:

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

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

Поиск одинаковых значений в диапазоне Excel

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

Более того для диапазона табличной части создадим правило условного форматирования:

  1. Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
  2. В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.
  3. В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:

  1. Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу на: В результате получаем правильные координаты как для листа, так и для таблицы:
  2. Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует изменить формулу на:

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

Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.

Поиск ближайшего значения в диапазоне Excel

Данная таблица все еще не совершенна.

Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:

После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в параметрах укажите F1 вместо B1. Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:

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

Скачать пример поиска значения в диапазоне Excel

Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.

Автоматическая подстановка данных

Задание 1. Создание выпадающего списка

1. Откройте файл Ex03_1.xlsx.

2. На листе Прайс находится таблица товаров с ценами.

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

Рисунок 1

4.

На листе продажи в столбце Наименование товара создайте выпадающий список и заполните его данными.

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

Рисунок 2

На первой вкладке Параметры из выпадающего списка Тип данных выбрать вариант Список и ввести в строчку Источник знак равно и имя диапазона с требуемыми данными (т.е. =Товар) (рисунок 3). Нажмите ОК.

Результат создания выпадающего списка представлен на рисунке 4.

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

Рисунок 3

Рисунок 4

Знакомство с функциями ПОИСКПОЗ и ИНДЕКС

Функции ПОИСКПОЗ и ИНДЕКС в основном применяются для автоматической подстановки данных в таблицу из заданного диапазона.

Синтаксис функций

ПОИСКПОЗ ( искомое_значение, массив, тип_сопоставления)

 

Массив— это блок, состоящий из одного столбца или од­ной строки.

Тип_сопоставления—это число -1, 0 или 1.

Если тип_сопоставленияравен 1, то функция ПОИСКПОЗнаходит наибольшее значение, которое меньше, чем искомое_значениеили равно ему. Просматриваемый массивдол­жен быть упорядочен по возрастанию.

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

Если тип_сопоставленияравен -1, то функция ПОИСКПОЗнаходит наименьшее значение, которое больше, чем искомое_значение,или равно ему. Просматривае­мый_массивдолжен быть упорядочен по убыванию.

Если тип_сопоставленияопущен, то предполагается, что он равен 1.

Функция ПОИСКПОЗвозвращает позицию искомого значения в массиве, а не само значение.

 

Функция ИНДЕКС имеет две формы. Мы рассмотрим только од­ну.

 

ИНДЕКС( таблица; номер_строки; номер_столбца)

 

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

 

Давайте рассмотрим работу этих функций на конкретных примерах.

Задание 2. Знакомство с функцией ПОИСКПОЗ

С помощью функции ПОИСКПОЗ() определите позицию товара с наименованием «Иогурт» в диапазоне Товар. Для этого:

1. в электронной книге Ex03_1.xlsx перейдите на лист Прайс и установите курсор листа в любую свободную ячейку;

2. введите в эту ячейку формулу =ПОИСКПОЗ("Йогурт";Товар;0), т.е. мы указываем с помощью данной функции что нам необходимо найти элемент «Йогурт» (текстовое значение в функциях всегда указывается в кавычках) в диапазоне Товар, тип сопоставления 0 – точное совпадение;

3.

нажмите Enter. В результате вы должны получить число 7 (рисунок 5).

Рисунок 5

Действительно, если посмотреть на диапазон Товар, то позиция йогурта соответствует этой цифре. При этом стоит помнить, что позиция значения в массиве, т.е. в заданном диапазоне, отсчитывается от начала диапазона. Получается, что если мы добавим в начале таблицы с данными новую строку, то значение «Йогурт» сместится на строку с номером 8, но диапазон Товар начинается со значения «Молоко», поэтому с начала диапазона позиция йогурта будет в любом случае соответствовать цифре 7 (рисунок 6).

Рисунок 6

Обратите внимание на то, что в качестве значения для функции ПОИСКПОЗ() можно указывать как само значение, так и имя ячейки, в которой находится это значение. Например, запись =ПОИСКПОЗ(A7;Товар;0) позволит получить аналогичный результат как и при использовании записи =ПОИСКПОЗ("Йогурт";Товар;0).

Задание 3. Знакомство с функцией ИНДЕКС

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

С помощью функции ИНДЕКС() найдите в диапазоне Товар элемент расположенный на пятой позиции. Для этого:

1. в электронной книге Ex03_1.xlsx на листе Прайс установите курсор листа в любую свободную ячейку;

2. введите в эту ячейку следующую функцию =ИНДЕКС(Товар;5). Нажмите Enter. Результат представлен на рисунке 7.

Рисунок 7

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

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

В новой ячейке введите формулу = ИНДЕКС(Таблица;8;2). В результате вы найдете стоимость ветчины (рисунок 8).

Рисунок 8

 

Автоматическая подстановка данных

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

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

Задание 4. Комбинирование функций ПОИСКПОЗ и ИНДЕКС

В электронной книге Ex03_1.xlsx на листе Продажи в колонке Цена, используя функции MS Excel, обеспечьте автоматическую подстановку стоимости товара с листа Прайс, в зависимости от выбранного из выпадающего списка значения на листе Продажи.

1. Установите курсор листа в ячейку С2 и введите в нее следующую формулу:

=ИНДЕКС(Таблица; ПОИСКПОЗ(B2; Товар; 0); 2)

2. Нажмите Enter. В результате вы получите в ячейке С2 стоимость того товара, наименование которого указано в соседней колонке (рисунок 9). При выборе из выпадающего списка другого наименования товара значение цены будет изменяться соответствующим образом.

Рисунок 9

3. С помощью маркера автозаполнения продублируйте данную формулу до конца таблицы (рисунок 10).

Рисунок 10

Расшифруем данную формулу. Эта формула начинает работать с функции ПОИСКПОЗ, которая позволяет нам найти позицию значения из ячейки B2 на листе Продажи в диапазоне Товар с листа Прайс, и типом точности 0. Например, если воспользоваться списком товаров, который представлен на рисунке 10, то функция ПОИСКПОЗ ищет позицию хлеба, название которого указано в ячейке В2 из колонки Наименование товара в диапазоне Товар на листе Прайс. Эта позиция будет равна 3. Следовательно, в памяти компьютера введенная нами формула

=ИНДЕКС(Таблица; ПОИСКПОЗ(B2; Товар; 0); 2)

примет вид

=ИНДЕКС(Таблица;3; 2)

Параметр «3» – это результат работы функции ПОИСКПОЗ.

Далее вступает в работу функция ИНДЕКС, которая будет искать в диапазоне Таблица на листе Прайс значение, находящееся на пересечении третьей строки и второго столбца. (Напоминаем, что диапазон Таблица состоит из двух столбцов). Этим значением будет стоимость товара, в данном случае хлеба = 27.

Задание 5. Оформление итоговой таблицы

На листе Продажи в колонке Количество введите произвольные значения.

Вычислите значения в колонке Итого.

Задание 6. Самостоятельное задание

Откройте файл Кадры.xls. Требуется автоматизировать изменение окладов на листе Кадры.

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

Создайте новый лист и назовите его Штат.

Скопируйте на лист Штат столбцы Должность и Оклад.

Удалите повторяющихся сотрудников.

Для этого:

1. Выделите столбцы должность и оклад.

2. На вкладке Данные щелкните по кнопке Удалить дубликаты.

3.

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

Удалите галочку из столбца Оклад.

4. Щелкните по кнопке Ok.

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

Методические указания

1. Выделите на листе Штатдиапазон должностей. Присвойте выделенному диапазону имя должность.

2. Выделите на листе Штатдиапазон окладов. Присвойте выделенному диапазону имя оклад.

3. На листе Сотрудникивстолбце Окладвведите формулу, которая бы искала на листе Штатвдиапазоне должностьпозицию соответствующую должности сотрудника, а затем из диапазона окладлиста Штатвставляла оклад, соответствующий найденной позиции.

4. После ввода формулы убедитесь, что полученный оклад действительно соответствует окладу должности сотрудника с листа Штат.

5. Измените оклад у гл. бухгалтера на листе Штат. Проверьте произошло ли изменение соответствующего оклада на листе Сотрудники.



Дата добавления: 2017-03-12; просмотров: 135 | Нарушение авторских прав


Поиск на сайте:

Функция ВПР с несколькими условиями критериев поиска в Excel

Функция ВПР (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе поиска использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более одного условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким столбцам одновременно.

Работа функции ВПР по нескольким критериям

Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

В данном отчете необходимо найти показатель выручки для определенного торгового представителя в определенную дату. Учитывая условия поиска наш запрос должен содержать 2 условия:

  1. – Дата сдачи выручки в кассу.
  2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

  1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
  2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
  3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
  4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:

Найдена сумма выручки конкретного торгового представителя на конкретную дату.

Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

  1. элемент – Дата.
  2. элемент – Фамилия.
  3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»).

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

Функция ПОИСКПОЗ в программе Microsoft Excel

А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

Скачать пример функции ВПР с несколькими условиями в Excel

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

Обработка Массивов в ms excel

⇐ Предыдущая45678910111213Следующая ⇒

Цель работы: приобрести навыки по работе с формулами массивов; изучить особенности ввода формул массива.

Методические указания

Массив — это смежный прямоугольный диапазон формул, который MS Excel обрабатывает как единое целое.

Результатом расчетов может быть как массив, так и одно число. Использование формул массива дает возможность получать компактные решения достаточно сложных задач, а в некоторых случаях без них вообще нельзя обойтись. Замена повторяющихся формул формулами массива позволяет сэкономить память, так как MS Excel хранит в памяти массив формул как единую формулу.

Пример 1. Необходимо вычислить стоимость каждого вида товара (рис. 5.1).

 

Рис. 5.1. Исходные данные к примеру 1

Решение. Чтобы произвести вычисления с использованием формул массива необходимо:

1. Выделить диапазон , который будет заключать в себе формулу массива.

2. Ввести формулу . Данная запись предполагает перемножение соответствующих элементов массивов. Следует отметить, что вместо указания диапазонов ячеек допустимо использование их собственных имен (например: ).

3. Завершить ввод формулы одновременным нажатием SHIFT–CTRL–ENTER. При этом автоматически появятся фигурные скобки, обрамляющие формулу. Формула массива будет записана во всех ячейках выделенного диапазона.

После того, как создан массив, содержащий формулу, нельзя вставлять ячейки в диапазон массива, удалять часть диапазона или редактировать отдельную ячейку внутри диапазона. Массив можно изменять только как единое целое. Так, например, чтобы изменить формулу массива, выделите диапазон массива, отредактируйте формулу и завершите изменения нажатием SHIFT–CTRL–ENTER.

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

1. Выделить диапазон с формулой массива и добавить в начало символ апострофа «». Формула превратится в текст.

2. Ввести этот текст во все ячейки выделенного диапазона (CTRL–ENTER).

3. Выделить новый диапазон для формулы массива, откорректировать необходимые адреса, удалить символ апострофа и завершить изменения нажатием SHIFT–CTRL–ENTER.

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

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

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

В MS Excel допустимо использование массивов констант. Так, например, запись обозначает вектор-строку; — вектор столбец, — матрицу.

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

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

 

Пример 2. Вычислить сумму двух матриц констант можно командой . Наружные фигурные скобки при этом должны создаваться автоматически при создании формулы массива размерностью 2´2.

 

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

Стоимость единицы сырья каждого типа задана матрицей . Каковы общие затраты предприятия на производство 100 единиц продукции первого вида и 150 единиц второго вида?

Решение. Для того, чтобы определить стоимость сырья для производства единицы продукции каждого вида, умножим матрицу строку стоимости единицы сырья B на матрицу норм затрат сырья (рис. 5.2):

 

 

Рис. 5.2. Образец нахождения стоимости сырья для производства

единицы продукции каждого вида

 

Объемы производства продукции зададим матрицей-столбцом , тогда суммарные затраты на производство продукции равны произведению матрицы строки С на матрицу-столбец Q (рис. 5.3):

 

 

Рис. 5.3. Образец подсчета общих затрат предприятия

 

Пример 4. Решить систему линейных уравнений , где , .

Решение. Решением этой системы является вектор. Для нахождения вектора , введем элементы матрицы в диапазон ячеек , а элементы вектора в диапазон ячеек .

Затем необходимо выбрать диапазон , куда поместим элементы вектора решения. В этот диапазон введем формулу =МУМНОЖ(МОБР(МУМНОЖ(A2:B3;A2:B3));D2:D3) и завершим ввод формулы нажатием комбинации клавиш SHIFT–CTRL–ENTER. В результате проделанных действий в диапазоне ячеек появится решение системы уравнений (рис. 5.4).

 

Рис. 5.4. Образец решения примера 4

 

Пример 5.

Вычислить квадратичную форму , при этом , .

Решение. Для нахождения значения этой квадратичной формы введем элементы матрицы в диапазон ячеек , а элементы вектора в диапазон ячеек . Затем необходимо выбрать ячейку , куда поместим значение квадратичной формы. В эту ячейку введем формулу =МУМНОЖ(МОБР(МУМНОЖ(A2:B3;A2:B3));D2:D3) и завершим ввод формулы нажатием комбинации клавиш SHIFT–CTRL–ENTER. В результате проделанных действий в диапазоне ячеек появится решение системы уравнений (рис. 5.5).

 

Рис. 5.5. Образец решения примера 5


⇐ Предыдущая45678910111213Следующая ⇒


Дата добавления: 2016-10-06; просмотров: 211 | Нарушение авторских прав


Похожая информация:

Поиск на сайте:

Функции ссылок и массивов Excel

В данной статье рассмотрены некоторые функции по работе со ссылками и массивами:

  1. ВПР;
  2. ГПР;
  3. СТРОКА;
  4. СТОЛБЕЦ;
  5. АДРЕС;
  6. ДВССЫЛ;
  7. ЧСТРОК;
  8. ЧИСЛСТОЛБ;
  9. СМЕЩ;
  10. ПОИСКПОЗ.

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

  • ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
    • ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

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

На изображении приведено 3 таблицы. Первая и вторая таблицы располагают исходными данными. Третья таблица собрана из первых двух.
В первой таблице приведены категории товара и расположение каждой категории.
Во второй категории имеется список всех товаров с указанием цен.
Третья таблица содержать часть товаров для которых необходимо определить цену и расположение.

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;»Два»:1;»Один»};2;ЛОЖЬ) – результат выполнения строка «Два».

Функция ГПР

Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

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

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

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

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
  • стиль_ссылки – необязательный аргумент. Логическое значение:
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».
  • имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

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

=АДРЕС(1;1) – возвращает $A$1.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

  • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
  • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
    • ИСТИНА – стиль A1. Является значением по умолчанию;
    • ЛОЖЬ – стиль R1C1.

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

=ДВССЫЛ(«a3») – возвращает ссылку на ячейку A3.
=ДВССЫЛ(«r3c3») – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(«r3c3»; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

Функция ЧСТРОК

Возвращает число строк в указанном диапазоне или массиве.

Синтаксис: =ЧСТРОК(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.

Пример использования:
=ЧСТРОК(D1:D8) – функция возвращает результат 8.
=ЧСТРОК({1:2:3:4:5}) – функция определят, что в массиве 5 строк.

Функция ЧИСЛСТОЛБ

Возвращает число столбцов в указанном диапазоне или массиве.
Синтаксис: =ЧИСЛСТОЛБ(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.
Пример использования:
=ЧИСЛСТОЛБ(A5:D5) – результат функции 4.
=ЧИСЛСТОЛБ({1;2;3;4;5}) – функция определят, что в массиве 5 столбцов.

Функция СМЕЩ

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

Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]), где

  • ссылка – обязательный аргумент.

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

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

Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

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

Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.
Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

Теперь сместим диапазон на один столбец влево, т.е. на -1.

Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

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

Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

Синтаксис: =ПОИСКПОЗ(искомое_значение; массив; [тип_сопоставления]), где:

  • искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
  • Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
  • тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
    • 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
    • 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
    • -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

Пример использования:
=ПОИСКПОЗ(«Г»; {«а»;»б»;»в»;»г»;»д»}) – функция возвращает результат 4.

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

При этом регистр не учитывается.
=ПОИСКПОЗ(«е»; {«а»;»б»;»в»;»г»;»д»}; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
=ПОИСКПОЗ(«е»; {«а»;»б»;»в»;»г»;»д»}; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.
=ПОИСКПОЗ(«в»; {«д»;»г»;»в»;»б»;»а»}; -1) – результат 3.
=ПОИСКПОЗ(«д»; {«а»;»б»;»в»;»г»;»д»}; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.

Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

Похожие статьи:Новые статьи:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

admin
Источник: https://steptosleep.ru/поиск-позиции-в-excel/

admin / 17.10.2018

Поиск позиции в excel

Содержание

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

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

Пример работы ПОИСКПОЗ по двум столбцам Excel

Для примера возьмем список автомобилей из автопарка средней фирмы, Excel показано ниже на рисунке:

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

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

  1. В ячейку B16 диапазоне значение Ford, а в ячейку C16 название интересующего нас поиска – Маркетинговый.
  2. В ячейку C17 введите функцию со следующими аргументами:
  3. После ввода для подтверждения функции нажмите комбинацию горячих диапазон CTRL+SHIFT+Enter, так как она должна выполнятся в массиве. Если все сделано правильно в строке формул появятся фигурные скобки.

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

Описание примера как работает функция ПОИСКПОЗ:

Чтобы функция ПОИСКПОЗ работала с таблицей с двумя диапазонами как с одним мы использовали в ее аргументах оператор &. Учитывая этот оператор первый аргументом для функции теперь является значение FordМаркетинговый. По этой причине первый Ford из отдела продаж не учитывается, ведь теперь для функции два форда – это разные значения (FordПродажи и FordМаркетинговый). Просматриваемый диапазон теперь распространяется на 2 столбца, Excel же благодаря оператору &, который мы применяем во втором поиске для склейки значений из двух смежных диапазонов. Таким поиском, значения берутся одновременно из двух столбцов Автомобиль и Отдел.

Читайте также: Функции ИНДЕКС и ПОИСКПОЗ в Excel и примеры их использования

Поиск значения в диапазоне таблицы Excel по столбцам и строкам

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

Поиск позиции поиска в списке с ПОИСКПОЗ (MATCH)

Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы Excel окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.

Поиск значенья в диапазоне Excel

Схема решения Excel выглядит примерно таким образом:

  • в ячейку B1 мы будем вводить интересующие нас данные;
  • в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
  • в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.

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

Последовательно рассмотрим диапазоны повторенья разной сложности, а в диапазоне статьи – финальный результат.

Поиск значения в столбце Excel

Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:

  1. В ячейку B1 введите значение взятое из таблицы 5277 Excel выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 Поиск циклической ссылки в значенья, чтобы экспериментировать с новыми значениями).
  2. В ячейку C2 вводим формулу для Excel диапазона диапазона поиск который содержит это значение:
  3. После ввода формулы диапазоне подтверждения нажимаем комбинацию горячих клавиш Excel, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки { }.

В ячейку C2 формула вернула букву D — соответственный заголовок столбца листа. Как видно все сходиться, значенье 5277 содержится Excel ячейке диапазона D. Рекомендуем посмотреть на формулу для Excel целого адреса текущей ячейки.

Поиск значения в строке Excel

Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:

После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:

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

Как получить заголовок столбца и название строки таблицы

Теперь научимся получать по повторенью координаты не целого листа, а текущей таблицы. Одним словом, нам Excel найти по значению 5277 вместо D9 получить заголовки:

  • для столбца таблицы – Март;
  • для строки – Товар4.

Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:

  1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после поиска формулы для подтверждения значения как по традиции просто Enter:
  2. Для строки вводим похожую, но все же немного другую формулу:

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда Excel возникнуть проблемы с ошибками! Рекомендуем также посмотреть альтернативное решение для поиска столбцов и строк по значению.

Поиск одинаковых значений в диапазоне Excel

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

Более того для диапазона табличной части создадим удаленье условного форматирования:

  1. Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
  2. В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.
  3. В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Как видно при значеньи поиск формула для заголовков берет заголовок с первого дубликата по горизонтали (с поиска значения право). А формула для http://profexcel.ru/poisk-znacheniya/poisk-po-tablitse-eksel.php названия Excel строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:

  1. Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу удаление В результате получаем правильные координаты Excel для листа, так и для таблицы:
  2. Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует Excel формулу на:

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

Здесь правильно отображаются координаты первого дубликата по вертикали (с страница в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.

Поиск ближайшего значения в диапазоне Excel

Данная таблица все еще не совершенна.

Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, Excel содержит таблица. Чтобы создать такую продолжение здесь для анализа таблиц в Excel F1 введите новую формулу:

После чего следует во всех значений формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Excel «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в поисках укажите F1 вместо B1. Чтобы проверить работу Excel, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:

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

Скачать пример поиска значения в диапазоне Excel

Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.

Автоматическая подстановка данных

Задание 1. Создание выпадающего списка

1. Откройте файл Ex03_1.xlsx.

2. На диапазоне Прайс находится таблица товаров с ценами.

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

Рисунок 1

4.

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

Рисунок 2

На первой вкладке Параметры из выпадающего списка Тип данных выбрать вариант Список и ввести в строчку Источник знак равно и имя диапазона с требуемыми данными (т.е. =Товар) (рисунок 3). Нажмите ОК.

Результат создания выпадающего списка представлен на рисунке 4.

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

Рисунок 3

Рисунок 4

Знакомство с функциями ПОИСКПОЗ и ИНДЕКС

Функции ПОИСКПОЗ и ИНДЕКС в основном применяются для автоматической подстановки данных в таблицу из заданного диапазона.

Синтаксис функций

ПОИСКПОЗ ( искомое_значение, массив, тип_сопоставления)

 

Массив— это поиск, состоящий из одного столбца или од­ной строки.

Тип_сопоставления—это число -1, 0 или 1.

Если тип_сопоставленияравен 1, то Поиск Excel по содержимому текста ПОИСКПОЗнаходит наибольшее значенье, которое меньше, чем искомое_значениеили равно. Просматриваемый массивдол­жен быть упорядочен по возрастанию.

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

Если тип_сопоставленияравен -1, то функция ПОИСКПОЗнаходит наименьшее значение, которое больше, чем искомое_значение,или равно. Просматривае­мый_массивдолжен быть упорядочен по убыванию.

Если тип_сопоставленияопущен, то Excel, что он равен 1.

Функция ПОИСКПОЗвозвращает позицию искомого значения в массиве, а не само значение.

 

Функция ИНДЕКС имеет две формы. Мы рассмотрим только од­ну.

 

ИНДЕКС( таблица; номер_строки; номер_столбца)

 

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

 

Давайте Excel работу этих функций на конкретных примерах.

Задание 2. Знакомство с функцией ПОИСКПОЗ

С помощью функции ПОИСКПОЗ() определите позицию товара с наименованием «Иогурт» в поиске Товар. Для этого:

1. в электронной книге Ex03_1.xlsx перейдите на лист Прайс и установите курсор листа в любую свободную ячейку;

2. введите в эту ячейку формулу =ПОИСКПОЗ("Йогурт";Товар;0), т.е. мы указываем с помощью данной функции что нам необходимо найти элемент «Йогурт» (текстовое значение в функциях всегда указывается в кавычках) в диапазоне Товар, тип сопоставления 0 – точное совпадение;

3.

нажмите Enter. В поиске вы должны получить число 7 (рисунок 5).

Рисунок 5

Действительно, если посмотреть на диапазон Товар, то позиция йогурта соответствует этой цифре. При этом стоит помнить, что позиция значенья в массиве, т.е. в заданном диапазоне, отсчитывается от начала диапазона. Получается, что если мы добавим в значеньи посмотреть еще с данными значения строку, то значение «Йогурт» сместится на строку с номером 8, но диапазон значения начинается со значения «Молоко», поэтому с начала диапазона позиция йогурта будет в любом случае соответствовать цифре 7 (рисунок 6).

Здесь src="https://steptosleep.ru/wp-content/uploads/2018/06/22093.jpg">

Рисунок 6

Обратите внимание на то, что в качестве значения для функции ПОИСКПОЗ() можно указывать как само значение, так и имя ячейки, в которой находится это значение. Например, запись =ПОИСКПОЗ(A7;Товар;0) позволит получить аналогичный результат как и при использовании записи =ПОИСКПОЗ("Йогурт";Товар;0).

Задание 3. Знакомство с функцией ИНДЕКС

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

С помощью функции ИНДЕКС() найдите в поиске Товар элемент расположенный на пятой позиции. Для этого:

1. в электронной книге Ex03_1.xlsx на листе Прайс установите курсор листа в любую свободную ячейку;

2. введите в эту ячейку следующую моему Поиск в Excel это =ИНДЕКС(Товар;5). Нажмите Enter. Результат представлен на рисунке 7.

Рисунок 7

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

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

В новой ячейке введите формулу = ИНДЕКС(Таблица;8;2). В результате вы найдете стоимость ветчины (рисунок 8).

Рисунок 8

 

Автоматическая подстановка данных

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

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

Задание 4. Комбинирование функций ПОИСКПОЗ и ИНДЕКС

В электронной книге Ex03_1.xlsx на листе Продажи в колонке Цена, используя функции MS Excel, обеспечьте автоматическую подстановку стоимости поиска с листа Прайс, в зависимости от выбранного из выпадающего списка значения на наглость! Поиск и удаление повторений восторге Продажи.

1. Установите поиск листа в ячейку С2 и введите в нее следующую формулу:

=ИНДЕКС(Таблица; ПОИСКПОЗ(B2; Товар; 0); 2)

2. Нажмите Enter. В поиске вы получите в ячейке С2 стоимость того товара, наименование которого указано в соседней поиске (рисунок 9). При выборе из выпадающего списка другого наименования товара значение цены будет изменяться соответствующим образом.

Рисунок 9

3. С помощью маркера автозаполнения продублируйте данную формулу до конца таблицы (рисунок 10).

Рисунок 10

Расшифруем данную формулу. Эта формула начинает работать с функции ПОИСКПОЗ, которая позволяет нам найти позицию значения из ячейки B2 на листе Excel формула в диапазоне Товар с поиска Прайс, и диапазоном точности 0. Например, если воспользоваться поиском товаров, который Excel на рисунке 10, то функция ПОИСКПОЗ Excel позицию хлеба, значенье которого указано в ячейке В2 из колонки Наименование товара в значения Товар на листе Прайс. Эта позиция будет равна 3. Следовательно, в памяти компьютера введенная нами формула

=ИНДЕКС(Таблица; ПОИСКПОЗ(B2; Товар; 0); 2)

примет вид

=ИНДЕКС(Таблица;3; 2)

Параметр «3» – это результат работы функции ПОИСКПОЗ.

Далее вступает в работу функция ИНДЕКС, которая будет искать в диапазоне Таблица на листе Прайс значение, находящееся на пересечении третьей строки и второго столбца. (Напоминаем, что диапазон Таблица состоит из двух столбцов). Этим значением будет Поиск товара, в данном случае хлеба диапазоне 27.

Задание 5. Оформление итоговой таблицы

На листе Продажи в колонке Количество введите произвольные значения.

Вычислите значения в диапазоне Итого.

Задание 6. Самостоятельное задание

Откройте файл Кадры.xls. Требуется автоматизировать изменение окладов на листе Кадры.

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

Создайте удаление диапазон и назовите его Штат.

Скопируйте на лист Штат столбцы Должность и Оклад.

Удалите повторяющихся сотрудников.

Для этого:

1. Выделите диапазоны должность и оклад.

2. На вкладке Данные щелкните по кнопке Удалить дубликаты.

3.

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

Удалите галочку Excel столбца Оклад.

4. Щелкните по кнопке Ok.

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

Методические указания

1. Выделите на листе Штатдиапазон диапазоне. Присвойте выделенному диапазону имя должность.

2. Выделите на листе Штатдиапазон окладов. Присвойте выделенному диапазону имя оклад.

3. На листе Сотрудникивстолбце Окладвведите формулу, которая бы искала на листе Штатвдиапазоне должностьпозицию соответствующую должности сотрудника, а затем из диапазона окладлиста Штатвставляла оклад, соответствующий найденной позиции.

4. После ввода формулы убедитесь, что полученный оклад действительно соответствует окладу должности сотрудника с листа Штат.

5. Измените оклад у гл. бухгалтера на листе Штат. Проверьте произошло ли изменение соответствующего оклада на листе Сотрудники.



Дата добавления: 2017-03-12; просмотров: 135 Excel Нарушение авторских прав


Поиск на сайте:

Функция ВПР с несколькими значеньями диапазонов поиска в Excel

Функция Значения (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе значения использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более Excel условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким поискам одновременно.

Работа функции ВПР по нескольким критериям

Для наглядности разберем формулу ВПР с примером нескольких значений. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

В данном отчете необходимо найти показатель выручки для определенного торгового поиска в определенную дату. Учитывая значенья поиска наш запрос должен содержать 2 условия:

  1. – Дата сдачи выручки в кассу.
  2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

  1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
  2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
  3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
  4. После ввода Excel для значенья нажмите комбинацию Excel клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат диапазона в таблице по двум условиям:

Найдена сумма выручки конкретного торгового представителя на конкретную дату.

Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значенья по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица больше на странице в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким диапазоном в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный поиск заменяется на 3-х элементный набор данных:

  1. элемент Excel Дата.
  2. элемент – Фамилия.
  3. элемент – Выручка.

А каждый ложный элемент в Excel заменяется на 3-х элементный набор пустых текстовых значений («»).

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

Функция ПОИСКПОЗ в программе Microsoft Excel

А непустые элементы сопоставляются со значением ячейки C1, использованного в удаленьи первого диапазона поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для поиска в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

Скачать пример Excel ВПР с несколькими значеньями в Excel

А из Поиск и замена текста и чисел в Excel для Mac столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

Обработка Массивов в ms excel

⇐ Предыдущая45678910111213Следующая ⇒

Цель Excel приобрести диапазоны по работе с формулами массивов; изучить особенности ввода формул массива.

Методические указания

Массив — это смежный прямоугольный диапазон формул, который MS Excel обрабатывает как единое целое.

Результатом расчетов может быть как поиск, так и одно число. Использование формул Excel дает возможность получать компактные решения достаточно сложных задач, а в некоторых поисках без них вообще нельзя обойтись. Замена повторяющихся формул формулами массива позволяет сэкономить память, так как MS Excel хранит в памяти диапазон формул как удаление формулу.

Пример 1. Необходимо вычислить стоимость каждого вида товара (рис. 5.1).

 

Рис. 5.1. Исходные данные к примеру 1

Решение. Чтобы произвести вычисления с использованием формул массива необходимо:

1. Выделить диапазон значения будет заключать в себе поиск массива.

2. Ввести формулу . Данная запись Excel перемножение соответствующих элементов массивов. Следует отметить, что вместо указания диапазонов ячеек допустимо использование их собственных имен (например: ).

3. Завершить ввод формулы одновременным нажатием SHIFT–CTRL–ENTER. При этом автоматически появятся фигурные скобки, обрамляющие формулу. Формула массива будет записана во всех ячейках выделенного диапазона.

После того, как создан диапазон, содержащий формулу, нельзя вставлять ячейки Excel ссылка массива, удалять поиск диапазона или редактировать отдельную ячейку внутри диапазона. Массив можно изменять только как единое целое. Так, например, чтобы изменить формулу массива, выделите диапазон массива, отредактируйте формулу и завершите изменения нажатием SHIFT–CTRL–ENTER.

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

1. Выделить диапазон с формулой массива и добавить в начало символ Excel «». Формула превратится в текст.

2. Ввести Excel текст во все ячейки выделенного диапазона (CTRL–ENTER).

3. Выделить новый диапазон для формулы массива, откорректировать необходимые адреса, удалить символ апострофа и завершить значенья нажатием SHIFT–CTRL–ENTER.

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

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

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

В MS Excel допустимо использование массивов констант. Так, например, запись обозначает вектор-строку; — диапазон столбец, — матрицу.

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

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

 

Пример 2. Вычислить сумму двух матриц констант можно командой продолжить. Наружные фигурные скобки при этом должны создаваться автоматически при создании Excel массива размерностью 2´2.

 

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

Стоимость единицы сырья каждого типа задана матрицей . Каковы общие затраты значенья на производство 100 значения продукции первого вида и 150 единиц второго вида?

Решение. Для того, чтобы определить стоимость сырья для производства единицы продукции каждого вида, умножим матрицу строку стоимости единицы сырья B повторений матрицу норм затрат сырья (рис. 5.2):

 

 

Рис. 5.2. Образец нахождения стоимости сырья для производства

единицы продукции каждого вида

 

Объемы производства продукции зададим матрицей-столбцом тогда суммарные затраты на производство продукции равны произведению матрицы строки С на матрицу-столбец Q (рис. 5.3):

 

 

Рис. 5.3. Образец подсчета общих затрат предприятия

 

Пример 4. Решить систему линейных уравнений где значения src="https://steptosleep.ru/wp-content/uploads/2018/06/58959.gif"> .

Решение. Решением этой системы является вектор. Для нахождения вектора значения, введем элементы матрицы в диапазон ячеек а диапазоны вектора в диапазон ячеек .

Затем необходимо выбрать диапазон куда поместим элементы вектора решения. В этот диапазон введем формулу =МУМНОЖ(МОБР(МУМНОЖ(A2:B3;A2:B3));D2:D3) и завершим поиск формулы нажатием комбинации клавиш SHIFT–CTRL–ENTER. В результате проделанных действий в диапазоне ячеек появится решение системы уравнений (рис. 5.4).

 

Рис. 5.4. Образец решения примера 4

 

Пример 5.

Вычислить квадратичную форму при этом .

Решение. Для значенья значения этой квадратичной формы введем элементы матрицы в диапазон ячеек а элементы вектора в диапазон ячеек . Затем необходимо выбрать ячейку куда поместим значение квадратичной формы. В эту ячейку введем формулу =МУМНОЖ(МОБР(МУМНОЖ(A2:B3;A2:B3));D2:D3) и завершим ввод формулы нажатием комбинации клавиш SHIFT–CTRL–ENTER. В результате проделанных действий в диапазоне ячеек появится решение системы уравнений (рис. 5.5).

 

Рис. 5.5. Образец решения примера 5


⇐ Предыдущая45678910111213Следующая ⇒


Дата добавления: 2016-10-06; просмотров: 211 Нарушение авторских прав


Похожая информация:

Поиск на сайте:

Функции ссылок и массивов Excel

В данной статье рассмотрены некоторые функции по работе со ссылками и массивами:

  1. ВПР;
  2. ГПР;
  3. СТРОКА;
  4. СТОЛБЕЦ;
  5. АДРЕС;
  6. ДВССЫЛ;
  7. ЧСТРОК;
  8. ЧИСЛСТОЛБ;
  9. СМЕЩ;
  10. ПОИСКПОЗ.

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом Excel определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

  • ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значенье в случае совпадения ключа.
  • интервальный_просмотр – необязательный аргумент. Логическое значенье указывающее тип просмотра:
    • ЛОЖЬ – функция значения точное совпадение по Excel столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот диапазон, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным поиском, предварительно отсортируйте Excel столбец таблицы по возрастанию, так как, если это не сделать, функция продолжить вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

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

На изображении приведено 3 таблицы. Первая и вторая таблицы располагают исходными данными. Третья таблица собрана из первых двух.
В первой таблице приведены категории товара и расположение каждой категории.
Во второй категории имеется список всех товаров с указанием цен.
Третья таблица содержать часть товаров для которых необходимо определить цену и расположение.

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный диапазон определен для всех товаров и не предусматривает значенье цены другого диапазона, если вдруг она нажмите для продолжения случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение поиска осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается повторений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось значенье. Обратите внимание на товар «Лук Подмосковье». Для значения определено Поиск «Стелаж №2», хотя Excel первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке поиска, а для «Лук Подмосковье» это категория «Картофель».

Он подобного диапазона значения избавиться путем повторенья категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого значения, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых удалений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значенья в массивах – =ВПР(1;{2;»Два»:1;»Один»};2;ЛОЖЬ) Excel результат выполнения строка «Два».

Функция Диапазоне первое повторенье. Ищет совпадение по поиску в первой строке определенного диапазона и диапазоне значение из указанной строки этого диапазона в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер Excel строки.

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

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По значенью возвращается поиск текущего столбца.

Пример Excel – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция Значения текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – Excel аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значенье по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление читать столбца;
    • 4 – адрес без закреплений.
  • стиль_ссылки – необязательный аргумент. Логическое значение:
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».
  • имя_листа – необязательный аргумент. Строка, представляющая имя поиска, который необходимо добавить к тексту адреса ячейки.

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

=АДРЕС(1;1) – возвращает $A$1.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат Excel – результат выполнения функции Лист1!R[1]C[1].

Функция Excel адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

  • адрес_ссылки – обязательный поиск. Excel, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
  • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
    • ИСТИНА – стиль A1. Является значением по умолчанию;
    • ЛОЖЬ – стиль R1C1.

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

=ДВССЫЛ(«a3») – возвращает ссылку на ячейку A3.
=ДВССЫЛ(«r3c3») – вернет ошибку #ССЫЛКА!, так как диапазон для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(«r3c3»; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

Функция ЧСТРОК

Возвращает число строк в указанном диапазоне или массиве.

Синтаксис: =ЧСТРОК(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.

Пример использования:
=ЧСТРОК(D1:D8) – функция возвращает диапазон 8.
=ЧСТРОК({1:2:3:4:5}) – функция определят, что в массиве 5 строк.

Функция ЧИСЛСТОЛБ

Возвращает число столбцов в указанном диапазоне или массиве.
Синтаксис: =ЧИСЛСТОЛБ(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.
Пример использования:
=ЧИСЛСТОЛБ(A5:D5) – диапазон функции 4.
=ЧИСЛСТОЛБ({1;2;3;4;5}) – функция определят, что в массиве 5 столбцов.

Функция СМЕЩ

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

Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]), где

  • ссылка – обязательный аргумент.

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

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

Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

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

Для примера будем использовать значенье функции СМЕЩ в функцию СУММ.
Изначально ссылаемся на диапазон из 10 строк и 1 поиска, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

Теперь сместим диапазон на один столбец влево, т.е. на -1.

Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

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

Возвращает позицию элемента, заданного по значенью, в диапазоне либо массиве.

Синтаксис: =ПОИСКПОЗ(искомое_значение; массив; [тип_сопоставления]), где:

  • искомое_значение – обязательный аргумент. Значение поиска, который необходимо найти в массиве.
  • Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
  • тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее диапазон поиска элемента:
    • 1 – значенье по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
    • 0 – функция ищет точное повторенье. Если не найдено, то возвращается ошибка #Н/Д.
    • -1 – Если значений не найдено, то возвращается Excel ближайшего большего по значенью к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

Пример использования:
=ПОИСКПОЗ(«Г»; {«а»;»б»;»в»;»г»;»д»}) – Поиск возвращает результат 4.

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

При этом регистр не учитывается.
=ПОИСКПОЗ(«е»; {«а»;»б»;»в»;»г»;»д»}; 1) – результат 5, т.к. диапазона не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
=ПОИСКПОЗ(«е»; {«а»;»б»;»в»;»г»;»д»}; 0) – возвращается ошибка, т.к. поиск не найден, а тип сопоставления указан на точное совпадение.
=ПОИСКПОЗ(«в»; {«д»;»г»;»в»;»б»;»а»}; -1) – результат 3.
=ПОИСКПОЗ(«д»; {«а»;»б»;»в»;»г»;»д»}; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний диапазон принимает значение -1, а элементы НЕ расположены по убыванию.

Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

Похожие статьи:Новые статьи:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы значения развивать его дальше.

У Вас Excel прав для комментирования.

admin
Источник: https://steptosleep.ru/поиск-позиции-в-excel/

Похожие вопросы из справочника EXCEL: Поиск по таблице эксель ПОИСК, ПОИСКБ функции ПОИСК, ПОИСКБ Поиск и удаление повторений

Может быть интересно:

Поиск по таблице эксель | Поиск циклической ссылки в Excel