1

Мне нужно сравнить группу значений в таблице A с другой группой значений в таблице B. Если заголовки столбцов (имена столбцов в каждой таблице) одинаковы в обеих таблицах, то соответствующие значения в строках ниже будут сравнивать Мой вопрос состоит из двух вещей: 1) как я могу проверить, что заголовки столбцов в одной таблице соответствуют заголовкам столбцов в другой, и 2) как я могу выбрать и сравнить значения в соответствующих столбцах?

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

ID  Name    Address           ID    Item    Quan
1   A       123A               1    U       22
2   B       234B               2    V       33
3   C       345C               3    W       44
4   D       456D               4    X       55
5   E       567E               5    Y       66
6   F       678F               6    Z       77
7   G       789G               7    T       88

Во втором примере ниже заголовки столбцов совпадают. Однако, хотя значения в столбцах ID и Address совпадают, значения в адресе Name не совпадают. Какую формулу я могу использовать для сравнения значений во всех 3 столбцах, после того как стало ИСТИНА, что все заголовки столбцов совпадают?

ID  Name    Address               ID    Name    Address
1   A       123                    1    D   123
2   B       234                    2    C   234
3   C       345                    3    E   345
4   D       456                    4    B   456
5   E       567                    5    A   567
6   F       678                    6    F   678

Мой последний пример ниже, где, хотя имена заголовков столбцов совпадают, порядок столбцов отличается. Какую формулу я могу использовать для сравнения значений в столбцах ID, Имя и Адрес независимо от их порядка?

ID  Name    Address               ID    Address Name
1   A       123                    1    123     A
2   B       234                    2    234     B
3   C       345                    3    345     C
4   D       456                    4    456     D
5   E       567                    5    567     E
6   F       678                    6    678     F

Буду признателен за любую оказанную помощь.

2 ответа2

0

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

Я взял предоставленные вами данные и организовал их, и соответствующую формулу следующим образом ...

Чтобы определить, совпадают ли заголовки ...

  • Ячейка I1 содержит формулу Control-Shift-Enter (CSE) {=IF(ISERROR(SUM(MATCH(E2:G2,A2:C2,0))),FALSE,TRUE)} .
  • Ячейка I11 содержит формулу CSE {=IF(ISERROR(SUM(MATCH(E12:G12,A12:C12,0))),FALSE,TRUE)} .
  • Ячейка I20 содержит формулу CSE {=IF(ISERROR(SUM(MATCH(E21:G21,A21:C21,0))),FALSE,TRUE)}

Поднять заголовки для сравнения ...

  • Ячейка I2, заполненная до K2, содержит =IF($I$1,OFFSET($E$2,0,MATCH(A$2,$E$2:$G$2,0)-1),"")
  • Ячейка I12, заполненная до K12, содержит =IF($I$11,OFFSET($E$12,0,MATCH(A$12,$E$12:$G$12,0)-1),"")
  • Ячейка I21, заполненная до K21, содержит =IF($I$20,OFFSET($E$21,0,MATCH(A$21,$E$21:$G$21,0)-1),"")

Чтобы сделать сравнения по значениям ...

  • Ячейка I3, заполненная до I9, заполненная до K9, содержит =IF($I$1,MATCH(A3,OFFSET($E$3,0,MATCH(A$2,$E$2:$G$2,0)-1,COUNT($A$3:$A$9),1),0),"")
  • Ячейка I13, заполненная до I18, заполненная до K18, содержит =IF($I$11,MATCH(A13,OFFSET($E$13,0,MATCH(A$12,$E$12:$G$12,0)-1,COUNT($A$13:$A$18),1),0),"")
  • Ячейка I22, заполненная до I27, заполненная до K27, содержит =IF($I$20,MATCH(A22,OFFSET($E$22,0,MATCH(A$21,$E$21:$G$21,0)-1,COUNT($A$22:$A$27),1),0),"")
0

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

Пример 1: я скопировал бы строку 1 обоих листов и вставил: преобразовал их в новый лист, чтобы они заняли один столбец каждый (например, Cols A и B). Если столбцы должны быть для возврата TRUE, я бы добавил следующую формулу в C1 и скопировал ее в последнюю строку информации заголовка столбца.

IF(A1=B1, TRUE, FALSE)

Затем в D1 вставьте формулу:

=COUNTIF(C:C, FALSE)

Любое ненулевое значение в D1 означает, что столбцы не совпадают.

Если столбцы не должны быть в том же порядке, чтобы возвращать значение ИСТИНА, после процесса копирования отсортируйте каждый столбец, а затем добавьте формулы IF и COUNTIF.

Пример 2: После того, как вы решили, что столбцы совпадают (и они находятся в одном и том же порядке), я бы сравнил значения, вставив новый столбец справа от каждого листа, который СОЗДАТЬ все данные в оригинале. колонны). Учитывая предоставленные вами данные Ex2, эта формула сделает это в ячейке D1 каждого рабочего листа (скопируйте формулу в последнюю строку):

=CONCATENATE(A1, "##", B1, "##", C1)

Я добавляю хэши, чтобы сохранить объединенные данные дискретными. В противном случае, если вы скажете, 1 в столбце A и 10 в столбце B рабочего листа A и 11 в столбце A и 0 в столбце B рабочего листа B, они оба будут объединены в 110.

Затем вы добавляете аналогичную формулу IF к одному из листов:

=IF(sheet1!D1=sheet2!D1, TRUE, FALSE)

И снова добавьте формулу COUNTIF в следующий столбец, чтобы увидеть, не совпадают ли какие-либо значения.

=COUNTIF(D:D, FALSE)

Пример 3 (где порядок не имеет значения), я бы отсортировал данные, чтобы столбцы были расположены в алфавитном порядке, а затем выполнил решение для Ex2. Чтобы правильно отсортировать данные, выберите информацию и в группе лент «Данные» -> «Сортировка и фильтрация» нажмите «Сортировать». В появившемся диалоговом окне нажмите кнопку « Options.... и выберите « Sort left to right . Когда заголовок столбца в области полей изменится с «Столбец» на «Строка», выберите «Строка 1» в поле « Sort by . Это изменит порядок столбцов в обеих таблицах на (в ваших примерах): Address, Id, Name. Затем, как я уже сказал, следуйте процессу для Ex2.

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

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .