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

Хорошо, один для Gnumeric гуру там. Я полагаю, что этот вопрос в порядке, поскольку с формулами Excel можно сделать большое количество запросов, несмотря на то, что я не уверен, что это следует рассматривать как программирование как таковое.

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

Date         TimesMyDogHasBittenMe   Cumulative
----------   ---------------------   ----------
2016-01-01                       4            4
2016-01-02                       1            5
:
2016-01-31                       7           92
2016-02-01                       3           95
:

На самом деле, если бы вы знали мою собаку, вы бы знали, что она не способна что-либо кусать , но мне пришлось быстро придумать какой-нибудь счетчик.

На другом листе должна быть итоговая накопленная стоимость за каждый месяц:

Month   MonthlyBites
-----   ------------
Jan               92
:

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

  1. Если для данного месяца есть только частичные данные, мне нужен последний введенный день для этого месяца (последующие будут пустыми). Например, по состоянию на сегодня (7 февраля) последний заполненный ряд в первом рабочем листе - 6 февраля, поскольку собака не завершила свое (мифическое) агрессивное поведение на сегодняшний день.

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

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

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

Вот что мне нужно:

=lastif("a2:c999","c","c!= '' and month('a') = 1")

где:

  • первый аргумент - область поиска;
  • второй столбец для извлечения; а также
  • третье условие (непустое и месяц январь).
Источник

1 ответ

Решение

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

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

Month    Month#    LatestDate    Row    Cumulative
-----    ------    ----------    ---    ----------
Jan           1       31/1/16     36            92
Feb           2       29/2/16     57            95
Mar           3       31/3/16     57

Номер месяца представлял собой простое увеличивающееся значение, но при необходимости вы могли бы рассчитать его по названию месяца - я выбрал самый простой способ.

Самой последней датой месяца была формула:

=date(year(detail!$A$2),Bn+1,1)-1

где detail!$A$2 была первой ячейкой даты в подробном листе (использовалась только для получения текущего года - у меня есть отдельная электронная таблица для каждого года). n в этом случае строка приведенных выше данных. Эта формула получает первый день следующего месяца и вычитает один, чтобы получить последний день желаемого месяца.

В строке используется функция поиска по диапазону подробных данных, содержащему даты:

=match(Cn,detail!$A$2:$A$999,1)

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

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

следовательно =evaluate("A3") эквивалентно =A3, Не очень полезно в данном конкретном случае, но жизненно важно, если мы хотим динамически построить адрес ячейки, а затем ссылаться на содержимое этой ячейки. Поэтому выражение для Cumulative ячейки:

=if(month(indirect(concatenate("detail!a",fixed(Dn,0,TRUE))))=Bn,
    indirect(concatenate("detail!c",fixed(Dn,0,TRUE))),
    "")

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

В этом случае для ячейки устанавливается пустая строка.

В противном случае мы извлекаем соответствующий столбец из данной строки сведений и используем его в сводной таблице.

Вот и все, не самый красивый код в мире, но, безусловно, то, что работает (втом числе не требует ручного пересчета).

Источник
Другие вопросы по тегам