Excel: найти количество записей, соответствующих нескольким критериям, и использовать время с момента создания первой записи

Question

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

   A         B            C            D            E          F        G           H           I          J
1 UID   RecordType      HCode     AdmittedDate   Forename   Surname    ReviewDate     Sex        STDate      RDate
2 87962 STAsses         STIV1     01/07/2012     Mark       Jones                     Male   09/12/2012 
3 89658 Transfer        GLSI2     01/01/2012     Alison     Aitken                    Female                 08/07/2013 
4 87962 Discharge       STIV1                    Mark       Jones      20/07/2012     Male               08/07/2013
5 89654 STAsses         STGE1     01/01/2012     Andrew     Macbeth                   Male   08/07/2012  
6 89867 Transfer        KIND1     01/01/2012     George     Deas                      Male               08/07/2013 
7 87962 Transfer        STIV1                    Mark       Jones      14/07/2012     Male               04/03/2013 
8 89654 Transfer        STGE1     01/01/2012     Andrew     Macbeth                   Male               12/08/2012

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

      B             C             D            E            F
4 HospCode     RecordType     0-2 Weeks    2-4 Weeks    4-6 Weeks
5 STIV1        Transfers
6              Discharges
7              
8

На моем листе «отчета» я рассчитываю подсчитать количество «переносов» и «разгрузок» (как указано в столбце b таблицы «данных»), произошедших за промежутки времени в столбцах D, E, F и т.д.

Таким образом, в ячейке D5 я ищу подсчет количества «переводов» (как указано в столбце b таблицы «данных») для гостеприимного кода «STIV1», происходящих в течение 0–2 недель после соответствующих «STAsses». 'запись (которая также указана в столбце b). D6 будет таким же, только количество записей «разгрузки», происходящих в течение указанного периода времени.

Я не знаю, с чего начать, или если это возможно сделать. У каждого человека будет как минимум запись STAsses, но не всегда запись «Transfer» или «Discharge». «UID» одинаков для каждого человека, поэтому его можно использовать для сопоставления записей «STAsses» с записями «Transfer» и «Discharge».

Любые указатели будут высоко оценены.

Answer 1

Есть много способов сделать это, но ни один из них не будет простым. Одним из способов было бы создать комбинированный столбец даты, который бы суммировал (stdate, rdate). Похоже, что только одна из этих дат заполняется за один раз, поэтому сумма будет просто правильно объединять их в один столбец.

Затем вы можете добавить сводную таблицу сверху, поместив UID в строки, RecordType в столбцы и новый столбец комбинированной даты в значения (как SUM). Поскольку комбинированная дата отличается от комбинации UID/RecordType, фактические даты здесь не будут суммироваться, так что вы просто получите красивую таблицу с разными UID и соответствующими датами STAsses, Transfer и Discharge на одной строке.

Имея эту информацию, вы можете теперь вычесть дату STAsses из даты Transfer и Dischage и разделить на 7, чтобы получить недели, прошедшие между двумя периодами. Вы также можете выполнить vlookup, чтобы ввести HCode.

В конце этого нового набора данных вы можете создать новые столбцы для захвата разряда 0-2, разряда 0-2, разряда 2-4, переноса 2-4 и т.д. Вы можете использовать оператор if, чтобы заполнить их как If(dischargeweeks<2, 1, 0) , If(transferweeks<2, 1, 0) и т.д. (Обратите внимание, что данные в типе 0-2, 2-4 столбцы составлены, так как ваши выборочные данные выплескиваются в диапазоне сотен недель).

Теперь опустите эти новые данные (я знаю ... опорные точки опорных точек)... На этот раз введите hcode в строках, а 0-2 Discharge, 0-2 перевод и т.д. В значениях. Теперь он даст вам счет каждого из них для каждого отдельного HCode ... который действительно близок к тому, что вы хотите.

Наконец, я просто скопировал и вставил (и вы могли бы отсортировать потом), чтобы получить его в правильном формате ... Я имею в виду, что на данный момент, что немного скопировать и вставить, верно?

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

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