【openpyxlで数式や関数を値貼り付け】1セルのみも可能

過去の自分

openpyxlでエクセルを編集しているんだけど、
セル内に数式が入っているから値がバグる…。
openpyxlを使って、まずは数式を値貼り付けしたいんだけどできるの！？

今の自分

簡単にできますよ！
「openpyxlで数式を値貼り付けする方法」
「特定のセルのみ値貼り付けする方法」
「値貼り付けする際の注意点」を解説いたします！

【著者情報】
Python歴３年。
入社２年目の春に先輩が突然トンズラし、業務を半分肩代わりするハメに…。
今までの1.5倍の仕事をこなせるはずもなく、苦しむ毎日。
業務効率化を模索中にPythonと出合う。
業務とPythonの相性が良く、２倍以上の効率化を実現。現在も効率化を進行中。

スポンサードリンク

openpyxlで数式を値貼り付けするには

「data_only = True」を記述します。まずは実行見本をどうぞ。

実行見本

全コード

ひとまず全コードを網羅します。解説は後ほど行います。

import openpyxl

wb = openpyxl.load_workbook('input.xlsx', data_only = True)

wb.save('output.xlsx')

事前準備

openpyxlのインストール

既存のエクセルファイルを編集するには「openpyxl」のインストールが必要です。

openpyxlのインストール方法

openpyxlを初めて使用する場合は、下記コードを入力・実行して、インストールしてください。

pip install openpyxl

※openpyxlを使用したことがある場合は、このインストール作業は不要です。

openpyxlの基礎解説

既存ファイルを開いて保存する方法などの操作の基本解説は、下記記事で行っております。
ご参考ください。

解説：「data_only = True」

※わかりやすさを重視しております。厳密には解釈が異なる場合がありますことをご了承ください。

wb = openpyxl.load_workbook('input.xlsx', data_only = True)

上記は、エクセルのワークブックを開いて変数に代入する記述です。
（）内に「data_only = True」を書くことにより、ワークブック内の数式を値貼り付けすることができます。

正確には、「ワークブック内の数式を値に変換してから開く」といった感じでしょうか。
まあ、結果的には値のみとなりますので、同じようなことですね…。

しかしながら、この方法では、ワークブック全体の数式が値貼り付けされてしまいます。
特定のセルのみを値貼り付けしたい場合は、次項をご覧ください。

スポンサードリンク

特定のセルのみ値貼り付けする方法

前項の方法では、ワークブック全体の数式が値貼り付けされてしまいました。
特定のセル、例えばA1セルのみ値貼り付けしたい場合はどうしたらいいのでしょうか？
少し工夫が必要ですが、これも簡単にできます。

全コード（特定のセルのみ値貼り付け）

import openpyxl

wb1 = openpyxl.load_workbook('input.xlsx', data_only = True)
ws1 = wb1['Sheet1']

wb1.close

wb2 = openpyxl.load_workbook('input.xlsx')
ws2 = wb2['Sheet1']

ws2['A1'].value = ws1['A1'].value

wb2.save('output.xlsx')

少し長いですね…。後ほど詳しく解説いたしますので、まずは実行見本をどうぞ。

実行見本（特定のセルのみ値貼り付け）

A1セルのみが値貼り付けされていて、A2セルや他のシートは数式のままなのがわかります。
では解説に移りましょう。

解説（特定のセルのみ値貼り付け）

wb1 = openpyxl.load_workbook('input.xlsx', data_only = True)
ws1 = wb1['Sheet1']

wb1.close

wb2 = openpyxl.load_workbook('input.xlsx')
ws2 = wb2['Sheet1']

ws2['A1'].value = ws1['A1'].value

wb2.save('output.xlsx')

まずはざっくり解説すると、下記のようなことを行っております。

ワークブックを「data_only = True」で開く
値のみになったワークシートデータを変数（WS1）に入れる
ワークブックを保存せずに閉じる
ワークブックを再び開く（「data_only = True」にはしない）
ワークシートデータを変数（WS2）に入れる（これは数式のまま）
WS1のA1セル（値のみ）をWS2のA1セル（数式）に代入する
ワークブックを保存して終了

というような感じです。

WS1は全て値のみ、WS2は全て数式のままです。
全て値のみのWS1の中から、A1だけをWS2にコピペしたようなものだと思ってください。

ws2['A1'].value = ws1['A1'].value

上記が「WS1」から「WS2」へコピペしたときのコードです。
コピペするは値のみですので、「.value」を記述しましょう。

for文を使うことで、特定の列や行だけを値貼り付けすることもできます。
下記記事にてfor文をわかりやすく解説しております。ご参考ください。

スポンサードリンク

値貼り付けする際の注意点（数式が消える！？）

今回の方法「data_only = True」で値貼り付けを行うと、稀に問題が発生することがあります。
それは「値貼り付けされずに数式自体が消える」という問題です。

原因として考えられるのが「セル内の数式が、openpyxlなどで書き込まれていた場合」です。

例えは「ws[‘A1’] = ‘=B1’」と記述することで、A1セル内に「=B1」という数式を書き込むことができます。
そのエクセルファイルを「data_only = True」で開いて保存してしまうと数式が消えてしまうようです。

この問題を解決するには下記のような対処が必要です。

数式を手動で書き込む
openpyxlで数式が書かれたワークブックを一度開き、何もせずに保存してから値貼り付け
xlwingsを使う

1つ目と2つ目はどちらも手動操作が入るため効率的ではありません。
3つ目の「xlwings」は2つ目を自動で行ってくれるというもので、一番効率的です。
ざっくりコードを網羅いたします。

import xlwings

excel = xlwings.App()
workbook = excel.books.open('input.xlsx')
sheet = workbook.sheets['Sheet1']
workbook.save()
workbook.close()
excel.quit()

#以降は値貼り付けのコードを記述

これで数式が消えることはなくなります。

xlwingsを初めて使う場合、インストールが必要となります。
まずは下記コードを実行して、インストールを完了させてください。