Иметь строку для каждого дня/часа и получить итог для каждого часа?

Вопрос или проблема

У меня есть электронная таблица, как показано здесь. Она содержит почасовые данные о ветре и солнечной энергии за 31 день.

Мне нужно общее количество для каждого часа как для ветра, так и для солнца. Другими словами, у меня получится:

Час	Производство ветровой энергии	Производство солнечной энергии
12:00 a.m.	60,153	-31
1:00 a.m.	59,123	-29

Итого 24 строки. Как я могу это сделать?

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

• Используя GROUPBY()

=GROUPBY(--SUBSTITUTE(TEXTBEFORE(TEXTAFTER(B2:B12," ",)," MST"),".",),C2:D12,SUM,,0)

• Если у вас нет доступа к GROUPBY(), то использовать функцию MAKEARRAY():

=LET(
     _T, --SUBSTITUTE(TEXTBEFORE(TEXTAFTER(B2:B12," ",)," MST"),".",),
     _U, UNIQUE(_T),
     _S, MAKEARRAY(ROWS(_U), 2, LAMBDA(r,c, 
         SUM(BYROW(_T, LAMBDA(b, AND(b=CHOOSEROWS(_U, r))))*
             CHOOSECOLS(C2:D12, c)))),
     HSTACK(_U,_S))

Пожалуйста, убедитесь, что формат ячеек для столбца времени соблюден как истинное время – h:mm AM/PM;@

Ответ или решение

Теория

Работа с большими объемами данных, особенно в табличных процессорах, таких как Microsoft Excel или Google Sheets, часто требует упорядочивания и агрегирования данных для построения отчетов и анализа. Представленная задача подразумевает необходимость собрать данные о выработке ветровой и солнечной энергии за каждый час в течение 31 дня и агрегировать их таким образом, чтобы получить общее значение для каждого часа. Это позволяет не только упростить анализ данных, но и предоставляет возможность выявить закономерности или тенденции в выработке энергии в зависимости от времени суток.

На практике задачи такого рода решаются с помощью функций обработки данных, таких как сводные таблицы и специализированные формулы. Хотя сводные таблицы являются мощным инструментом, в современных версиях Excel появились новые функции, упрощающие процесс агрегирования данных, такие как GROUPBY() и MAKEARRAY().

Пример

Представим, что у нас есть лист Excel с данными о выработке энергии. У каждого часа есть соответствующие значения выработки энергии ветра и солнца. Наша задача — суммировать значений для каждого часа дня, чтобы получить агрегацию по времени суток.

Рассмотрим, как это можно сделать с использованием современных функций:

GROUPBY(): Эта функция позволяет легко сгруппировать данные по уникальным значениям и применить агрегацию (например, сумму) к другой колонке данных.
```
=GROUPBY(--SUBSTITUTE(TEXTBEFORE(TEXTAFTER(B2:B12," ",)," MST"),".",),C2:D12,SUM,,0)
```

MAKEARRAY(): Если функции GROUPBY() нет в доступности, можно использовать функцию MAKEARRAY() в связке с LET() для выполнения подобной операции.

=LET(
 _T, --SUBSTITUTE(TEXTBEFORE(TEXTAFTER(B2:B12," ",)," MST"),".",),
 _U, UNIQUE(_T),
 _S, MAKEARRAY(ROWS(_U), 2, LAMBDA(r,c, 
     SUM(BYROW(_T, LAMBDA(b, AND(b=CHOOSEROWS(_U, r))))*
         CHOOSECOLS(C2:D12, c)))),
 HSTACK(_U,_S))

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

Применение

Для применения данных решений к вашей таблице выполните следующие шаги:

Подготовьте данные: Убедитесь, что данные о времени смещены в одном и том же формате (например, h:mm AM/PM). Для работы с текстовыми значениями времени важно, чтобы они имели одинаковую структуру в каждой ячейке.
Используйте формулы: Вставьте формулы в пустую ячейку рабочего листа. При этом не забудьте настроить диапазон, откуда берутся данные, чтобы формулы указывали на нужные ячейки.
Анализируйте результаты: Получите таблицу с агрегированными значениями, которая покажет вам суммарную выработку энергии для каждого часа. Это упростит восприятие информации и поможет выявить периоды пиковых нагрузок или низкой выработки.

Функции GROUPBY() и MAKEARRAY() значительно повышают эффективность автоматизации процессов агрегирования данных в Excel и упрощают построение отчетности. Правильно оформленные данные позволяют сэкономить время на их манипуляцию и создать более точные и информативные отчеты, которые легко идентифицируют ключевые показатели и тренды.

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