☰

отображать еженедельные сводки по введенным пользователем датам в Excel 2010

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
отображать еженедельные сводки по введенным пользователем датам в Excel 2010

У меня есть проект, в котором я должен создать автоматизированную электронную таблицу. На первом листе человек вводит данные вручную. Например, предположим, что человек вводит сумму X того, что он получил на дату XX/XX/XX (используйте текст в столбцах Excel, затем разделите, затем нажмите клавишу табуляции и пробел и закончите).

       11/19/2015   11/20/2015  11/25/2015  11/30/15    1/2/2016
Apples    4             5           2           7           1
Bananas   6             4           5           7           3
Oranges   3             0           4           0           0

Теперь, это все введенные пользователем данные. Из этих введенных пользователем данных мне нужен второй лист для отображения еженедельной даты (дата, которая является субботой, которая определяет неделю) и суммы всех полученных вещей за эту неделю. Вот что мне нужно, чтобы Лист 2 отобразил из введенных пользователем данных:

        11/21/2015  11/28/2015  1/2/2016
Total      22           11         4
Apples      9            2         1
Bananas    10            5         3
Oranges     3            4         0

Если вы заметили, 11/19/2015 и 11/20/2015 попадают в неделю 11/21/2015, поэтому 11/21/2015 отображается только в одной ячейке, а все данные за эту неделю суммируются. Введенные пользователем даты могут быть как угодно далеки друг от друга, поэтому я включил 1/2/2016.

Теперь, вычисление формулы для сумм должно быть более простым, но проблема, с которой я сталкиваюсь, заключается в отображении еженедельной даты один раз. Одна из вещей, которая может произойти, — это пользователь ошибочно вводит одну и ту же дату дважды. Я проверил это с помощью формулы:

=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",__________)

Первая ячейка еженедельной даты проста, поскольку она отвечает на первую введенную пользователем дату. Она просто:

=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",B1-weekday(B1)+7)

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

ОБНОВЛЯТЬ После поиска в Google моя проблема в основном та же, что и поиск k-го наибольшего числа, чем указанное число. К счастью, было много веб-сайтов, которые дали мне полезную информацию. Итоговая формула, которую я придумал для отображения еженедельных дат, следующих за первой еженедельной датой, выглядит так:

=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",IFERROR(LARGE(1:1,COUNTIF(1:1,">"&B17))-WEEKDAY(LARGE(1:1,COUNTIF(1:1,">"&B17)))+7,""))

Эта формула также позволяет ячейкам, следующим за последней еженедельной датой, отображать "", что можно изменить для отображения значения #N/A, если это необходимо. Теперь перейдем к правильному суммированию.

решение1

Я интерпретирую ваш вопрос так: «Мне нужно значение каждой седьмой ячейки в горизонтальном массиве, начиная с третьей ячейки».

Если это так, то вы можете использовать следующую формулу в ячейке A2 и заполнить строку 2:

=INDEX($A1:$J1,1,7*(B1-$A1)-4)

Убедитесь, что вы:
A: Проверьте, что ваши входные данные распознаются Excel как даты, а не как текст. Возможно, вам придется изменить введенные даты на ваш локальный формат (например, дд/мм/гг), чтобы это заработало.
B: Расширьте диапазон $A1:$J1 до полной ширины вашего диапазона данных.

Связанный контент