はじめに
この記事ではプラス・マイナスの数字のセットを見つけて目印を表示する数式を紹介します。
足すと0になるペアになっているもの、相殺のペアになっているものを見つけるとも言いかえられます。
相殺されていない、実態のある数字はどれなのか？
勘定科目の残高の中身はどれなのか？
実務では上記のような用途で使っている数式です。
今回は試みとして数式を組み立てる過程を辿る更正になっています。
数式だけ知れれば良い場合は見出しの「解決編」だけ見てください。
一応、先に数式も載せておきます。
（期待に沿った式ではない場合、ここで引き返せますからね）
<例で使用している数式>
=IF(COUNTIF($C$3:$C$12,-C3)>=COUNTIF($C$3:C3,C3),"相殺","残高")数字のプラスマイナスのセットを見つけて、目印を表示する数式
数式を作る過程
えっと…これがこれを消してて…これがこれを…
キュウさん、頑張っていますね！
これは勘定科目の残高の内容を調べているところですか？
はい、そうなんです
ただ目視でやるのには限界を感じていて、数式でなんとかできそうだなとは思ったのですが…。
たしかに数式で何とかなりそうな感じがしますね。
一緒に考えてみましょうか！
よろしくお願いします！
対象の数字（C列）が相殺の対象と言える条件は何でしょう？
それは足すと0になる数、つまり対象の数字をプラス・マイナス反転させた数が表の中に存在することでしょうか。
そうですね。
ただ、例の表のように-100が2個に対して100が4個という場合をその条件に当てはめてしまうと…。
相殺できない2つの100まで条件に合う判定になってしまいます。
ただ、相殺できる2つはちゃんと相殺にしたいので、ここの分岐がポイントになりそうです。
良いところに気が付きましたね。
では、相殺できる100とできない100は何が違うのでしょうか。
1個目の100は1個目の-100と相殺されるはずです。
2個目の100は2個目の-100と相殺。
3個目の100は…相殺の相手となる-100が表の中にないから相殺ではないです。
つまり、相殺のペアになる数値の数を、対象の数値の数が超えていなければ相殺できる…？
相殺のペアになる数値の数>=対象の数値の数 と整理できそうですね。
ただ、対象の数値の数は上からみて何回目の登場かと言い換えたほうが良さそうです。
相殺のペアになる数値の数>=対象の数値は上からみて何回目の登場か
相殺のペアが全部で2個のとき、対象の数値の登場が3番目だと、相殺の対象とならないということですね。
あとはこれを数式にすれば良いわけですね。 左辺の「相殺のペアになる数値の数」はCOUNTIF関数の数式で求められそうです。
右辺の「対象の数値は上からみて何回目の登場か」はコツが必要になりますね。
同じくCOUNTIF関数が使えそうですが、表の上から順にカウントするには、対象のセルより上のセルのみをCOUNTIFの対象とすれば良いのです。
セル範囲を絶対参照と相対参照の組み合わせ、例えば「$A$1:A1」のように設定します。
「$A$1:A1」の参照は下のセルにコピーすると「$A$1:A2」「$A$1:A3」「$A$1:A4」と、参照が縦に伸びていくのですね。
右辺と左辺を「>=」で繋いで条件式を作って、その条件式をIF関数の第1引数に設定することで「”相殺”」「”残高”」と表示を分岐させれば…。
解決編！
<例で使用している数式>
=IF(COUNTIF($C$3:$C$12,-C3)>=COUNTIF($C$3:C3,C3),"相殺","残高")でっ…できた…っ！ できました！！
おつかれさまでした。
さて、数式が完成したら確認が必要です。
望んだ結果になっていますか？
バッチリです！！
これで…これで楽ができます！！
その楽な方法を求める姿勢はとても大事です。
私にこんな数式が作れるなんて…！
額にしまって飾っておきます！
そこまでしなくても…。
おわりに
この数式は単純に同じ数字のプラス・マイナスを上からマッチングするものです。
例えば、200に-100が2つで対応しているような場合には機能しません。
私はこういう数字の対応を調べる業務を目でチェックしてやっていました。
そんな中、苦労して考えたのがこの数式でした。
COUNTIF関数とIF関数しか使わないので、とてもシンプルですね。
大した数式ではないですが、その改善効果はすさまじいものでした。
目も疲れないですし、正確で早い。
個人的に思い入れのあるこの数式を以前Twitter（自称X）に投稿していたのですが、
その投稿を探して活用していただけたと聞き、嬉しくなって記事にした次第であります。
記事中では、数式を作る過程を追ってみましたが、意外に難しいですね。
対応する数字のペア（100と-100のように）のうち、少ない方は条件式がFALSEになることがないので全て「相殺」になる。
その理屈は分かるのですが、どうも上手い説明ができなかったように思います。
また良い言い回しが思いついたら記事を修正するかもしれません。
【外部リンク】Microsoftサポート|COUNTIF関数
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント