Создайте формулу, в которой ссылки на конкретные ячейки изменяются в каждой n-й строке в Excel.

Question

У меня есть большой набор данных, он структурирован таким образом, что всегда 16 рядов принадлежат одной группе. (например: A1, A2, A3, A4, A5, A6,… A16 -> Group1; A17, A18, A19, A20,…, A32 -> Group2..etc.)

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

Поэтому у меня есть формула, которую я придумал, которая дает мне необходимую информацию, и пока она не работает! Формула, которую я использую в Excel, следующая:

=SUM(MIN(A1,A$1),MIN(A1,A$2),MIN(A1,A$3),MIN(A1,A$4),MIN(A1,A$5),MIN(A1,A$6),MIN(A1,A$7),MIN(A1,A$8),MIN(A1,A$9),MIN(A1,A$10),MIN(A1,A$11),MIN(A1,A$12),MIN(A1,A$13),MIN(A1,A$14),MIN(A1,A$15),MIN(A1,A$16))-A1

Но теперь проблема в том, что эта формула работает только для первых 16 строк (A1 - A16). Можно ли менять формулу каждые 16. строки?

Или как я могу сформулировать формулу таким образом, чтобы, достигнув строки A17, она ссылалась на следующие 16 ячеек (от A17 до A32), а не на предыдущие 16 ячеек (от A1 до A16)?

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

Answer 1

Прежде всего, я упростил вашу исходную формулу, используя формулу массива (нужно вводить, нажав Ctrl + Shift + Enter

=SUM(IF(A1<$A$1:$A$16,A1,$A$1:$A$16))-A1

Тогда это должно быть динамичным

Эта формула возвращает 1 для первых 16 строк листа

=(INT((ROW()-1)/16)*16+1)

Эта формула возвращает 16 для первых 16 строк листа

=(INT((ROW()-1)/16)+1)*16

Затем они были введены в индексную функцию для возврата ячеек A1 (ниже) и A16

INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1))

Все эти формулы были объединены, чтобы создать окончательную формулу, которую все еще нужно вводить, используя ctrl+shift+enter.

Предполагается, что ваш общий диапазон составляет A1:A32, который необходимо расширить для вашего набора данных. Кроме того, если ваши данные не начинаются со строки 1, вам необходимо настроить формулы Int.

=SUM(IF(A1<INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1)):INDEX($A$1:$A$32,((INT((ROW()-1)/16)+1)*16)),A1,INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1)):INDEX($A$1:$A$32,((INT((ROW()-1)/16)+1)*16))))-A1

Вот картина работы первой формулы массива. Остальная часть ответа просто делает так, чтобы он менялся каждые 16 ячеек.