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

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

新規登録して質問してみよう
ただいま回答率
85.48%
ファイル

ファイルとは、文字列に基づいた名前又はパスからアクセスすることができる、任意の情報のブロック又は情報を格納するためのリソースです。

openpyxl

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

Python

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

pandas

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

Q&A

2回答

1681閲覧

売上を計上している顧客管理データからテンプレートへ転記

MitAbe

総合スコア15

ファイル

ファイルとは、文字列に基づいた名前又はパスからアクセスすることができる、任意の情報のブロック又は情報を格納するためのリソースです。

openpyxl

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

Python

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

pandas

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

0グッド

0クリップ

投稿2021/08/11 16:03

編集2021/09/01 16:02

日付順に記載している顧客管理データ(売上も計上している)から各営業さんごとの給与明細まで出したくpythonを勉強しております。

今現在下記の項目に取り組んでおります。
データを担当ごとに給与計算表テンプレートに転記したい。転記する項目は、担当名、売上金額です。その際、担当名と歩合率でグループ化しテンプレートに転記をしたい。
流れといたしましては、
➀元のデータから、特定の項目のみを抽出し、excelに転記致します(*の部分です。)
➁上記で、転記したデータをグループ分けし、その後テンプレートに出力する。

上記となります。
お忙しいとは思いますが、助けていただきたく思います。
何卒よろしくお願い致します。

###➀エクセルの特定の文字でマークした行だけをPythonで転記

import openpyxl wb = openpyxl.load_workbook("売上管理表.xlsx") ws = wb["売上管理表"] bikou_rows = [] for row in ws.iter_rows(min_row=4): if row[16].value == "*" or row[16].value == "備考": bikou_rows.append(row) ws2 = wb.create_sheet(title="集金済み") for bikou_row in bikou_rows: ws2.append([r.value for r in bikou_row]) wb.save("売上管理表.xlsx")

###出力結果です。
![イメージ説明

左下に【集金済み】との項目ができ下記のように転記ができました。

イメージ説明

顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 備考 1 2021-06-07 0:00:00 a1 近藤 阿部 千葉県2-12-14 工事 090-xxxx-xxxx 2200000 0.12 * 5 2021-06-11 0:00:00 a5 近藤 佐藤 沖縄県 工事 94-xxxx-xxxx 1430000 0.12 * 6 2021-06-12 0:00:00 a6 近藤 齋藤 沖縄県 工事 95-xxxx-xxxx 675000 0.11 * 8 2021-06-14 0:00:00 a8 近藤 阿部 沖縄県 工事 97-xxxx-xxxx 908000 0.06 * 近藤 阿部 1900000 0.12

➁に関する質問です。
テンプレートには給与の計算式が入っておりますので、groupbyを使用し担当名 と歩合率をグループ化して転記できれば良いのではないか、と考えております。
自分が行った手順は下記になります。(2パターン試してみました。)
パターンaとbです。

下記が、コード等になります。
ご指南の程何卒よろしくお願い致します。

###転記するためのテンプレートです。

イメージ説明

給与計算表 顧客コード 日付 担当者名1(クローザー) 担当者名2(アポインター) 住所 〒 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 給与支給額 ¥0 12.0% ¥0 ¥-1,000 11.0% ¥0 ¥-2,000 10.0% ¥0 ¥-3,000 9.0% ¥0 ¥-4,000 8.0% ¥0 ¥-5,000 7.0% ¥0 ¥-6,000 6.0% ¥0 ¥-7,000 5.0% ¥0 ¥-8,000 4.0% ¥0 ¥-9,000 3.0% ¥0 ¥-10,000 2.0% ¥0 ¥-11,000 1.0% ¥0 合計 ¥0

###パターンaのコードです。
ながれです。
・pandasのgroupbyを使用し、グループ分けを行う。
・上記をcsvファイルにし、保存する。
・上記csvファイルより、指定のテンプレートに出力を試みる。
問題点
・指定のファイル名(torihiki)でexcelシートは立ち上がるが、中身に何も記載されていない。
・担当ごとにexcelファイルが作成される予定だが、1つしか作成されない。
・ファイル名(torihiki)を開けると"循環参照"という注意事項が出てくる
・12%の項目がなくなってしまう。

from datetime import datetime from openpyxl import load_workbook import pandas as pd import csv df = pd.read_excel('売上管理表.xlsx',sheet_name = '集金済み') pd.set_option('display.max_columns',None) pd.set_option('display.max_rows',13) print(df) df.groupby(['担当者名2(アポインター)','歩合率']).sum()['金額'].to_csv("./appoint.csv",encoding="shift jis") filepath1 = '給与計算表.xlsx' wb1 = load_workbook(filename=filepath1) ws1 = wb1['給与計算表'] values1=[[cell.value for cell in row1] for row1 in ws1] del values1[0] conv_values1 = list(zip(*values1)) torihiki_list = list(set(conv_values1[0])) for torihiki in torihiki_list: product_data =[] for i in range(len(values1)): if values1[i][0] == torihiki: product_data.append(values1[i]) ws1.title = '給与計算表' for y, row in enumerate(product_data): for x, cell in enumerate(row): ws1.cell(row= y+2, column= x+1, value=product_data[y][x]) now = datetime.now() hiduke = now.strftime('%Y-%m-%d') filename = 'torihiki.xlsx' wb1.save(filename)

###出力結果

Unnamed: 0 Unnamed: 1 顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) \ 0 NaN NaN 1.0 2021-06-07 a1 近藤 阿部 1 NaN NaN 5.0 2021-06-11 a5 近藤 佐藤 2 NaN NaN 6.0 2021-06-12 a6 近藤 齋藤 3 NaN NaN 8.0 2021-06-14 a8 近藤 阿部 4 NaN NaN NaN NaT NaN 近藤 阿部 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 備考 0 NaN 千葉県2-12-14 工事 090-xxxx-xxxx NaN NaN NaN 2200000 0.12 * 1 NaN 沖縄県 工事 94-xxxx-xxxx NaN NaN NaN 1430000 0.12 * 2 NaN 沖縄県 工事 95-xxxx-xxxx NaN NaN NaN 675000 0.11 * 3 NaN 沖縄県 工事 97-xxxx-xxxx NaN NaN NaN 908000 0.06 * 4 NaN NaN NaN NaN NaN NaN NaN 1900000 0.12 NaN >>>

見づらくて申し訳ありません。torihiki というexcelファイルが1つだけ出来上がりました。
イメージ説明

イメージ説明

###パターンbのコードです。
ながれです。
・pandasのgroupbyを使用し、グループ分けを行う。
・上記をexcelファイルにし、保存する。
・上記excelファイルより、指定のテンプレートに出力を試みる。
問題点
・エラーコードが出てしまう。
・担当者(アポインター)3名の不明なファイルが出来上がる。

from datetime import datetime from openpyxl import load_workbook import pandas as pd import csv df = pd.read_excel('売上管理表.xlsx',sheet_name = '集金済み') pd.set_option('display.max_columns',None) pd.set_option('display.max_rows',13) print(df) df.groupby(['担当者名2(アポインター)','歩合率']).sum()['金額'].to_excel("./appoint.xlsx") filepath1 = './appoint.xlsx' filepath2 = '給与計算表.xlsx' wb1 = load_workbook(filename=filepath1) ws1 = wb1['Sheet1'] wb2 = load_workbook(filename=filepath2) ws2= wb2['給与計算表'] values1=[[cell.value for cell in row1] for row1 in ws1] del values1[0] conv_values1 = list(zip(*values1)) torihiki_list = list(set(conv_values1[0])) for torihiki in torihiki_list: product_data =[] for i in range(len(values1)): if values1[i][0] == torihiki: product_data.append(values1[i]) ws2.title = 'torihiki' for y, row in enumerate(product_data): for x, cell in enumerate(row): ws1.cell(row= y+2, column= x+1, value=product_data[y][x]) now = datetime.now() hiduke = now.strftime('%Y-%m-%d') filename = torihiki wb2.save(filename)

###出力結果

===== Unnamed: 0 Unnamed: 1 顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) \ 0 NaN NaN 1.0 2021-06-07 a1 近藤 阿部 1 NaN NaN 5.0 2021-06-11 a5 近藤 佐藤 2 NaN NaN 6.0 2021-06-12 a6 近藤 齋藤 3 NaN NaN 8.0 2021-06-14 a8 近藤 阿部 4 NaN NaN NaN NaT NaN 近藤 阿部 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 備考 0 NaN 千葉県2-12-14 工事 090-xxxx-xxxx NaN NaN NaN 2200000 0.12 * 1 NaN 沖縄県 工事 94-xxxx-xxxx NaN NaN NaN 1430000 0.12 * 2 NaN 沖縄県 工事 95-xxxx-xxxx NaN NaN NaN 675000 0.11 * 3 NaN 沖縄県 工事 97-xxxx-xxxx NaN NaN NaN 908000 0.06 * 4 NaN NaN NaN NaN NaN NaN NaN 1900000 0.12 NaN Traceback (most recent call last): File "C:\Users\wildl\Desktop\新・給与関係\appointer.py", line 62, in <module> wb2.save(filename) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save save_workbook(self, filename) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook writer.save() File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\writer\excel.py", line 275, in save self.write_data() File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\writer\excel.py", line 67, in write_data archive.writestr(ARC_APP, tostring(props.to_tree())) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1802, in writestr with self.open(zinfo, mode='w') as dest: File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1505, in open return self._open_to_write(zinfo, force_zip64=force_zip64) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1600, in _open_to_write self.fp.write(zinfo.FileHeader(zip64)) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 759, in write n = self.fp.write(data) AttributeError: 'NoneType' object has no attribute 'write' >>>

イメージ説明

ご指摘の程、何卒宜しくお願い致します。

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

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

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

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

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

meg_

2021/08/11 16:30

> なお添付写真は、自分で作成したダミーの顧客管理データ等です。 画像ですと回答者がデータとして使えません。また添付画像はタイトル部分が見切れていて分かりません。 > 全く反応いたしませんでした。 エクセルファイルが保存されなかった、という意味でしょうか?
MitAbe

2021/08/14 16:02

meg_様 ご返信遅くなり誠に申し訳ありあせん。 エクセルのファイルが出来上がりませんでした。 いろいろと教材や、サイト等を見ながら試行錯誤してはおりますが、テンプレートに出力できない状態です。お忙しいとは思いますが、何卒、ご教授の程宜しくお願い致します。
MitAbe

2021/08/14 16:10

「過去tに投稿した質問と同じ内容の質問」とのご指摘を受けました。 知らぬこととは故、ご迷惑をおかけして申し訳ありませんでした。
meg_

2021/08/25 17:03

KeyError: '担当者名' のエラーが出るのはデータフレーム(df)が空のデータフレームだからですね。 ※コードでは「df_by = df.groupby(["担当者名2", "歩合率"]).sum() 」となっていて、エラーメッセージでは「df_by = df.groupby(["担当者名", "歩合率"]).sum()」となっています。不整合がありますと回答者が混乱しますので今一度ご確認ください。
guest

回答2

0

まず➀について、ヘッダーも転記したいのですが、出てきませんでした。

転記するためのコードがないので当然転記されません。

下記でどうでしょうか?

Python

1for row in ws.iter_rows(min_row=4): 2 3 if row[16].value == "*" or row[16].value == "備考": 4 bikou_rows.append(row)

【追記】

panndas を使用しグループ分けまでは、できるようになりましたが、そこから先のテンプレートへの記載がうまくいきませんでした。

python

1 2print(df.groupby(['担当者名2(アポインター)','歩合率']).sum()['金額']) 3df.to_csv("./appoint.csv",encoding="shift jis")

上記コードを下記にするとグループ化したデータが保存されるかと思います。(不要な列があれば削除するコードを追加ください)

python

1 2df.groupby(['担当者名2(アポインター)','歩合率']).sum().to_csv("./appoint.csv",encoding="shift jis")

投稿2021/08/25 16:11

編集2021/08/27 05:57
meg_

総合スコア10580

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

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

MitAbe

2021/08/25 16:59

meg_様へ 素早いご対応、誠にありがとうございます。 for row in ws.iter_rows(min_row=4):の部分でエクセルを4行目から1行ずつ読み込んでいくので、日付の等の部分も読み込み転記できるものかと思っておりました。 ご指摘ありがとうございました。 早速、再度見返し勉強致します。
meg_

2021/08/25 17:05

読み込んではいますが(元のコードだと)その後のif文で条件不一致でbikou_rowsには追加されません。確認ですが質問のコードはご自身で書かれたものですよね?
MitAbe

2021/08/25 18:00

meg_様へ 上記➀は、教科書や色々なサイト等を調べ、そこに記載してあるものをもとにおそらくこれなら転記できるのではないか、との予測で自分なりに書いてみたものです。ですので、勉強不足で理解不十分なところも多々あります。申し訳ございません。
meg_

2021/08/26 10:38

②についてですが、私なら①で作成したブックを保存 → pandasでそのブックからデータを読み取って加工する、の手順でやってみるかと思います。
MitAbe

2021/08/27 05:30

meg_様へ ご返信ありがとうございます。 panndas を使用しグループ分けまでは、できるようになりましたが、そこから先のテンプレートへの記載がうまくいきませんでした。 お忙しいとは思いますが、何卒アドバイスの程よろしくお願い致します。
MitAbe

2021/09/01 15:30

meg_様へ 頂いたアドバイスを含め色々と試してみました。 上記のパターンbがあと一歩のように思えるのですが、いかがでしょうか。 ご指摘の程宜しくお願い致します。
guest

0

「全く反応いたしませんでした。」がどういう意味かわかりませんが・・・

ちらっとコードを見た限りでは、2つ目に提示されたコードがかなりおかしいように思います。

  • for name, date in users.items():というループがありますがusersが定義されていません。
  • ループ内で関数make_user_salaraycalを呼んでいますが、定義されていません。
  • 関数make_user_salarycalを定義していますが、どこからも呼び出されていません。
  • 上記がタイプミスだとしても、そもそもループ内で関数を呼び出す以前に定義されている関数は1つもありません。
  • make_user_salaraycal(name, data)という関数呼び出しをしていますが、dataは定義されていません。

なので、普通に考えると「エラーが出力されるという反応」があるようにおもいますので、反応がないのなら実行方法・環境がおかしいのではないでしょうか?

投稿2021/08/11 21:03

編集2021/08/11 23:46
udon-ken

総合スコア657

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

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

MitAbe

2021/08/14 16:05

udon-ken 様 ご返信遅くなり誠に申し訳ありません。 ふあぁいる ファイル内にエクセルシートが出来上がらなかったという意味でした。 いろいろと至らぬ点が多く誠に申し訳ありませんでした。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.48%

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

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

質問する

関連した質問