У меня большая таблица (20-30 столбцов, 10-15 тыс. строк). Мне нужно найти количество строк, которые совпадают идентично в одном (или двух) определенных полях, но не совпадают в другом определенном поле (остальные поля не имеют значения). Кроме того, я хотел бы иметь возможность использовать условное форматирование для выделения таких строк. Возможно ли это сделать без скриптов?
Пример таблицы с 10 строками и 4 столбцами:
2.558658418 106.47 STSGGTAALGCLVK P01857
2.558658418 106.47 STSGGTAALGCLVK P01860 <--
2.4505791896 106.4 LYHSEAFTVNFGDTEEAK P01009
1.3850997023 106.34 EQADFAIEALAK P35579
1.3850997023 106.34 EQADFALEALAK Q7Z406
0.6654422739 105.36 RFDEILEASDGIMVAR P14618-2
2.0767656337 105.26 STSESTAALGCLVK P01859
2.0767656337 105.26 STSESTAALGCLVK P01859
2.0767656337 105.26 STSESTAALGCLVK P01861 <--
2.0767656337 105.26 STSESTAALGCLVK P01861
В этом сценарии я хотел бы получить счетчик 8 и, если возможно, чтобы строки, которые я отметил стрелками (для примера), были выделены. Обратите внимание, что если и третье, и четвертое поля идентичны (т.е. если разница между строками находится в другом месте таблицы), этонетряд интересов.
Я обычно не работаю с Excel/OOCalc, поэтому чувствую себя немного не в своей тарелке, работая с такими таблицами. Я наткнулся на несколько how-tos/форумоводин из которыхсодержит предложение использовать COUNTIFS
(например =COUNTIFS(C2:C114, "YES", F2:F114, "> 0")
) или эквивалент OOCalc с SUMPRODUCT
(например=SUMPRODUCT(C2:C114="YES" ; F2:F114>0)
Проблема с этим подходом в том, что он сопоставляет содержимое ячейки с предопределенным значением, например "YES"
. В моем случае я хотел бы сравнить содержимое ячейки с содержимым ячейки непосредственно выше/ниже. Можно ли настроить приведенные выше формулы так, чтобы они соответствовали моему случаю?
решение1
Один из способов отметить дубликаты (судя по всему, это именно то, что вам нужно) — поместить это в ячейку E1, а затем скопировать вниз.
=COUNTIF(C$1:C1;C1)
или
=COUNTIF(C$1:C1,C1)
*в зависимости от настроек вашей страны
Это делает то, что ставит 1 для каждого уникального или первого элемента и счетчик для всех последующих элементов. Вы можете "продвинуть" это с помощью:
=IF(COUNTIF(C$1:C1;C1)>1;"<--";"")
или
=IF(COUNTIF(C$1:C1,C1)>1,"<--","")
Тогда формула будет иметь вид:
=IF(COUNTIF(C$1:C1;C1)>1;IF(COUNTIF(D$1:D1;D1)=1;"<--";"");"")
О, кстати, в вашем примере вы показываете 3 стрелки, но данные между ячейками C4 и C5 разные! Поэтому моя формула показывает только 2 стрелки.
Автоформат
Если вы хотите автоматически отформатировать каждую отмеченную строку, отформатируйте ячейку A1 следующим образом:
Выберите: Формула
Введите: IF($E1="<--")
Создайте новую цветовую схему (например, MyYellow) и определите фон для этой схемы.
Теперь используйте , format painter
чтобы взять формат ячейки A1 и вставить его в ячейки A1–D8.