[作って]異なるセルを見つけるマクロ

  1. Home
  2. Excel
  3. ExcelのAI活用

【2025年9月】

今回は、ちょっと変わったマクロを作成してもらいます。結論から言いますが、このマクロは、Excelに存在する"ある機能"を使えば一発です。それを、プログラミング的な発想で解決しようとすると、けっこう難しくなるっていう話です。

上図のようなリストがあります。B列の「国語」からD列の「英語」まで数値が入力されていて、E列のSUM関数で合計を求めています。よくあるケースです。E列先頭(セルE2)の数式は正しいです。

しかし、セルE4の数式は、参照する範囲が間違っています。

E列のセル範囲E2:E10には、このように間違った数式があるかもしれないので、もしあったら、間違っているセルの文字を赤色にします。プロンプトが難しいですね。うまく伝わるといいのですが。

セル範囲E2:E10にはSUM関数を入力しています。SUM関数で合計しているのは、B列「国語」列からD列「英語」列までです。セルE2に入力した「=SUM(B2:D2)」は正しいです。セル範囲E3:E10に入力したSUM関数の中で、セルE2とは異なる参照をしている場合、そのセルの文字色を赤くするVBAのコードを作ってください。

う～む、やはり難しいのでしょうかね。確かに、これはプログラミングの技術よりも、Excelの仕様に精通していなかったら、手も足も出ない内容です。もう一度お願いしています。

セル範囲E2:E10にはSUM関数を入力しています。SUM関数で合計しているのは、B列「国語」列からD列「英語」列までです。セルE2に入力した「=SUM(B2:D2)」は正しいです。セル範囲E3:E10に入力したSUM関数の中で、セルE2とは異なる参照をしている場合、そのセルの文字色を赤くするVBAのコードを作ってください。Pythonは使いません。ExcelのVBAマクロを作ってください。 
Sub HighlightWrongSumReferences()
    Dim ws As Worksheet
    Dim cell As Range
    Dim correctFormula As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    correctFormula = "=SUM(B2:D2)"
    
    For Each cell In ws.Range("E3:E10")
        If cell.HasFormula Then
            If cell.Formula <> Replace(correctFormula, "2", cell.Row) Then
                cell.Font.Color = vbRed
            Else
                cell.Font.Color = vbBlack
            End If
        End If
    Next cell
End Sub
このマクロは、セルE3～E10のSUM関数の参照範囲が「B行番号:D行番号」と異なる場合、
そのセルの文字色を赤にします。実行にはマクロ有効ブックが必要です。

さすがです。作ってくれました。実行してみましょう。

分かりやすくするため、F列に、E列の数式を表示します。太字にした数式が間違っています。

提示されたマクロで何をやっているのか、簡単に解説します。まず、正しい数式「=SUM(B2:D2)」を変数correctFormulaに入れます。次に、セル範囲E3:E10をFor Eachで1つずつチェックします。最初のセルE3でのチェックは、まず変数correctFormulaに入っている「=SUM(B2:D2)」の、"2"を"3"に置換します。置換した"3"とは、チェックしているセルE3の行番号です。セルE3の数式は「=SUM(B3:D3)」なので正しいです。次のセルE4も同じ処理をします。変数correctFormulaを「=SUM(B4:D4)」に置換しましたが、これはセルE4に入力されている「=SUM(B4:C4)」とは異なっています。なので「cell.Font.Color = vbRed」として、セルも文字色を赤色にしています。

なるほどね～数式を文字列として比較するという作戦ですか。いや、シンプルでいいと思います。私はExcelの専門家なので、どうしても"Excel的"な発想で考えがちです。ちなみに、事前に作成して動作確認した私のマクロは、こんな感じです。

Sub Sample1()
    Dim c As Range, correct As Range
    Set correct = Range("E2").DirectPrecedents
    For Each c In Range("E3:E10")
        With c.DirectPrecedents
            If .Count <> correct.Count Then c.Font.Color = 255
            If .Column <> correct.Column Then c.Font.Color = 255
        End With
    Next c
End Sub
DirectPrecedentsは、セルが参照しているセル範囲を返します。セルE2が参照しているのは、セル範囲B2:D2です。これを、文字列としてではなくオブジェクト変数に入れています。正しいかどうかの判定は、ちょっと不本意です。調べているのは2点「参照しているセルの個数は3個か？」と「参照している左端はB列か？」です。間違った数式も私が作りましたので、何となく、それらが見つかるような判定で"お茶を濁して"います。もっと、いろいろなケースに対応するには、不十分だなって感じてます。にしても、数式を文字列として扱って、行番号の数値を一括で置換するというのは考えなかったです。だって、今回のケースでは使えますけど、たとえば正しい数式が「=LEFT(A2,2)」みたいな奴だったり、名前定義＋LAMBDA関数やユーザー定義関数などで「=SUM2(B2:D2)」なんてのも作れますしね。安易に一括置換するってのは、そういうのを考えたとき候補から外しました。

Excelの機能を利用する

冒頭にネタばらししたように、実はこうした"異なっているセル"を一発で見つける機能が、Excelにはあります。「アクティブ列との相違」です。「アクティブ列との相違」に関しては、下記のページでメッチャ詳しく解説していますので、ぜひご覧ください。最後の方で、致命的な誤訳についても触れています。

見えない違いを調べる機能

さて、この「アクティブ列との相違」は、もちろんVBAからでも実行できます。この機能を使うと、マクロは次のようになります。

Sub Macro1()
    Range("E2:E10").ColumnDifferences(Range("E2")).Font.Color = 255
End Sub

ああ、いまさらですけど、AIが提示したコード内で使われている「vbRed」と上記「255」は、どちらも同じ赤色を表しています。にしても、たったこれだけです。もちろん今回は「異なっているセルが必ず存在する」という前提なので、この1行で済みます。もし、異なっているセルが1つも見つからなかったら、上記の1行ではエラーになりますので、何らかのエラー対策を施します。

Sub Macro1()
    On Error Resume Next
    Range("E2:E10").ColumnDifferences(Range("E2")).Font.Color = 255
    If Err.Number > 0 Then MsgBox "見つかりません"
End Sub

みたいな感じかな。

まとめ

今回検証したかったのは、AIが作るマクロって、Excelの機能を上手く活用するんだろうか、ってことです。たぶん、活用しないんだろうなと予想していました。さすがに、並べ替えや検索で、まさかバブルソートや二分探索は使ってこないでしょうけどｗ いや、今度試してみよう。