openpyxlで配列数式を算出する方法について

Question

前提・実現したいこと

既存システムで出力されているエクセルフォーマットに合わせ
新規にopenpyxlからエクセル出力を行っているのですが、配列数式で算出している個所を
openpyxlで実現するのにはどの様に行えばいいのでしょうか？

発生している問題・エラーメッセージ

例A1から列B5の絶対値をサマリーし、列C1へ出力する際
ws['C1'].value = "{=SUM(ABS(A1:B5))}"

としても実際のエクセルには

{=SUM(ABS(A1:B5))}

と表示されるだけです。

該当のソースコード

import openpyxl

#保存エクセルファイル名
XLSFile="c:\temp\sample.xlsx"
wb = openpyxl.Workbook()
ws = wb.active

ws['A1'].value=1
ws['A2'].value=1
ws['A3'].value=1
ws['A4'].value=1
ws['A5'].value=1

ws['B1'].value=1
ws['B2'].value=1
ws['B3'].value=1
ws['B4'].value=1
ws['B5'].value=1

ws['C1'].value = "{=SUM(ABS(A1:B5))}"
wb.save(XLSFile)

試したこと

ws['C1'].value = "=SUM(ABS(A1:B5))"
としても、エクセルには
♯Value!
と表示されるだけです。

配列数式は使用できない為、
ws['C1'].value =ABS(A1)+ABS(A2)+ABS(A3)+ABS(A4)+ABS(A5)+
ABS(B1)+ABS(B2)+ABS(B3)+ABS(B4)+ABS(B5)

とするしかないのでしょうか？

Answer 1

openpyxlというよりExcelの問題です。
"=SUM(ABS(A1:B5)"→"=SUMPRODUCT(ABS(A1:B5))"でできます。
参考：Summing Absolute Values

Comments

[taharasan]

ありがとうございます。
"=SUMPRODUCT(ABS(A1:B5))"
で実現できました。

[KSwordOfHaste]

なるほどそういうことなのですね！
そもそも自分は"{SUM(ABS(A1:B5))}"みたいな式を知りませんでしたが、こう設定されてると実際は
Formula："=SUM(ABS(A1:B5))"
HasArray: True
となるのですね・・・HasArrayはVBAで試してみるとTrueには設定できないのでなんかExcelの中に特別な秘密がある感じなのかなと想像しました。

[can110]

自分も配列数式は使ったことありませんし知りませんでした。
ちょっと調べてみたところVBAでは「セル.FormulaArray = "=SUM(ABS(A1:B5))"」で設定できるようですね。

[KSwordOfHaste]

なるほど！わざわざコメントいただき恐縮です！

Answer 2

ws['C1'].value = "{=SUM(ABS(A1:B5))}"

の行ですけども、なぜ"{...}"となっているのでしょうか？

ws['C1'].value = "=SUM(ABS(A1:B5))"

このようにしないとformula(式)の設定とは見做されない気がします。

http://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/cell/cell.html

インストールしてないので上のソースの_bind_valueメソッドを見て想像でものを言ってます。間違ってたらご容赦くださいませ。

Comments

[KojiDoi]

＞KSwordOfHasteさん
お手本にする既存のエクセルワークシートに配列数式が使われているので再現したいという話でしょう。
http://office-qa.com/Excel/ex69.htm

[taharasan]

エクセルで配列数式を行う際
=SUM(ABS(A1:B5))
と入力しCtrl+Shift キーを押しながら Enterを押すと
{=SUM(ABS(A1:B5))}
と表示されるので、これでいけるかなーとおもったのですが駄目でした。

ws['C1'].value = "=SUM(ABS(A1:B5))"
は「試したこと」で記載していますが、エラーとなってしまいました。

上記で解決しましたので、ありがとうございました。

[KSwordOfHaste]

お二人ともコメントありがとうございます。
こういう数式があるってこと自体知りませんでした！