Давайте посмотрим правде в глаза. Независимо от того, чем мы занимаемся, рано или поздно нам придется иметь дело с повторяющимися задачами, такими как обновление ежедневного отчета в Excel.
Python идеально подходит для решения задач автоматизации. Но если вы работаете компании, которая не использует Python, вам будет сложно автоматизировать рабочие задачи с помощью этого языка. Но не волнуйтесь: даже в этом случае вы все равно сможете использовать свои навыки питониста.
Для автоматизации отчетов в Excel вам не придется убеждать своего начальника перейти на Python! Можно просто использовать модуль Python openpyxl, чтобы сообщить Excel, что вы хотите работать через Python. При этом процесс создания отчетов получится автоматизировать, что значительно упростит вашу жизнь.
Набор данных
Прежде чем писать какой-либо код, внимательно ознакомьтесь с файлом на Google Drive. Этот файл будет использоваться как входные данные для создания следующего отчета на Python:
Excel 365 | Делаем таблицу учета личных финансов за 5 минут
Теперь давайте сделаем этот отчет и автоматизируем его составление с помощью Python!
Создание сводной таблицы с помощью pandas
Импорт библиотек
Теперь, когда вы скачали файл Excel, давайте импортируем библиотеки, которые нам понадобятся.
import pandas as pd import openpyxl from openpyxl import load_workbook from openpyxl.styles import Font from openpyxl.chart import BarChart, Reference import string
Чтобы прочитать файл Excel, создать сводную таблицу и экспортировать ее в Excel, мы будем использовать Pandas. Затем мы воспользуемся библиотекой openpyxl для написания формул Excel, создания диаграмм и форматирования электронной таблицы с помощью Python. Наконец, мы создадим функцию на Python для автоматизации всего этого процесса.
Примечание. Если у вас не установлены эти библиотеки в Python, вы можете легко установить их, выполнив pip install pandas и pip install openpyxl в командной строке.
Чтение файла Excel
Прежде чем читать Excel-файл, убедитесь, что он находится там же, где и ваш файл со скриптом на Python. Затем можно прочитать файл Excel с помощью pd.read_excel() , как показано в следующем коде:
В файле много столбцов, но для нашего отчета мы будем использовать только столбцы Gender , Product line и Total . Чтобы показать вам, как они выглядят, я выбрал их с помощью двойных скобок. Если мы выведем это в Jupyter Notebooks, увидим следующий фрейм данных, похожий на таблицу Excel:
Создание сводной таблицы
Теперь мы можем легко создать сводную таблицу из ранее созданного фрейма данных excel_file . Для этого нам просто нужно использовать метод .pivot_table() .
Предположим, мы хотим создать сводную таблицу, которая показывает, сколько в целом потратили на разные продуктовые линейки мужчины и женщины. Для этого мы пишем следующий код:
Как вести учет ДОХОДОВ и РАСХОДОВ в EXCEL? Как вести учет в EXCEL
report_table = excel_file.pivot_table(index=’Gender’, columns=’Product line’, values=’Total’, aggfunc=’sum’).round(0)
Таблица report_table должна выглядеть примерно так:
Экспорт сводной таблицы в файл Excel
Создание расчетных программ в Excel
1. Создаем сам алгорим вычисления и необходимые формулы в Excel
2. Проверяем работу программы — т.е. выполняем тестирование программы в Exсel
3. Подбираем картинку для фона — редактируем ее в Photoshop
4. Вставляем картинку фона в Excel
5. Тестируем и подправляем полученную программу — если в этом есть необходимость
1. Создаем сам алгорим вычисления и необходимые формулы в Exсel
1.1 Создадим программу для вычисления необходимого количества штучных материалов — для покрытия кровли
Исходные данные:
— обычно известно размер здания — по крайней мере, его можно измерить
— форма кровли выбирается исходя из визуальных предпочтений
— материал кровли выбирается исходя из имеющийся информации и толщины кошелька
— выбирается окончательный вид кровли — односкатная, двухскатная, четырехскатная и т.д.-
в любом случае кровля состоит из участков имеющих форму: треугольник, квадрат, параллелограмм
— количество материала необходимое для покрытия отдельного участка кровли зависит от:
размер отдельного участка в плане здания;
геометрический вид участка кровли;
уклон кровли;
размер штучного материала;
площадь эффективного перекрытия единицы штучного материала;
— суммарная площадь всех участков кровли и общая потребность в материалах верхнего покрытия с учетом отходов.
1.2 Предположим размер здания в плане 12 х 9 м (исходя из модуля 3 м)
1.3 Форма кровли — четырехскатная (вальмовая) — т.е. все четыре ската имеют наклон щипцы расположены в 4,5 м от торцов здания, карнизные свесы 250 мм, выход крайнего листа за лобовую доску 150мм, кровля без мансардная, высота от конька до чердачного перекрытия 4,5 м
1.4 Материал кровли — волнистые асбестоцементные листы — размеров 1750 х 1125, площадь эффективного перекрытия 1,5 м2
смежное перекрытие по длине листа 250 мм (сверху и по низу листа) и боковое перекрытие 125 мм ( с двух сторон)
1.5 Уклон скатов кровли определим расчетно — исходя из проекции скатов на чердачное перекрытие
Для того чтобы немного усложнить задачу — предположим что конек кровли смещен в сторону главного фасада —
то есть находится на расстоянии 3+6 (если смотреть в плане)
1.6 Создаем формулы:
A — ширина здания в плане
L — длина здания в плане
H — заданная высота кровли
C — длина ската
K — величина карнизных свесов
S — площадь скатов здания
B1 — расстояние от стены до конька по скату 1 (проекция)
B2 — расстояние от стены до конька по скату 2 (проекция)
F1 — расстояние от стены до конька по боковому скату 1 (проекция)
F2 — расстояние от стены до конька по боковому скату 2 (проекция)
1.7 Выводим все это на листе — вводим формулы.
2. Проверяем все работает
3. Через поиск Яндекса находим нужную по теме картинку — вставляем ее в Photoshop на новый слой — прозрачность слоя задаем от 10 до 20%
сохраняем картинку на жесткий диск компьютера.
4. В Excel, через «Разметка страницы» -> «Вставить фон» = картинку вставляем на выбранный лист с программой
5. Проверяем и при необходимости правим созданную программу.
6. По образу и подобию — есть возможность создавать не сложные программы для повседневных вычислений
7. Есть возможность немного усложнить программу — тем самым расширив функционал. Делается это через создание полей с выпадающими списками — в них будет возможность выбрать заранее заданные значения. И еще как вариант — можно задать выбор данных, через применение функции «ЕСЛИ»
8. Программу лучше сохранить в двух вариантах — один «только для чтения» и второй «рабочий вариант» — так больше вероятность, что вы ее
не измените до не рабочего состояния, после первого же запуска. Как сделать только для чтения? В проводнике на файле программы — кликаем правой кнопкой мыши и в контекстном меню выбираем «Свойства». В атрибутах файла ставим галочку в поле «Только чтение»
и нажимаем «Применить». Работать лучше на копии — но там глочку только чтение необходимо снимать.
9. Для работы — необходимо подставить свои данные в не выделенные поля и получить результат.
Сама программа — находится здесь.
Источник: pc.krognet.com
Формулы в Excel
Excel необходим в случаях, когда вам нужно упорядочить, обработать и сохранить много информации. Он поможет автоматизировать вычисления, делает их проще и надежнее. Формулы в Excel позволяют проводить сколь угодно сложные вычисления и получать результаты моментально.
Как написать формулу в Excel
Прежде чем учиться этому, следует понять несколько базовых принципов.
- Каждая начинается со знака «=».
- Участвовать в вычислениях могут значения из ячеек и функции.
- В качестве привычных нам математических знаков операций используются операторы.
- При вставке записи в ячейке по умолчанию отражается результат вычислений.
- Посмотреть конструкцию можно в строке над таблицей.
Каждая ячейка в Excel является неделимой единицей с собственным идентификатором (адрес), который обозначается буквой (номер столбца) и цифрой (номер строки). Отображается адрес в поле над таблицей.
Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:
- Установите указатель на ячейку, где будут производиться вычисления.
- Нажмите на клавиатуре знак «=».
- Мышкой щелкните по первой ячейке, которая участвует в вычислениях.
- Поставьте знак оператора, например «*».
- Щелкните по второй ячейке.
- Нажмите ENTER.
- Переведите указатель на ячейку с вычислениями, и вы увидите, как выглядит результат ваших действий.
