Progress-servis55.ru

Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel вхождение в массив

Excel вхождение в массив

Модератор форума: _Boroda_, Manyasha, SLAVICK, китин
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определение вхождения значения в массиве для определения фак (Формулы/Formulas)

Определение вхождения значения в массиве для определения фак

AlexHolodДата: Воскресенье, 09.12.2018, 10:29 | Сообщение № 1

Определение вхождения значения в массиве для определения фактически отработанного времени

Ребят прошу помощи. Нужна формула или макрос для екселя. Суть: в столбце А определённое число, в столбце Б фамилия, ВПРом сделал массив, теперь у меня при вводе значения (цифрового) в столбец С подбирает фамилию и вписывает его в столбец Д. необходимо сделать так, что при каждом четном повторе значения в столбце С программа выдавала надпись в столбец Е =повтор=, при нечетном другое к примеру =один=. Смысл : есть считыватель карт с картами, необходима на предприятии регистрация прибывающих и убывающих сотрудников, для контроля служебного времени. и вот с Простановкой значения =Убыл= у меня проблема. помогите кто чем может. Пример и то, что я наработал на втором листе. ЛИБО по другому еще скажу, нужен «журнал посещений». я в екселе не силен, но если направите в нужное русло, думаю смогу сделать.

Определение вхождения значения в массиве для определения фактически отработанного времени

Ребят прошу помощи. Нужна формула или макрос для екселя. Суть: в столбце А определённое число, в столбце Б фамилия, ВПРом сделал массив, теперь у меня при вводе значения (цифрового) в столбец С подбирает фамилию и вписывает его в столбец Д. необходимо сделать так, что при каждом четном повторе значения в столбце С программа выдавала надпись в столбец Е =повтор=, при нечетном другое к примеру =один=. Смысл : есть считыватель карт с картами, необходима на предприятии регистрация прибывающих и убывающих сотрудников, для контроля служебного времени. и вот с Простановкой значения =Убыл= у меня проблема. помогите кто чем может. Пример и то, что я наработал на втором листе. ЛИБО по другому еще скажу, нужен «журнал посещений». я в екселе не силен, но если направите в нужное русло, думаю смогу сделать. AlexHolod

Сообщение Определение вхождения значения в массиве для определения фактически отработанного времени

Ребят прошу помощи. Нужна формула или макрос для екселя. Суть: в столбце А определённое число, в столбце Б фамилия, ВПРом сделал массив, теперь у меня при вводе значения (цифрового) в столбец С подбирает фамилию и вписывает его в столбец Д. необходимо сделать так, что при каждом четном повторе значения в столбце С программа выдавала надпись в столбец Е =повтор=, при нечетном другое к примеру =один=. Смысл : есть считыватель карт с картами, необходима на предприятии регистрация прибывающих и убывающих сотрудников, для контроля служебного времени. и вот с Простановкой значения =Убыл= у меня проблема. помогите кто чем может. Пример и то, что я наработал на втором листе. ЛИБО по другому еще скажу, нужен «журнал посещений». я в екселе не силен, но если направите в нужное русло, думаю смогу сделать. Автор — AlexHolod
Дата добавления — 09.12.2018 в 10:29

Формулы массива в Excel

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

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

  1. выделяем ячейку С7
  2. вводим с клавиатуры =СУММ(
  3. выделяем диапазон B2:B5
  4. вводим знак умножения (звездочка)
  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

  • чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter
  • Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

    Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter.

    Пример 2. Разрешите Вас. транспонировать?

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

    Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

    • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
    • вводим функцию транспонирования =ТРАНСП(
    • в качестве аргумента функции выделяем наш массив ячеек A1:B8

    жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

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

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

    Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

    Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

    Пример 3. Таблица умножения

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

    При помощи формул массива она вся делается в одно движение:

    1. выделяем диапазон B2:K11
    2. вводим формулу =A2:A11*B1:K1
    3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

    и получаем результат:

    Пример 4. Выборочное суммирование

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

    В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

    Найти в ячейке любое слово из списка

    Предположим, вы получаете от поставщика/заказчика/клиента заполненную таблицу с перечнем продукции:

    и эту таблицу необходимо сравнить с артикулами/кодами товара в имеющемся у вас каталоге продукции:

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

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

    Tips_All_AnyoneOfArray.xls (49,5 KiB, 15 987 скачиваний)

    На листе «Заказ» в этом файле таблица, полученная от заказчика, а на листе «Каталог» наши артикулы.
    Сама формула на примере файла будет выглядеть так:

    =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2 );Каталог!$A$2:$A$11)
    =LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)
    эта формула вернет название артикула, если в тексте есть хоть один артикул из каталога и #Н/Д (#N/A) если артикул не найден в каталоге.
    Прежде чем облагородить эту формулу всякими дополнениями(вроде виде убирания ненужных #Н/Д ) давайте разберемся как она работает.
    Функция ПРОСМОТР (LOOKUP) ищет заданное значение( 2 ) в указанном диапазоне(массиве — второй аргумент). В качестве диапазона обычно приводится массив ячеек, но функция ПРОСМОТР имеет первую нужную нам особенность — она старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом. Иными словами она вычисляет выражение в этом аргументе, чем мы и пользуемся, подставив в качестве второго аргумента выражение: 1/ПОИСК(Каталог!$A$2:$A$11;A2) . Часть ПОИСК(Каталог!$A$2:$A$11;A2) ищет поочередно каждое значение из списка Каталога в ячейке A2 (наименование из таблицы Заказчика). Если значение найдено, то возвращается номер позиции первого символа найденного значения. Если значение не найдено — возвращается значение ошибки #ЗНАЧ!(#VALUE!). Теперь вторая особенность: функция требует расположения данных в массиве в порядке возрастания. Если данные расположены иначе — функция будет просматривать массив до тех пор, пока не найдет значение больше искомого, но максимально к нему приближенное(хотя если данные позволяют — для более точного поиска все же лучше отсортировать список по возрастанию). Поэтому сначала мы 1 делим на выражение ПОИСК(Каталог!$A$2:$A$11;A2) , чтобы получить массив вида: <0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!>
    Ну а в качестве искомого значения мы подсовываем функции число 2 — заведомо большее число, чем может вообще встретиться в массиве(т.к. единица, поделенная на любое число будет меньше двух). И как результат мы получим позицию в массиве, в которой встречается последнее совпадение из каталога. После чего функция ПРОСМОТР запомнит эту позицию и вернет значение из массива Каталог!$A$2:$A$11 (третий аргумент), записанное в этом массиве для этой позиции.
    Вы можете просмотреть этапы вычисления функции самостоятельно для каждой ячейки, я здесь просто приведу этапы чуть в расширенном для понимания виде:

    Теперь немного облагородим функцию и сделаем еще пару реализаций
    Реализация 1:
    Вместо артикулов и #Н/Д выведем для найденных позиций «Есть» , а для отсутствующих «Не найден в каталоге» :
    =ЕСЛИ(ЕНД(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2)));»Не найден в каталоге»;»Есть»)
    =IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),»Не найден в каталоге»,»Есть»)
    работа функции проста — с ПРОСМОТР(LOOKUP) разобрались, поэтому остались только ЕНД и ЕСЛИ.
    ЕНД (ISNA) возвращает ИСТИНА (TRUE) если выражение внутри неё возвращает значение ошибки #Н/Д (#N/A) и ЛОЖЬ (FALSE) если выражение внутри не возвращает значение этой ошибки.
    ЕСЛИ (IF) возвращает то, что указано вторым аргументом если выражение в первом равно ИСТИНА (TRUE) и то, что указано третьим аргументом, если выражение первого аргумента ЛОЖЬ (FALSE) .

    Реализация 2:
    Вместо #Н/Д выведем «Не найден в каталоге» , но при этом если артикулы найдены — выведем названия этих артикулов:
    =ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);»Нет в каталоге»)
    =IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),»Нет в каталоге»)
    Про функция ЕСЛИОШИБКА (IFERROR) я подробно рассказывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0.
    Если вкратце, то если выражение, заданное первым аргументом функции, возвращает значение любой ошибки, то функция вернет то, что записано вторым аргументом(в нашем случае это текст «Не найден в каталоге» ). Если же выражение не возвращает ошибку, то функция ЕСЛИОШИБКА запишет то значение, которое было получено выражением в первом аргументе(в нашем случае это будет наименование артикула).

    Реализация 3
    Надо не просто определить какому артикулу соответствует, но и вывести цену для наименования по этому артикулу(сами цены должны быть расположены в столбце B листа Каталог):
    =ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);»»)
    =IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),»»)

    Пара важных замечаний:

    • данные на листе с артикулами не должны содержать пустых ячеек . Иначе с большой долей вероятности формула будет возвращать значение именно пустой ячейки, а не то, которое подходит под условия поиска
    • формула осуществляет поиск таким образом, что ищется любое совпадение. Например, в качестве артикула записана цифра 1, а в строке наименований может встречаться помимо целой 1 еще и 123, 651123, FG1412NM и т.п. Для всех этих наименований может быть подобран артикул 1, т.к. он содержится в каждом наименовании. Как правило это может произойти, если артикул 1 расположен в конце списка

    Поэтому желательно перед использованием формулы отсортировать список по возрастанию(от меньшего к большему, от А до Я).

    В приложенном в начале статьи примере вы найдете все разобранные варианты.

    Если же вам понадобится выводить все наименования, то можно воспользоваться функцией СОДЕРЖИТ_ОДНО_ИЗ из моей надстройки MulTEx.

    Статья помогла? Поделись ссылкой с друзьями!

    Пример функции ПОИСКПОЗ для поиска совпадения значений в Excel

    Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

    Примеры использования функции ПОИСКПОЗ в Excel

    Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

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

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

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

    Формула для поиска неточного совпадения текста в Excel

    Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

    Вид исходной таблицы данных:

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

    Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

    Сравнение двух таблиц в Excel на наличие несовпадений значений

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

    Вид таблицы данных:

    Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

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

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

    Как видно, третьи элементы списков не совпадают.

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

    Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

    Вид исходной таблицы данных:

    Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

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

    Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

    Особенности использования функции ПОИСКПОЗ в Excel

    Функция имеет следующую синтаксическую запись:

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

    • искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
    • просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
    • [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
    1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
    2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
    3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.
    1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
    2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
    3. Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
    4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.
    Читать еще:  Автоматическая фильтрация в excel
    Ссылка на основную публикацию
    Adblock
    detector