Ввод попарно сравниваемых значений в большую таблицу

ProgramBox

Ввод попарно сравниваемых значений в большую таблицу

Post author:admin
Запись опубликована:20 декабря, 2022
Post category:Вопросы по программированию

#excel

Вопрос:

У меня есть несколько больших (500-1000) наборов данных в следующем формате, использующих только первые две строки.

ID	значение
a-b	число
a-c	число
a-d	число
…	число
b-c	число
b-d	число

и так далее

Они сравнивают два значения и сохраняют их разницу, пропуская ранее выполненные сравнения. Я хочу поместить их в такую таблицу, как эта:

ID	a	b	c	d	e
a	/	число	число	число	число
b	число	/	число	число	число
c	число	число	/	число	число
d	число	число	число	/	число
e	число	число	число	число	/

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

1. Спасибо за редактирование, только что заметил, что первая таблица сломалась!

2. Несколько вопросов: 1) вы ищете решение на основе формул или VBA? 2) в ваших данных вы исключаете (?) a-a , b-b Но не c-c , d-d , e-e — опечатка или что-то неуловимое? 3) The lower left half of this table is easily prepared with offset, but how do I feed the values into the upper right half? можете ли вы поделиться тем, что вы сделали до сих пор? 4) They compare two values and save their difference while skipping previously done comparisons итак, у вас есть некоторые разреженные данные, вы заполняете таблицу тем, что у вас есть, а затем происходит еще один «проход», чтобы со временем вы заполнили пробелы?

3. Привет: 1) Если возможно, на основе формулы. 2) Это была опечатка, да. Теперь это должно быть исправлено. 3) Идея состоит в том, чтобы записать сравниваемую дату в ячейки вверху и справа от символов /, а затем использовать простую команду смещения, вставленную в «нижнюю половину», чтобы скопировать значения сверху, потому что значения для ячейки ba должны быть одинаковыми для ab. 4) Я не уверен, соответствует ли мой ответ на # 3 тому, что вы имели в виду.

4. Итак, вы хотите, чтобы данные отражались по диагонали?

Ответ №1:

Если я понимаю, что вы пытаетесь сделать, я бы предложил сделать это в 2 шага:

Настройте формулы в таблице результатов для «чтения» данных
Есть макрос для «сохранения» данных

Сначала заполните таблицу результатов этой формулой (пример для ячейки B2 ) — оставьте свою offset формулу внизу левой половины

 =IF(B$2=$A3, "", VLOOKUP(IF(B$2>$A3,$A3amp;"-"amp;B$2, B$2amp;"-"amp;$A3), $H$3:$I$35, 2, FALSE))

Это даст вам , совпадают ли идентификаторы строк и столбцов или совпадают или vlookup вкл row_id-column_id / column_id-row_id выкл, если они разные, гарантируя, что они упорядочены «низко-высоко» всегда, чтобы дать вам ваше зеркало по диагонали. Некоторые могут возразить, что дублирование vlookup неэффективно, но более неэффективно, чем an offset , и выяснить, как вставить одну формулу в одну диагональ, а другую — в другую? Кто действительно знает, и это просто, и это работает ИМХО

Затем поместите данные для текущего «прохода» в таблицу наблюдений, и ваши значения появятся в таблице через VLOOKUP

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

 Option Explicit

Sub replace_formula_with_value()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  Dim r_in As Range
  
  ' this is where my input data start
  Set r_in = Range(ws.Range("B3"), ws.Range("B3").End(xlDown).End(xlToRight))
  
  Dim r As Range
  For Each r In r_in
    If Not IsError(r.Value) Then
      If r.Value <> "" Then
        'overwrite formula with value by setting value to the evaluation of the formula in the cell
        r.Value = r.Value
      End If
    End If
  Next r
  

End Sub

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

1. Большое вам спасибо, это избавит меня от лишних хлопот. Если вы позволите мне последний вопрос: как мне теперь быстро заменить нижнюю (или верхнюю часть, на самом деле не имеет значения!) с формулой смещения «зеркального отображения»? Копирование кажется болезненным, когда задействованы диагонали.

2. Кажется, работает безупречно. Еще раз спасибо. Если возникнут какие-либо проблемы, я знаю, к какому мастеру Excel обратиться.