#excel #excel-formula
#excel #excel-формула
Вопрос:
У меня есть таблица Excel с настройками-значениями для разных частей. Каждый раз, когда изменяется параметр, мне нужен номер детали и параметр, на который он был изменен, в выходной таблице.
Я прочитал несколько возможных решений и сообщений, но у всех либо возникают проблемы при повторении настройки, либо при изменении длины списка.
По соображениям совместимости мне в настоящее время нужно избегать VBA.
Спасибо за любую помощь и идеи!
РЕДАКТИРОВАТЬ: я расширил файл примера, чтобы показать, что у меня есть несколько — на самом деле 12 — разных настроек для каждой детали.
Комментарии:
1. Какая версия Excel у вас есть?
2. Excel 2016, Office для дома и бизнеса 2016
3. Хотя я не изучал это, вероятно, это должно быть возможно с помощью PowerQuery
4. @JvdV Я предполагаю, что это будет возможно, и мне бы этого хотелось, хотя мои M-навыки и близко не подходят к поиску решения этой проблемы: x
Ответ №1:
Создайте вспомогательный столбец и поместите туда следующую формулу (в C5, для C4 вы только что поставили =B4 ):
IF(B4<>B5;B5;-1)
Очевидно, что вы перетаскиваете это по всему столбцу C. Затем вы настраиваете автофильтр, чтобы отображались только значения, отличные от -1.
Комментарии:
1. Это отлично работает для одного столбца настроек. Я отредактировал вопрос, чтобы уточнить, что у меня всего 12 настроек. Решение на основе фильтров потребует от меня расположения их друг под другом, что затруднит чтение данных, тем более что я добавляю вычисления в каждую выходную таблицу.
Ответ №2:
Хорошо, итак, после еще некоторых поисков и переделок я нашел довольно хорошее решение: на основе этой статьи от потрясающего Дейва Брунса @Excelject я нашел эту формулу:
Для настройки-столбцы: {=IFERROR(INDEX(Input[Setting_1],SMALL(IF(Input[Setting_1]<>OFFSET(Input[Setting_1],-1,),ROW(Input[Setting_1])-ROW(INDEX(Input[Setting_1],1,1)) 1),ROW()-4 1)),"")}
Для части-столбцы: {=IFERROR(SMALL(IF(Input[Setting_1]<>OFFSET(Input[Setting_1],-1,),ROW(Input[Setting_1])-ROW(INDEX(Input[Setting_1],1,1)) 1),ROW()-4 1),"")}
Обратите внимание на {} скобки — , означающие, что это и формула массива, для которой требуется Ctr Shift Enter.
Ответ №3:
Немного более простой вариант — для Part col:
=IFERROR(INDEX($A$1:$A$16,AGGREGATE(15,6,ROW($A$2:$A$16)/($B$1:$B$16<>$B$2:$B$17),ROW(A1))),"")
со ссылками на таблицы:
=IFERROR(INDEX(Input[[#All],[Part]],AGGREGATE(15,6,ROW(Input[Part])/(Input[[#All],[S1]]<>$B$2:$B$17),ROW(Input[@S1])-1)),"")
для Settings col:
=IFERROR(INDEX($B$1:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)/($B$1:$B$16<>$B$2:$B$17),ROW(B1))),"")
со ссылками на таблицы:
=IFERROR(INDEX(Input[[#All],[S1]],AGGREGATE(15,6,ROW(Input[Part])/(Input[[#All],[S1]]<>$B$2:$B$17),ROW(Input[@S1])-1)),"")
