Поиск минимальных / максимальных значений в столбце, где ячейка соответствует чему-либо

Question

У меня есть электронная таблица вроде этого:

  Date      Project#
2012-01-01    130
2012-01-02    153
2012-01-03    153
2012-01-04    130
2012-01-05    130
2012-01-06    130

И я хочу получить минимальные / максимальные даты для тех строк, где Project # соответствует переменной, чтобы получить это:

Projects    Start date    End date
130         2012-01-01    2012-01-06
153         2012-01-02    2012-01-03

Я подумал, что смогу сделать это через VLOOKUP и поиск в обоих направлениях, но я не могу заставить его работать должным образом. Все работает нормально, если номера проектов сгруппированы вместе, но если они чередуются, как в примере, это больше не работает. Он возвращается только из самой верхней группировки, поэтому вместо даты завершения проекта 130 я бы назвал 2012-01-01.

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

Спасибо!

Answer 1

Предисловие: я предполагаю, что вы знакомы с кальциевым способом ссылки на ячейки, который представляет собой ColumnLetterRowNumber а диапазоны выражаются следующим образом: A10:C12 означает все ячейки между A10 и C12, поэтому 3 строки и три столбца

Хорошо, я предполагаю, что ваши данные в A1:B100 (или сколько у вас есть строк).

Теперь в D1 введите Project #, в E1 введите дату начала, а в F1 введите дату окончания, а затем перечислите все номера проекта (вручную) следующим образом:

Date      Project#              Project#   Start date   End date
2012-01-01    130                   130
2012-01-02    153               Project#   Start date   End date
2012-01-03    153                   153
2012-01-04    130
2012-01-05    130
2012-01-06    130

Теперь формула для E2 равна =DMIN(A1:B100,1,D1:D2) а для F2 это =DMAX(A1:B100,1,D1:D2). Затем скопируйте ячейки E2:F2 в другие строки.

К сожалению, именно так работают эти операции.

Answer 2

Я бы использовал Data Pilot для создания сводной таблицы.

1. Данные меню
2. Выберите пилот данных
3. Нажмите Пуск
4. Нажмите ОК
5. Перетащите проекты в поля строк
6. Перетащите Дата в поля данных
7. Дважды нажмите на дату, которую вы только что добавили
8. Выберите функцию Макс
9. Нажмите ОК

Это то, что вы ищете?

Answer 3

Я знаю, что посмотрел на это в сентябре, но сегодня я по-новому взглянул на это и обнаружил новую морщинку.  Он основан на ответе Стефана, но он решает проблему «критериев» базы данных, требующих двух строк, имеющих два столбца из них и чередующихся между ними:

   

Вот формулы:

 

Пожалуйста, дайте мне знать, если это не ясно.

Answer 4

Скотт, спасибо за твою изысканность - это действительно помогло. Чтобы упростить настройку электронной таблицы, я предпринял следующие шаги:

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

2 - в столбце рядом с дублированными данными (C в вашем примере) поместите 1 в верхнюю ячейку и затем = IF (ISODD (ROW (A2)); C1+1; C1) скопировать полностью в нижнюю часть данные (шаг 1 позволяет легко найти дно)

3 - в следующем столбце вставьте = IF(ISODD(ROW(A1)); "Project"; OFFSET($ B $ 1; C1; 0)) и снова скопируйте в конец дублированных данных - при этом появятся чередующиеся имена полей и ценности, которые мы хотим опросить

4 - максимум затем определяется с помощью = DMAX($ A $ 1:$ B $ 3437; "Livedate"; OFFSET(D $ 1; ROW($ A1)* 2-2; 0; 2; 1)), что дает максимум в каждом ряду, как в вашем примере.

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