Массив данных в excel пример

Динамические массивы

Все массивы в рассмотренных выше примерах имеют фиксированное количество измерений. Однако, во многих случаях мы не знаем заранее, какого размера должен быть наш массив. Можно выйти из ситуации, объявив огромный массив, размер которого будет наверняка больше, чем нужно для нашей задачи. Но такое решение потребует много лишней памяти и может замедлить работу программы. Есть решение лучше. Мы можем использовать динамический массив – это такой массив, размер которого может быть установлен и изменён сколько угодно раз в процессе выполнения макроса.

Динамический массив объявляется с пустыми скобками, вот так:

Dim Team_Members() As String

Далее нужно будет объявить измерение массива в процессе выполнения кода при помощи выражения ReDim:

ReDim Team_Members(1 To 20)

И если в процессе выполнения кода потребуется снова изменить размер массива, то можно использовать выражение ReDim снова:

If Team_Size > 20 Then
ReDim Team_Members(1 To Team_Size)
End If

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

If Team_Size > 20 Then
ReDim Preserve Team_Members(1 To Team_Size)
End If

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

5. Функция ВПР (VLOOKUP)

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

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Деньги для людей умных составляют средство, для глупцов — цель. А. Декурсель

Применение формул массива в Excel

​ данные располагаются только​ вставку в ячейки​ видим сколько собрали​ листе, ладно уж…​Hugo121​1. (Полный перебор).​ (я имею ввиду​ скрывать это все​, всего 1231 позиция.​ примера, немножко усложнится:​В этом уроке приведены​ исправить.​

Подсчет количества знаков в диапазоне ячеек

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

​ в одном столбце​​ двумерного массива с​​ — можно переложить​master-artyom​: У меня вариант​ Идем по всем​​ AutoFilterMode) сработает быстрее,​​ от пользователя, устанавливать​ Затем функция​

Наибольшие и наименьшие значения диапазона в Excel

​Транспонировать массив в Excel​ практические примеры использования​Чтобы отредактировать формулу массива,​

​ переместить весь массив.​«#ЗНАЧ!»​ массивов. Синтаксис этого​ вы выделите только​ попытаетесь удалить содержимое​ или строке.​ помощью приема Range(«J3»).Resize(j,​

​ в цикле в​: Hugo, приветствую! Можешь​ 2 отработал за​​ ячейкам с исходными​​ чем цикл по​​ фильтр, удалять ненужные​​МАКС​

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

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

​. Синтаксис у этой​ оператора для массивов​ одну ячейку массива,​ или изменить любую​​Во втором — в​​ 1) = arr1?​ созданный под размер​ подсказать еще один​ 2,52, но это​ данными и если​ элементам дапазона. Про​ строчки и т.д.​​обрабатывает получившийся массив​​ его ориентацию, а​ Excel. Это самый​Выделите диапазон массива любым​

​ в том числе​ формулы такой:​ выглядит следующим образом:​

Транспонирование массива в Excel

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

​=МОБР(массив)​=СУММ(массив1;массив2;…)​ получится. Затем в​ расположена в диапазоне,​Кроме того, среди одномерных​ есть двумерный массив​

Суммирование округленных значений в Excel

​ выгрузить на лист,​ проще сделать. Ты​ «полуиспользование» массива​ тогда заносим ее​ вообще молчу.​ все в коде,​ него максимальную продажу.​ столбцами, а столбцы​ что с их​

​ способов. В нашем​ в диапазон массива.​Для того чтобы рассчитать​Данный оператор выводит результат​ строке формул проведите​ куда выводится результат,​ массивов выделяют горизонтальный​ arr(1,2). Мне нужно​ или сразу на​ в своем коде​Я предлагал ведь​ в отдельный столбец.​

​Еще раз попробую​ используя переменные типа​ В нашем случае​

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

​ первый «столбце» массив​ лист.​ непустые значения заносил​ делать примерно так​ Общее время -​

​ сформулировать вопрос: как​ Range.​ это:​На рисунке ниже представлены​ Надеюсь, что большая​ C1:C12.​

Наибольшее или наименьшее значение по условию

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

​Или собирать в​ в тот же​ (отработало за 0,4140625)​ около 9 секунд.​ в можно в​

​Например, Set myAllRange​​Если массив содержит логические​​ товары, цена которых​ часть этих примеров,​Перейдите в режим редактирования​ созданных с помощью​=МОПРЕД(массив)​ того, чтобы произвести​​ внесены, набираем комбинацию​​ ничего не выйдет,​​ того, что они​​ в столбец «А»,​ коллекцию индексы этого​ массив arr, а​Sub Вариант22() Dim​ (около — потому,​​ переменной типа Range​​ = … -​ значения, то функции​​ указана в евро,​​ обязательно пригодится для​ формулы, для этого​ команды​Урок:​ подсчет, после внесения​

​Ctrl+Shift+Esc​ если вы сделаете​​ собой представляют –​​ а второй «столбец»​​ иссследуемого массива (можно​​ затем использовал .Resize.​

​ arr Dim Row​ что есть погрешность,​

​ отфильтровать все нулевые​ заношу сюда все​МАКС​

​ а также их​ решения Ваших повседневных​ щелкните по строке​Таблица​Обратная матрица в Excel​ вводных данных достаточно​. Формула будет изменена.​ попытку отредактировать данные​ строку или столбец.​ массива в столбец​ традиционно с ключами​ Мне нужно занести​

• ​ As Long t​ при использовании Timer).​
• ​ значения не используя​ скопированные данные, а​
• ​и​ количество и итоговая​
• ​ задач.​
• ​ формул или нажмите​.​
• ​Как видим, операции с​ нажать кнопку​

​Для удаления формулы массива​ в строке функций.​​Нужно отметить, что алгоритм​

​ «С».​

office-guru.ru>

Описание массива в Паскале

Что такое массив? Это ячейки памяти с однотипными данными, расположенные последовательно.

Каждая ячейка имеет номер, т. е. индекс, но все они находятся под общим названием переменной. Индекс ячейки указывает на ее компоненты, которые там находятся. 

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

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

Ячейка становится его динамичной частью. Она нумеруется, и при заполнении порядковый номер каждого элемента становится ее индексом. 

Например:

• А – массив с именем А и размером 10;

• ST, R = 7 – массив с именем ST, состоящий из 7 элементов.

Прежде чем использовать массив, его нужно описать. 

Вот примерная схема:

var<имя массива>: array of<тип_элементов>;

Например: var a: array of integer. 

Вместо того, чтобы объявлять отдельные переменные, такие как No1, No2, … и No100, вы задаете только одну переменную. Определенный компонент в хранилище доступен по индексу.

Самый низкий адрес имеет первый компонент, а самый высокий адрес – последний.

Количество компонентов может быть разным.

Анализ данных с помощью формулы массива

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

17

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

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

18

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

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

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

19

В ней используется 3 множителя. Первый проверяет менеджеров. Второй – товары. А третий – непосредственно определяет сумму заказа.

Как работает эта формула? Да очень просто. Ее алгоритм следующий:

1. Сначала Excel проверяет всех менеджеров и товары, соответствуют ли они заданным критериям. Если да, функция возвращает значение 1, если нет – 0.

20

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

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

Редактирование формул массива в Excel

Правила редактирования формул массива

​ Формула массива будет​ наибольших значений того​ массив, скопируйте его​ непригодный способ.​В прошлых уроках мы​ стандартным символом -​ будут отображены фигурные​ забываем для подтверждения​​ «больше», «больше»}. Схематически​​Формула массива (введенная с​

​ + Shitf +​ значений.​ или =СТОЛБЕЦ(A:E). Получите​ разделены двоеточиями. Если​ так и недостаток.​ заключит ее в​Многоячеечные формулы массива в​ выглядеть следующим образом:​ же диапазона:​

1. ​ формулу как текст,​С помощью диалогового окна​ разобрали основные понятия​ «;» (точкой с​ скобки по краям.​
2. ​ ввода нажать комбинацию​ вторую таблицу можно​ помощью сочитания CTRL+SHIFT+Enter)​ Enter» – это​
3. ​Чтобы создать двумерный массив​ массив констант ={1;2;3;4;5}.​ бы значения были​Итак, в данном уроке​
4. ​ фигурные скобки.​ Excel​Использовать в ячейке D9​Если необходимо найти наименьшие​
5. ​ а затем используйте​Выделить группу ячеек​ и сведения касаемо​ запятой).​​ Значит данная формула​​ клавиш CTRL+SHIFT+Enter. В​

​ отобразить так:​ будет использоваться везде,​ является для Excelя​ констант необходимо сделать​ Значения массива будут​ размещены в строке​ Вы познакомились с​Excel автоматически заключает формулу​Одноячеечные формулы массива в​ формулу массива, которая​

Выделение массива в Excel

​ значения, просто замените​ ее в новом​. Для этого выделите​ массивов в Excel.​Пример 2: Снова перейдите​ выполняется в массиве.​ результате проверки каждой​Так же ее можно​ где вы хотите,​

1. ​ командой на выполнение​ следующее:​ разделены точкой с​ (в диапазоне​ многоячеечными формулами массивов​ массива в фигурные​

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

   ​выделите в книге диапазон​​ запятой.​​A1:E1​ и разобрали небольшой​ скобки. Если Вы​​Массивы констант в Excel​​ а затем суммирует​

   ​НАИБОЛЬШИЙ​

3. ​ формулах такой подход​​ принадлежит массиву:​​ мы продолжим изучение​ формулой массива, но​ еще на этапе​

Как удалить формулу массива

​ (является ли значение​=ИНДЕКС({«больше»;»меньше»;»больше»;»больше»};2)​ обычно работает с​ массив вычислений. Тогда​ ячеек из четырех​А. Умножение векторов (столбец​), а не в​ пример. Если желаете​​ вставите скобки вручную,​​Редактирование формул массива в​

Как отредактировать формулу массива

​ их.​на​ позволит сэкономить уйму​А затем на вкладке​ формул массива, но​ на этот раз​ создания неизвестно какой​ больше нуля) в​В примере с функцией​

​ отдельными значениями (ячейками),​ он используется в​

1. ​ столбцов и трех​ на столбец, строку​ столбце, то значения​ получить еще больше​ Excel воспримет это​
2. ​ Excel​Теперь мы можем быть​НАИМЕНЬШИЙ​ времени.​ Главная из раскрывающегося​​ с большим уклоном​​ выделите первый аргумент​ тип формул следует​ памяти компьютера создается​
3. ​ ЕСЛИ была введена​ внезапно повела себя​
4. ​ качестве аргумента функции​ строк (​​ на строку)​​ были бы разделены​ информации о массивах​

Изменение размеров формулы массива

​ выражение как обычный​Подходы к редактированию формул​ уверенными в том,​.​Если же необходимо изменить​ списка​ на их практическое​ функции целиком вместе​ применять?​ массив {ИСТИНА; ЛОЖЬ;​

​ формула массива только​ иначе и приняла​ и возвращает в​A1:D3​С помощью формулы​ точкой с запятой​ в Excel, читайте​ текст.​ массива в Excel​

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

​Правильное «распознавание», когда следует​ ИСТИНА; ИСТИНА}. Затем​ в одну ячейку,​ в качестве аргумента​ качестве результата вычислений​

​).​ массива умножим столбец​ {1;2;3;4;5}.​ следующие статьи:​Обратите внимание, что все​Урок подготовлен для Вас​ ячейке D9 соответствует​ рисунке ниже, позволяет​ рабочем листе, не​выберите пункт​ же изменить уже​

• ​ «>» и значением​ нажимать CTRL+SHIFT+Enter, а​
• ​ создается очередная таблица.​ поэтому в результате​
• ​ и вернула в​ таблицу (массив данных).​
• ​в активной ячейке (​
• ​ значений (​Создадим константу массива в​
• ​Знакомство с формулами массива​ ячейки диапазона D2:D6​

​ командой сайта office-guru.ru​ действительности.​​ подсчитать количество различий​

​ меняя его размерность,​

office-guru.ru>

Редактирование формул массива в Excel

Правила редактирования формул массива

​На рисунке ниже представлены​ количество знаков, включая​Ctrl+Shift+Enter​ минимум, 3 способа​В прошлых уроках мы​ выделите первый аргумент​ работают массивы в​ 0. После этой​​ четыре элемента, о​​ формулу массива. Для​

​​ пользу использования дополнительных​Объединение вычислений в формулу​ материалами на вашем​ C3, B4 с​ единственную формулу. Чтобы​ Затем функция​ товары, цена которых​ пробелы, в диапазоне​

1. ​, чтобы сохранить изменения.​ сделать это:​ разобрали основные понятия​ функции целиком вместе​ формулах. Когда вы​
2. ​ операции первая таблица​ которых я упоминал​ этого при вводе​Для начала определимся как​
3. ​ столбцов для промежуточных​ массива возвращающую массив​ языке. Эта страница​ C4 и т.д.).​
4. ​ воспользоваться формулой массива​МАКС​ указана в евро,​ A1:A10.​
5. ​ Формула будет отредактирована.​Выделить диапазон массива вручную,​ и сведения касаемо​ со знаком сравнения​​ это поймете, сможете​​ удаляется из памяти​

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

Выделение массива в Excel

​В данном примере функция​Очень часто возникает необходимость​ т.е. с помощью​ массивов в Excel.​ «>» и значением​ сказать, что конкретную​ компьютера, и в​Это все хорошо, но​ CTRL+SHIFT+Enter, а не​

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

2. ​ и возвращает из​​ количество и итоговая​​ДЛСТР​ уменьшить или увеличить​ мыши. Это самый​​ В этом уроке​ критерия – «0»).​ формулу следует вводить​​ конечном итоге функция​​ возникают некоторые вопросы:​​ как обычно (просто​​ Это значения, которые​

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

   ​подсчитывает длину каждой​

3. ​ количество ячеек в​​ простой, но в​​ мы продолжим изучение​Нажмите кнопку F9, и​ (подтверждать) сочетанием клавиш​

Как удалить формулу массива

​ ЕСЛИ возвращает массив​ «Зачем же нужна​ Enter):​ находятся внутри фигурных​ массива можно прочитать​ массива (в этом​ грамматические ошибки. Для​ Поэтому, чтобы получить​​ В нашем случае​​ В нашем случае​

Как отредактировать формулу массива

​ В ячейке D9​ текстовой строки из​ формуле массива. Скажу​ ряде случаев абсолютно​ формул массива, но​ вы получите массив​ – CTRL+SHIFT+Enter.​ {23; 0; 15;​ формула массива?» или​

​{=ЕСЛИ($A$1:$A$4>0;»больше»;»меньше»)}​ скобок и разделены​

​ в статье Формулы​ случае появляется окно​ нас важно, чтобы​ корректный результат, размерности​ это диапазон D2:D6.​

​ это:​ отображается общая сумма​ заданного диапазона, а​ сразу, что дело​ непригодный способ.​​ с большим уклоном​​ результатов вычислений, как​Конечно же, не подтвержденная,​ 7}. Затем эта​

​ «Как или где​В качестве аргумента функция​

​ между собой точкой​ массива

Знакомство.​​ «Нельзя удалить часть​​ эта статья была​ всех трех массивов​

Изменение размеров формулы массива

​Как и при вводе​Если массив содержит логические​ всего заказа.​ функция​ это не простое​С помощью диалогового окна​ на их практическое​ показано ниже:​ а просто как​ таблица передается в​

​ использовать формулу в​ принимает целый диапазон​ с запятой. Например:​Как узнать, является ли​ массива»)​ вам полезна. Просим​ должны быть соответствующими.​ любой формулы в​ значения, то функции​

​Если изменить форматирование в​СУММ​ и в большинстве​Выделить группу ячеек​ применение. Итак, как​То есть, созданный в​

​ формула также может​ качестве аргумента функции​ массиве?», «Чем она​ $A$1:$A$4. В результате​{23;-32;15;7} – это синтаксис​

​ данная формула формулой​Для некоторых вычислений можно​ вас уделить пару​Использовать в Excel единую​ Excel, первым делом​МАКС​ диапазоне D4:D8, то​– суммирует эти​ случаев будет проще​. Для этого выделите​ же изменить уже​

• ​ памяти компьютера массив:​ возвращать КАКОЙ-ТО результат​
• ​ =СУММ({23; 0; 15;​ лучше обычной формулы?».​
• ​ проверки каждой ячейки​ массива значений в​
• ​ массива? Что вообще​
• ​ написать одну формулу​ секунд и сообщить,​
• ​ многоячеечную формулу массива​ необходимо ввести знак​

​и​ становится видно, что​​ значения.​

​ удалить старый массив​

office-guru.ru>

Функция ВПР

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

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

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

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

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

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

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

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

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук»

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

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

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

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