質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
openpyxl

openpyxlは、Excel2007以降のファイル(xlsx/xlsm/xltx/xltm)を読み書きするためのPythonライブラリです。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

pandas

Pandasは、PythonでRにおけるデータフレームに似た型を持たせることができるライブラリです。 行列計算の負担が大幅に軽減されるため、Rで行っていた集計作業をPythonでも比較的簡単に行えます。 データ構造を変更したりデータ分析したりするときにも便利です。

Q&A

解決済

2回答

3426閲覧

pythonを使いエクセルデータから担当毎、商品別に売上を算出して売上明細書を作りたい

kotaro_szk

総合スコア5

openpyxl

openpyxlは、Excel2007以降のファイル(xlsx/xlsm/xltx/xltm)を読み書きするためのPythonライブラリです。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

pandas

Pandasは、PythonでRにおけるデータフレームに似た型を持たせることができるライブラリです。 行列計算の負担が大幅に軽減されるため、Rで行っていた集計作業をPythonでも比較的簡単に行えます。 データ構造を変更したりデータ分析したりするときにも便利です。

0グッド

1クリップ

投稿2020/07/26 05:34

前提・実現したいこと

初めての質問です不備があったら申し訳ございません。
エクセルのデータベースから
担当毎の商品の売上をエクセルの明細書へ転記したいと考えています。

pandasを使い担当者ごとの商品別売上をgroupbyで
抽出するところまではできたのですが

できたデータを担当毎でエクセルファイルへ任意の場所へ(明細書)
順に転写していき担当者ごとのファイルを作りたいです。

###参考画像
######データベース
イメージ説明

######groupbyで抽出した結果
イメージ説明

######明細書の完成イメージ
イメージ説明

該当のソースコード

python

1!pip install openpyxl 2 3import pandas as pd 4import numpy as np 5import openpyxl 6import glob 7 8import_file = 'sample_auto02.xlsx' 9 10excel_sheetname01 = '実績管理表' 11 12df_actual = pd.read_excel(import_file, sheet_name = excel_sheetname01) 13 14df_actual.head() 15 16#4/1~6月末までの集計 17(df_actual['売上日']>="2020-04-01") & (df_actual['売上日']<"2020-07-01") 18 19 20#データフレームの角括弧の中に入れる 21df_actual[(df_actual['売上日']>="2020-04-01") & (df_actual['売上日']<"2020-07-01")] 22 23#変数に入れ直す 24df_actual = df_actual[(df_actual['売上日']>="2020-04-01") & (df_actual['売上日']<"2020-07-01")] 25 26#氏名に加えて商品分類ごとも 27df_actual[['氏名','数量','単価(円)','商品分類']].groupby(['氏名','商品分類']).sum() 28 29 30#こんなイメージでやってみましたがエラーばかり↓ 31 32# for i in (氏名を入れたい): 33# df_order_name = df_actual[df_actual['氏名'] == i] 34# df_order_name.to_excel(既存のEXCELのテンプレファイル(明細書)の任意のセルへ書き出したい) 35 36 37#欲を言えば 38#それらを氏名ごとにPDFで書き出しまで出来たらとても嬉しいです...。

試したこと

氏名を事前にunipueで取得してみたのですが
groupbyしたあとだとエラーになってしまいました。

補足情報(FW/ツールのバージョンなど)

jupyterLabを使用しています。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

aokikenichi

2020/07/26 06:23

ご質問内容が分かりません。 すでにフォーマットが決まっている書式のExcelにPythonの集計結果を転記ということでしょうか? ただ例示いただいた明細書は書式はありますが、担当者から単価の列までは自由なようですので、 Pythonの出力をCSV等に出してExcelで開き、ごそっと明細書に貼り付けるだけでは? 「氏名を事前にunipueで取得してみたのですが groupbyしたあとだとエラーになってしまいました。」 これと上記と何の関係があるのでしょうか? これも解決したいのであればエラーコードも記載ください。
kotaro_szk

2020/07/26 06:33

ご対応いただきありがとうございます。 説明不足で申し訳ございませんでした。 すでにフォーマットが決まっている書式のExcelにPythonの集計結果を転記ということでしょうか? >>はい、おっしゃる通りでございます。 それを担当者名毎にPDF書き出しをしたいと思っていますが やり方が分からず困っていて質問をしてみました。 ユニークを使った理由は担当者名をPDFのファイル名にしたいと思っていたので 抽出しておくほうがいいのかと思っていました。使わずに済むのであれば 無視していただいて構いません、ややこしくしてしまい申し訳ございませんでした。
tiitoi

2020/07/26 07:27 編集

入力データを作るのが大変なので、画像で添付されている5行程度でいいので、入力データをテキストで質問に転記していただくことはできますか?
kotaro_szk

2020/07/26 07:30

ご連絡ありがとうございます! このような形でよろしいでしょうか? 売上日 社員番号 氏名 支店 商品分類 商品名 単価(円) 数量 売上金額(円) 2020/1/4 a023 河野 利香 大阪 ボトムス ロングパンツ 7,000 8 56,000 2020/1/5 a003 石崎 和香菜 東京 ボトムス ジーンズ 6,000 10 60,000 2020/1/6 a003 石崎 和香菜 東京 ボトムス ロングパンツ 7,000 10 70,000 2020/1/7 a036 西尾 謙 横浜 ボトムス ロングパンツ 7,000 2 14,000 2020/1/9 a013 宮瀬 尚紀 福岡 トップス ニット 8,000 7 56,000 2020/1/10 a047 上瀬 由和 札幌 アウター ダウン 18,000 7 126,000 2020/1/11 a013 宮瀬 尚紀 福岡 トップス ニット 8,000 5 40,000 2020/1/11 a013 宮瀬 尚紀 福岡 ボトムス ハーフパンツ 3,000 9 27,000 2020/1/11 a047 上瀬 由和 札幌 ボトムス ロングパンツ 7,000 1 7,000 2020/1/16 a036 西尾 謙 横浜 ボトムス ハーフパンツ 3,000 5 15,000 2020/1/19 a003 石崎 和香菜 東京 トップス シャツ 4,000 1 4,000 2020/1/21 a003 石崎 和香菜 東京 アウター ダウン 18,000 1 18,000
tiitoi

2020/07/26 07:33

それで大丈夫です。ありがとうございます。
kotaro_szk

2020/07/26 07:35

こちらのほうこそ ありがとうございます><; お手数ですがよろしくお願い致します。
aokikenichi

2020/07/26 07:59

原文にpdfうんぬんは一切ありませんでした。 何が問題点で何をなさりたいのか整理して必要な情報を出しませんと正しい回答を得られる確率が低くなると思われます。
kotaro_szk

2020/07/26 08:05

返信ありがとうございます。 ご指摘、アドバイスいただき助かります。 次回、質問する際は気をつけたいと思います。 ご不便おかけして申し訳ございませんでした。
guest

回答2

0

できたデータを担当毎でエクセルファイルへ任意の場所へ(明細書)

順に転写していき担当者ごとのファイルを作りたいです。

最初にExcelを作る方法ですが、Excelテンプレートと一致するようにDataFrameを作り、to_excel()で位置を指定して書き出せば良いです。

python

1# テンプレートと一致するDataFrame: df_clerkを作る 2df_clerk = ( 3 df_actual[["氏名", "数量", "単価(円)", "商品分類", "売上金額(円)"]] 4 .groupby(["氏名", "商品分類"]) 5 .sum() 6) 7df_clerk["備考"] = "" # 明細書欄に備考があったので追加 8df_clerk = df_clerk[["数量", "単価(円)", "備考", "売上金額(円)"]] 9 10# テンプレートの読み込み 11template_book = openpyxl.load_workbook("template.xlsx") 12template_sheets = dict( 13 (sheet.title, sheet) for sheet in template_book.worksheets 14) 15 16# 担当者ごとにファイルを作成して書き出し 17for order_name, df_items in df_clerk.groupby(level=0): 18 output_filename = f"{order_name}.xlsx" 19 with pd.ExcelWriter(output_filename, engine="openpyxl") as writer: 20 writer.book = template_book 21 writer.sheets = template_sheets 22 df_items.to_excel( 23 writer, 24 sheet_name="明細書", 25 startrow=1, 26 startcol=0, 27 header=False, 28 index=True, 29 ) 30

次にPDFにする方法ですがOSがWindowsかつExcelがインストールされているのであればpywin32というパッケージを使えば比較的楽にできます。もしLinuxやmacOSであれば、Excelを経由しないでMarkdownやLaTeX等を使いpandocにより変換する方がよいでしょう。

参考URL:

投稿2020/07/26 09:03

yymmt

総合スコア1615

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kotaro_szk

2020/07/26 09:29

回答いただきありがとうございます! PDFの方法まで示していただき感謝です! 記載していただいたコードを自分なりに 噛み砕いて理解しながら進めてみたいともいます><! まる1日試行錯誤してましたが素直に質問してよかったです。 また機会がありましたらよろしくお願い致します。
guest

0

ベストアンサー

読み込んだ Excel ファイルを整形して、質問画像と同じ項目の Excel ファイルを出力するところまでできました。

列の幅や色などの見た目の体裁は openpyxl で出力されたファイルを読み込んで、適宜整えてください。

python

1from pathlib import Path 2 3import openpyxl as xl 4import pandas as pd 5from IPython.display import display 6 7################################## 8# 設定項目 9################################## 10# 抽出期間 11start, end = "2020-01-04", "2021-04-01" 12# 出力ディレクトリのパス 13output_dir = Path("output") 14 15# 保存ディレクトリを作成する。 16output_dir.mkdir(exist_ok=True) 17 18# Excel ファイルを読み込む。 19df = pd.read_excel("sample.xlsx", sheet_name="実績管理表") 20 21# 指定した期間を抽出する。 22df.query(f"'{start}' <= 売上日 <= '{end}'", inplace=True) 23 24# 氏名、商品分類でグループ化 25df_by = df.groupby(["氏名", "商品分類"]).sum() 26 27 28def save_excel(path, df): 29 writer = pd.ExcelWriter(path) 30 df.to_excel(writer, "実績管理表") 31 writer.save() 32 display(df) 33 34 35for name, df_by_name in df_by.groupby(level=0): 36 # 保存するパス 37 output_path = output_dir / f"{name}.xlsx" 38 print(f"Saving result to '{output_path}'") 39 40 # 列 備考 を追加 41 df_by_name.insert(0, "備考", "") 42 # 列を並び替える 43 df_by_name = df_by_name.reindex(columns=["数量", "単価(円)", "備考", "売上金額(円)"]) 44 # 列名を変更 45 df_by_name.rename(columns={"単価(円)": "単価", "売上金額(円)": "価格"}, inplace=True) 46 df_by_name.index.rename(["担当者名", "商品名"], inplace=True) 47 48 save_excel(output_path, df_by_name)
output ├── 上瀬由和.xlsx ├── 宮瀬尚紀.xlsx ├── 河野利香.xlsx ├── 石崎和香菜.xlsx └── 西尾謙.xlsx

イメージ説明

投稿2020/07/26 08:39

編集2020/07/26 08:41
tiitoi

総合スコア21954

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kotaro_szk

2020/07/26 08:46

早速ご対応いただきありがとうございます! 自分の環境でも試してやってみたいと思います! 本当に本当に助かりました!
tiitoi

2020/07/26 08:48 編集

デザインに関しては、Excel で中身が空のテンプレートを作っておいて、Openpyxl で読み込んで、DataFrame の値を入れて保存する形がいいかもしれません。 Openpyxl でゼロから見た目が凝った Excel ファイルを作るのは大変だと思うので。。
kotaro_szk

2020/07/26 09:24

そうなんですね、書き出しすらままならないので 見た目は二の次かもしれないですが可能な限り自分でやってみたいと思います。 また機会がありましたらご教授いただければ幸いです^^!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問