Блог

Я ищу какую-нибудь помощь с формулой. На моем листе «данные» у меня есть данные в формате ниже:

 А В С Д Е Ф Г Ч И Й
1 UID RecordType HCode AdmittedDate Имя Фамилия DOB Пол STDate RDate 
2 87962 STAsses STIV1 01/01/2012 Марк Джонс 13.07.1978 Мужчина 09/12/2012 
3 89658 Передача GLSI2 01/01/2012 Элисон Эйткен 20/12/1956 Женщина 08/07/2013 
4 84563 Разряд JHOP1 01/01/2012 Дэвид Бекхэм 08.09.1987 Мужчина 07.08.2013
5 89654 STAsses STGE1 01/01/2012 Эндрю Макбет 27/09/1976 Мужчина 08/07/2012 
6 89867 Вид ТРАНСФЕРА1 01/01/2012 Джордж Диас 05.08.1989 Мужчина 07.08.2013
7 87962 Трансфер СТИВ1 01/01/2012 Марк Джонс 13/07/1978 Мужчина 04/03/2013 
8 89654 Передача STGE1 01/01/2012 Эндрю Макбет 27/09/1976 Мужчина 12/08/2012

На моем листе «отчет» у меня настроена следующая таблица:

 Б С Д Е Ф
4 Тип записи HospCode июль-12 август-12 сентябрь-12
5 Оценок STGE1
6 переводов
7 разрядов
8

Я хотел бы сделать 2 вещи.

1. В D5, E5, F5 требуется формула для возврата количества записей ‘STAssess’ для HCode ‘STGE1’ из таблицы данных, которые были завершены в месяце, указанном в D4 листа ‘report’ (12 июля). Даты для этих записей указаны в столбце I таблицы данных — «Стандартная дата».

Я пробовал использовать SUMPRODUCT, который работает до некоторой степени, но у меня возникают трудности с возвратом записей, завершенных только в июле. Мне нужно, чтобы формула была динамической, поскольку она возвращает результаты только за месяц, введенный в D4 (поэтому диапазон дат, встроенный в формулу, не выполняет эту работу).

1. В D5, E5, F5 мне нужно вернуть количество записей из таблицы «данные», которые имеют «Перенос» в столбце B (RecordType), «STGE1» в столбце C (HKode) и дату в столбце J (RDate), которая приходится на месяц, указанный в D4 листа «отчет» (12 июля).).

У кого-нибудь есть идеи? Таблица данных на самом деле будет внешним файлом .csv, которым нельзя манипулировать. Я не против использовать другие скрытые листы для выполнения любой сортировки, которую необходимо выполнить, при условии, что это можно настроить так, чтобы при вводе данных не требовалось никакой дополнительной работы.

Спасибо!

Для обоих ваших вопросов вы можете использовать COUNTIFS . Это проще для понимания (для меня) и совместимо с Excel 2007 и новее.

Первый вопрос:

 =COUNTIFS(DATA!B:B,"STAsses",DATA!C:C,$B5,DATA!I:I,">="amp;D$4,DATA!I:I,"<"amp;DATE(YEAR(D4),MONTH(D4) 1,1))
  

Второй вопрос:

 =COUNTIFS(DATA!B:B,"Transfer",DATA!C:C,$B5,DATA!J:J,">="amp;D$4,DATA!J:J,"<"amp;DATE(YEAR(D4),MONTH(D4) 1,1))
  

И эти формулы можно перетащить в нужный вам диапазон.

Вот пример формулы, которую вы могли бы использовать для ячейки D5, указывает ли она вам правильное направление?:

 {=SUM(IF($B$2:$B$8=$B14,IF($C$2:$C$8=$A14,IF($D$2:$D$8=C$13,1,0),0),0))}
  

Это функция массива, поэтому вы должны ввести ее без {} на каждом конце и использовать Ctrl Shift Enter для ввода формулы.

Если ваша сводная таблица выглядит как

 HospCode    RecordType  12-Jul  12-Aug  12-Sep
STGE1       STAsses     1       0       0
STGE1       Transfer    0       1       0
STGE1       Discharges  0       0       0
  

И ваши данные находятся в таблице с именем Table1, затем в D5

 =SUMPRODUCT((Table1[HCode]=$B5)*(Table1[RecordType]=$C5)*(MONTH(Table1[STDate])=MONTH(D$4)))
  

Я не уверен, что вы подразумеваете под «вернуть», но предположим, вы имеете в виду, что хотите показать это, когда RecordType = столбец C и когда RecordType = «Transfer» и RDate находятся в нужном месяце. Затем

 =SUMPRODUCT((Table1[HCode]=$B5)*(Table1[RecordType]=$C5)*(MONTH(Table1[STDate])=MONTH(D$4))) SUMPRODUCT((Table1[HCode]=$B5)*(Table1[RecordType]="Transfer")*(MONTH(Table1[RDate])=MONTH(D$4)))