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

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

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

Транспонировать ячейки в один столбец на основе уникальных значений с помощью кода VBA

Перенести ячейки в один столбец на основе уникальных значений с помощью Kutools for Excel

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

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

1 . Введите эту формулу массива: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) в пустую ячейку, например, D2, и одновременно нажмите клавиши Shift + Ctrl + Enter , чтобы получить правильный результат, см. снимок экрана:

Примечание : в приведенной выше формуле A2: A16 – это столбец, из которого вы хотите перечислить уникальные значения, а D1 – это ячейка над этой ячейкой формулы.

2 . Затем перетащите маркер заполнения вниз к ячейкам, чтобы извлечь все уникальные значения, см. Снимок экрана:

3 . Затем введите эту формулу в ячейку E2: = ЕСЛИ ОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 16, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ D2: D2, $ B $ 2: $ B $ 16) + ЕСЛИ ($ A $ 2: $ A $ 16 $ D2, 1, 0), 0)), 0) и не забудьте нажать клавиши Shift + Ctrl + Enter , чтобы получить результат, см. снимок экрана:

Примечание . В приведенной выше формуле: B2: B16 – это данные столбца, которые вы хотите транспонировать, A2: A16 – это столбец, который вы хотите чтобы транспонировать значения на основе, а D2 содержит уникальное значение, которое вы извлекли на шаге 1.

4. Затем перетащите маркер заполнения вправо от ячеек, в которых вы хотите отобразить транспонированные данные, пока не отобразится 0, см. Снимок экрана:

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

Перенести ячейки в один столбец на основе уникальных значений с кодом VBA

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

1 . Удерживая нажатыми клавиши ALT + F11 , откройте окно Microsoft Visual Basic для приложений .

2 . Нажмите Вставить > Module и вставьте следующий код в окно Module .

Код VBA: транспонирование ячеек в одном столбце на основе уникальных значений в другом столбце:

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

4 . Затем нажмите кнопку OK , появится еще одно окно с напоминанием о том, что нужно выбрать ячейку для отображения результата, см. Снимок экрана:

6 . Нажмите кнопку OK , и данные в столбце B были перенесены на основе уникальных значений в столбце A, см. Снимок экрана:

Транспонировать ячейки в один столбец на основе уникальных значений с помощью Kutools for Excel

Если у вас есть Kutools for Excel , объединение Advanced Combine Rows и Split Cells утилит, вы можете быстро выполнить эту задачу без каких-либо формул или кода.

После установки Kutools for Excel , сделайте следующее:

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

2 . Затем нажмите Kutools > Merge & Split > Advanced Combine Rows , см. Снимок экрана:

3 . В диалоговом окне Объединить строки на основе столбца выполните следующие операции:

(1.) Щелкните имя столбца, данные которого вы хотите транспонировать на основе , и выберите Первичный ключ ;

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

4 . Затем нажмите кнопку Ok , данные в столбце B были объединены в одну ячейку на основе столбца A, см. Снимок экрана:

5 . Затем выберите объединенные ячейки и нажмите Kutools > Объединить и разделить > Разделить ячейки , см. Снимок экрана:

6 . В диалоговом окне Разделить ячейки выберите Разделить на столбцы под параметром Тип , а затем выберите разделитель, разделяющий ваши объединенные данные, см. снимок экрана:

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

8 . Нажмите ОК , и вы получите нужный результат. См. Снимок экрана:

Скачать бесплатно пробная версия Kutools for Excel прямо сейчас!

Демонстрация: транспонируйте ячейки в один столбец на основе уникальных значений с помощью Kutools for Excel