Поиск нескольких результатов по нескольким критериям

Содержание

Вопрос или проблема
Ответ или решение
Поиск нескольких результатов по нескольким критериям в Excel
Использование формул Excel
Решение с использованием VBA
Заключение

Вопрос или проблема

У меня есть список родительских артикулов (SKU) для товаров, которые мне нужно создать в моей системе учёта запасов. Этот список был тщательно сокращён до 165 продуктов, которые мы хотели бы иметь. Однако у каждого из этих 165 артикулов есть от 2 до 8 дочерних артикулов разных цветов, размеров и т.д. Они хранятся на другом листе, перемешанные с примерно 2500 товарами. Это те артикулы, которые мне нужно ввести в мою систему учёта запасов.

Вот как это выглядит.

Лист 1 — это просто артикулы:

Лист 2 состоит из всех дочерних артикулов, с родительскими артикулами в колонке B. Не у всех родителей одинаковое количество детей:

  A        B
1BLKM      1
1BLKL      1
1BLUM      1
2BLKM      2
2BLKL      2
2BLUM      2
2ORAM      2
3BLKM      3
3BLUM      3

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

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

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

=IFERROR(INDEX(Sheet2!$A$1:$A$11,SMALL(IF(ISERROR(MATCH(Sheet2!$B$1:$B$11,Sheet1!$A$1:$A$5,0)),2000000,ROW(Sheet2!$B$1:$B$11)),ROW())),"")

Чтобы сделать это формулой массива, вы должны ввести её, нажав Ctrl+Shift+Enter.

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

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

=IF(COUNTIF(Sheet2!$B:$B, $A1)<COLUMN(A1), "", INDEX(Sheet2!$A:$A, MATCH($A1, Sheet2!$B:$B, 0)+(COLUMN(A1)-1)))

вставьте описание изображения здесь

Ответ или решение

Поиск нескольких результатов по нескольким критериям в Excel

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

Использование формул Excel

Исходные данные:
- Лист 1: содержит список родительских артикулов (например, в столбце A).
- Лист 2: содержит все дочерние артикулы (в столбце A) и соответствующие родительские артикулы в столбце B.
Формула для получения дочерних артикулов:
Вы можете использовать следующую формулу, чтобы извлечь дочерние артикулы для каждого родительского артикула. Когда вы будете вводить формулу, используйте комбинацию клавиш Ctrl + Shift + Enter, так как это формула массива:
```
=IFERROR(INDEX(Sheet2!$A$1:$A$11, SMALL(IF(ISERROR(MATCH(Sheet2!$B$1:$B$11, Sheet1!$A$1:$A$5, 0)), 2000000, ROW(Sheet2!$B$1:$B$11)), ROW())), "")
```
- Sheet2!$A$1:$A$11 – диапазон дочерних артикулов.
- Sheet2!$B$1:$B$11 – диапазон родительских артикулов на Листе 2.
- Sheet1!$A$1:$A$5 – диапазон родительских артикулов на Листе 1.
- Функция IFERROR обрабатывает ошибки в случае отсутствия совпадений.
Непосредственное использование формулы:
Введите формулу в первой строке рядом с вашим списком родительских артикулов и протяните её вниз, чтобы получить все дочерние артикулы для всех артикулов из списка.
Неформальная версия формулы:
Альтернативная формула (которую можно ввести без массива) может выглядеть следующим образом:
```
=IF(COUNTIF(Sheet2!$B:$B, $A1) < COLUMN(A1), "", INDEX(Sheet2!$A:$A, MATCH($A1, Sheet2!$B:$B, 0) + (COLUMN(A1) - 1)))
```
- Эта формула должна быть введена в первую строку и затем копироваться вниз и вправо для охвата всех возможных дочерних артикулов.

Решение с использованием VBA

Если вы предпочитаете использовать программный код, вы можете использовать VBA для автоматизации процесса поиска и извлечения дочерних артикулов. Вот пример простого VBA-скрипта, который вы можете использовать:

Sub ExtractChildSKUs()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim parentSKU As Range, childSKU As Range
    Dim outputRow As Integer

    Set ws1 = ThisWorkbook.Sheets("Sheet1") ' Лист с родительскими артикулами
    Set ws2 = ThisWorkbook.Sheets("Sheet2") ' Лист с дочерними артикулами

    outputRow = 1

    ' Проход по списку родительских артикулов
    For Each parentSKU In ws1.Range("A1:A165") ' Предположим, что у вас 165 родительских SKU
        For Each childSKU In ws2.Range("B1:B2500")
            If childSKU.Value = parentSKU.Value Then
                ' Если совпадает, выводим дочерний артикул
                ws1.Cells(outputRow, 2).Value = ws2.Cells(childSKU.Row, 1).Value
                outputRow = outputRow + 1
            End If
        Next childSKU
    Next parentSKU
End Sub

Заключение

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

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