Как рассчитать различия на основе группировки значений в другом столбце в Excel

Question

У меня есть несколько наборов данных со столбцом группировки, содержащим имена корней растений (Rname) и столбец, указывающий день года, когда был обнаружен корень (причем каждый из этих дней указывает на сеанс измерения во время эксперимента - было до шести сеансов в целом). Вот образец данных:

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

Я не очень хорошо разбираюсь в Excel, и я не знаю, как кодировать макрос или делать что-то подобное. Мне нужно сделать это примерно для 70 отдельных таблиц, и было бы почти невозможно рассчитать это вручную для каждого корня. Есть ли способ сделать это автоматически с помощью вспомогательного столбца или что-то в этом роде?

Answer 1

Мое решение

Во-первых, сохраняйте сортировку столбцов A и B в порядке возрастания.

Затем, предполагая, что строка 1 является строкой заголовка, установите

C2 до =IF(NOT(A2=A1),ROW(),0) ;

D2 до =IF(AND(NOT(C2=C1),NOT(C2=0)),CONCATENATE("B",TEXT(C2,"0")),D1) ;

E2 до =IF(NOT(A3=A2),ROW(),0) ;

F2 - =IF(AND(NOT(E2=E1),NOT(E2=0)),CONCATENATE("B",TEXT(E2,"0")),F3) ;

и, наконец, G2 в =INDIRECT(F2)-INDIRECT(D2) .

Заполните столбцы с C по G до последней строки, и вы получите время жизни корневого Ax в ячейке Gx.

Вот вывод с вашими примерами данных:

Пояснения

Сортируйте первые два столбца, чтобы убедиться, что данные сгруппированы по корням, а последние данные за вычетом первых данных в каждой группе - это время жизни этого корня.

Используйте два дополнительных столбца (C & D), чтобы найти индекс ячейки первых данных, и еще два, чтобы найти индекс последних данных.

Хитрость заключается в том, что каждый раз, когда вы наблюдаете изменение значения столбца A, вы знаете, что группа данных закончилась и началась новая.

После заполнения столбцов D и F индексами ячеек используйте функцию INDIRECT, чтобы получить соответствующее значение и вычесть их.

Ссылка

Функция ТЕКСТ

НЕПОСРЕДСТВЕННАЯ функция

Answer 2

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

=IF(A2<>A3,MAX(IF(A:A=A2,B:B,FALSE))-MIN(IF(A:A=A2,B:B,FALSE)),"")

Это формула массива, которая должна вводиться в C2 с помощью сочетания клавиш CTRL+Shift+Enter, а не просто ввода. После того, как это введено, нажмите на C2 и заполните до последней строки ваших данных. Таблица ниже показывает результаты:

Как это работает: Внутренние выражения IF(A:A=A2,B:B,FALSE) возвращают массив значений в столбце B, где столбец A = An (конкретное имя R в текущей строке) и FALSE повсюду. Таким образом, дни года изолированы для каждого имени R, какими бы они ни были и сколько бы их ни было. Затем жизнь рассчитывается путем вычитания минимума из максимума этого массива.

Внешний IF() помещает вычисление Life в конец группы R Names и остается пустым везде.

Надеюсь, что это помогает и удачи.

Answer 3

@Dunius, я хотел бы предложить вам немедленное решение, как вычитание группы. И это работает правильно, используя ваши данные.

Поскольку для каждого набора кодов у вас есть самое низкое и самое высокое значение дня в году.

Так что мое решение ,

= ЕСЛИ ($ A201 <> $ А200, (МАКС (В $ 200:B200)-MIN (В $ 200:B200)), "")

Перетащите вниз формулу.

И дайте мне немного времени, чтобы создать код VBA для той же работы.

Примечание: формула в ячейке C200. Пожалуйста, измените диапазон данных, как вам нужно.

Answer 4

@Dunois, я хотел бы предложить два решения. Первый связан с предыдущим постом, а второй импровизирован.

Решение 1:

Что касается предыдущего поста, ваш вопрос был, будет ли Формула работать с данными переменной длины или нет?

Да, это будет работать. Проверьте снимок экрана Root3 имеет 7 строк, а Формула просто перетаскивается на длину, то есть

= ЕСЛИ ($ A201 <> $ А200, (МАКС (В $ 200:B200)-MIN (В $ 200:B200)), "")

Примечание: диапазон данных от A200 до C218(исключая строку заголовка). Ячейка формулы C200. Настройте ярость данных, как вам нужно.

Решение 2:

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

= IF(F200 = "", MAX(G200:INDEX(G $ 199:G199, MATCH(2,1/(G $ 199:$ G199 = ""), 1)))- MIN(G200:INDEX(G $ 199:G199, MATCH(2,1/(G $ 199:$ G199 = ""), 1))), "")

Примечание: диапазон данных от F200 до H218(исключая строку заголовка). Формула находится в ячейке H200.

Кроме того, я специально удалился в заголовок из G199.

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

Настройте ярость данных, как вам нужно.