@AnastasiaPinchuk представила (https://habr.com/ru/articles/891074) замечательный туториал по решению типичной задачи: в «плоской» таблице заданы интервалы неких событий. Основываясь на указанных интервалах необходимо сформировать отчет значений на конкретные даты.
Например, у упомянутого автора, из дат нахождения на должности сотрудников (Рис .1) необходимо сформировать отчет должностей сотрудников на конкретные даты
@AnastasiaPinchuk подробно расписывает процесс получения необходимого результата через использование формул. Однако, поскольку задачи такого класса встречаются очень часто, в рамках темы «разработка для Office 365» ниже показываются возможности, которые дает современный Excel пользователю для решения аналогичных задач.
1. Copilot
Для написания громоздких формул сейчас логичнее использовать мощь Copilot. При использовании в Office 365 Copilot не требует VPN и отлично работает на территории РФ. Единственное условие- сохранить файл в OneDrive под тем же логином, с которым зарегистрированы в Office 365.
Например, по приведенной задаче элементарный запрос:
«Заполни колонку "Должность" в таблице "Отчет" на основании "Дата Отчета" и данных из таблицы "Реестр". "Дата отчета" должна быть в диапазоне от "Действует с" по "Действует до"»
Выдает необходимую формулу:
=ЕСЛИОШИБКА(ИНДЕКС(Реестр[должность];ПОИСКПОЗ(1;([@ФИО]=Реестр[ФИО])*([@[дата отчета]]>=Реестр[действует с])*([@[дата отчета]]<=Реестр[[Действует до ]]);0));"")
Задача решена в две минуты и акцент пользователя переводится с написания правильной формулы и подсчета кавычек на «умную» работу - формирование выводов и предложений по данным.
2. Python в Excel
В новых версиях Excel 365 реализована возможность работы с Python, которая дает огромные возможности пользователю. Вот, например, решение той же задачи с использованием Python:
Решение получается в результате следующих действий.
Создаем дата фрейм- в ячейки записываем формулы (пункт меню «Формулы-Вставить Python»):
#Создаем Дата фрейм (DataFrame) таблицу Report
Report_df=xl("Report[[#Headers];[#Data]]", headers=True)
#Загружаем данные с таблицы Reestr
Reestr_df = xl("Reestr[[#Заголовки];[#Данные]]", headers=True)
Для использования DataFrame импортировать модуль Pandas не нужно. В другую ячейку (в примере- A6) записываем формулу:
#Добавляем 'Должность' в таблицу 'Report'
Report_df['Должность'] = Report_df.apply(lambda row: Reestr_df[(Reestr_df['ФИО'] == row['ФИО']) & (Reestr_df['действует с'] <= row['дата отчета']) & (Reestr_df['Действует до '] >= row['дата отчета'])]['должность'].values[0], axis=1)
Report_df
Код удобно редактируется во вкладке Python Editor (Меню «Формулы- Редактор Python»):
В необходимом месте (на рис.2- в ячейке A9 ) пишем формулу и выводим результат:
=ЕСЛИОШИБКА(A6.arrayPreview;A6.Python_str).
Требуемый отчет получен (таблица на Рис.2):
3. Python и Copilot
Самое замечательное, Copilot позволяет использовать Python в Excel без глубоких знаний языка. Однако важные ограничения- для работы с Python запросы в Copilot нужно делать на английском. Язык в Excel переключаем на английский (Параметры/Язык). Колонки таблицы желательно называть также на английском. После получения результата, в формулах названия можно заменить на русские.
Результат, аналогичный приведенному выше получается по запросу в Copilot:
«Implement it in Excel using Python. The 'Position' column in the 'Report' table will be created by merging the 'Report' and 'Reestr' tables based on the 'FIO' and the 'Report Date' falling within the 'Valid from' and 'Valid until' range. This will ensure that the correct position is assigned to each report entry.»
К сожалению, запросы для работы с Python на русском не отрабатываются, Copilot отвечает – «please try your prompt again in English.» Однако элементарный запрос типа:
«Реализуй в Excel с использованием языка Python. Создай колонку "Должность" в таблице "Отчет". " Должность" возьми из таблицы "Reestr". "Дата отчета" должна быть в диапазоне от "Действительна с" до "Действительна до"» просто переведенный в Яндекс.Переводчик отлично принимается Copilot и дает искомый результат. Copilot самостоятельно переформулирует его в более структурированный запрос, создаст в Excel листы, вставит необходимый код там где нужно и напишет комментарии. Полученный код Python близок к тому, что был приведен в п.2 и формирует верные ответы.
Использование Python в Excel для решения подобных часто встречающихся задач дает пользователю фактически неограниченные возможности. Например, для связывания данных по нечетким совпадениям адресов (пример @empenoso в https://habr.com/ru/articles/873242/), использования иных многочисленных функций.
Copilot позволяет значительно ускорить использование Python в Excel и существенно уменьшить «цену входа». God created programmers. Copilot made them equal.
Автор: Pride09
