数式を使​​用して Excel で FOR ループを作成する方法 (3 つの例)

Excel VBAを使ってFORループを作りたくないですか？ 式を使用してExcelで？この記事では、FOR ループ を作成する方法を示しました。 数式を使用します。

Excel VBA でコーディングする方法を知っている場合 、あなたは恵まれています🙂。ただし、VBA でコードを書いたことがない場合は、 または、Excel ワークブックに Excel VBA を含めないようにしたい ほとんどの場合、単純なループを作成するために独創的に考える必要があります。 .

作業ファイルをダウンロード

以下のリンクから作業ファイルをダウンロードしてください:

数式を使​​用して Excel で FOR ループを作成する 3 つの例

ここでは、3 のデモンストレーションを行います FOR ループの作成例 数式を使用して Excel で。詳細な例を見てみましょう。

1.組み合わせた関数を適用して Excel で FOR ループを作成する

では、この例を書くようになった背景を教えてください。

私は Udemy のいくつかのコースの作成者です。コースの 1 つは、Excel の条件付き書式に関するものです。コースのタイトルは次のとおりです。 7 つの実用的な問題で Excel の条件付き書式を学習します。 [このコースに無料でアクセスするには、ここをクリックしてください ].

コースのディスカッション ボードで、学生から次のような質問がありました [スクリーンショット画像]。

Udemy で学生から尋ねられた質問。

上記の質問を注意深く読み、解決してみてください…

上記の問題を解決する手順:

ここでは、OR を使用します 、オフセット 、最大 、MIN 、ROW FOR ループを作成するための Excel 数式として機能します .

• まず、新しいワークブックを開き、上記の値を 1 つずつワークシートに入力します [セル C5 から開始] ].
• 次に、範囲全体を選択します [セル C5:C34 から] ].
• 3 番目に、ホームから リボン>> 条件付き書式 をクリックします コマンド
• 最後に、新しいルールを選択します ドロップダウンからのオプション

現時点では、新しいフォーマット ルール ダイアログ ボックスが表示されます。

• [ルール タイプの選択] で ウィンドウ>> [数式を使​​用して書式設定するセルを決定する] を選択します オプション
• 次に、この式が真である場合の値の書式設定 フィールドに次の式を入力します:

=OR(OFFSET(C5,MAX(ROW(C$5)-ROW(C5)+3,0),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)-OFFSET(C5,MAX(ROW($C$5)-ROW(C5),-3),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)=3)

• [フォーマット…] をクリックして、適切なフォーマット タイプを選択します。 ダイアログ ボックスのボタン

この時点で、[セルの書式設定] という名前のダイアログ ボックスが表示されます。

• さて、塗りつぶしから オプション>> 任意の色を選択する必要があります。ここでは、ライト ブルーを選択しました バックグラウンド。 サンプルもご覧いただけます 即座に。 この場合、任意のライトを選択してみてください 色。暗い色は入力されたデータを隠す可能性があるためです。次に、フォントの色を変更する必要がある場合があります .
• 次に、OK を押す必要があります フォーメーションを適用します。

• その後、OK を押す必要があります 新しいフォーマット ルールについて ダイアログボックス。 プレビューですぐにサンプルを確認できます ボックス。

最後に、フォーマットされた数値を取得します。

上記の問題を解決するアルゴリズムをお見せしましょう:

• ここでは、アルゴリズムを簡単に理解できるように、2 つの参照セルを使用して全体を説明します:セル C11 とC17 .細胞内 C11 とC17 、値は 10 そして 20 それぞれ（上の画像）。 Excel の数式に慣れている場合は、OFFSET の匂いがします。 関数、OFFSET として 関数は基準点で動作します。
• ここで、セル範囲 C8:C11 の値を取得しているとします。 &C11:C14 、および C14:C17 &C17:C20 並べて[下の画像]。参照セルは C11 です とC17 合計 7 を服用しています 参照セルの周囲のセル。次のような架空の画像が得られます。最初の部分から、画像からパターンを見つけることができます。 C9–C12=3 、C10-C13=3 、パターンがあります。しかし、第 2 部では、そのようなパターンはありません。

• では、上記のパターンを念頭に置いてアルゴリズムを構築しましょう。一般的な数式を作成する前に、セル C11 の数式を示します。 とC17 そして、式を修正して、すべての人に共通のものにします。基準点 ( C11 など) または C17 )、合計 7 を取る その周りのセル (基準点を含む) を並べて配置し、配列を作成する数式に配置します。次に、違いのいずれかが 3 に等しい場合、配列の違いを見つけます。 参照セルが TRUE になること
• ここでは、OFFSET を使用して簡単に行うことができます オフセットとして機能します 関数は配列を返します。セル参照用に言う C11 、次のような式を書くことができます:=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3) .この式は何を返しますか?数式の最初のオフセット関数は配列を返します:{10; 11; 12; 15} 、2 番目のオフセット関数は配列 {5; を返します。 8; 9; 10} . {10; 11; 12; 15} – {5; 8; 9; 10} ={10-5; 11-8; 12-9; 15-10} ={5; 3; 3; 5} .この配列が =3 で論理的にテストされる場合 次に、Excel は内部的に次のように計算します。 {5=3; 3=3; 3=3; 5=3} ={偽;真実;真実;いいえ} . OR 関数がこの配列に適用されます:OR({False; True; False; True} 、あなたは TRUE を取得します .だからセル C11 返された真の値を取得します。
• これで、このアルゴリズムがどのように機能するかについての全体的な概念は理解できたと思います。今、問題があります。この数式は、セル C8 から機能します 、セル C8 の上 、3 あります 細胞。ただし、セル C5、C6、 の場合 とC7 この式は機能しません。したがって、これらのセルの数式を変更する必要があります。
• セル C5 C7 へ 、式が上限を考慮しないことを望みます 3 細胞。たとえば、セル C6 の場合 、数式はセル C11 の数式とは異なります :=OR(オフセット(C11, 0, 0, 4, 1)-オフセット(C11, -3, 0, 4, 1)=3) .
• セル C5 の場合 、式は次のようになります:OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3) .
• 次に、セル C6 について 、式は次のようになります:OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3) .
• その後、セル C7 について 、式は次のようになります:OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3) .
• 再び、セル C8 について 、式は次のようになります:OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [これが一般式です].
• 次に、セル C9 について 、式は次のようになります:OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [これが一般式です].
• 最後に、上記の式からいくつかのパターンを見つけましたか?最初の オフセット 関数の行引数が 3 から減少しました 0 まで;高さの引数が 1 から増加しました 4 まで . 2 番目の OFFSET 関数の行引数が 0 から減少しました -3 に 高さの引数が 1 から増加しました 4まで .
• まず、最初の OFFSET 関数の行引数は次のように変更されます:MAX(ROW(C$5)-ROW(C5)+3,0)
• 次に、2 番目の OFFSET 関数の行引数は次のように変更されます:MAX(ROW(C$5)-ROW(C5),-3)
• 3 番目に、最初の OFFSET 関数の高さ引数は次のように変更されます:MIN(ROW(C5)-ROW(C$5)+1,4)
• 4 番目に、2 番目の OFFSET 関数の高さ引数は次のように変更されます:MIN(ROW(C5)-ROW(C$5)+1,4)
• では、上記の変更を理解してください。これらを理解するのはそれほど難しくありません。これらすべて 4 変更は FOR LOOP として機能します Excel VBA の一部ですが、Excel の数式で作成しました。
• これで、C5:C34 のセルに対して一般式がどのように機能するかがわかりました。 .

それで、私は Excel スプレッドシートでのループについて話していました。つまり、これは Excel でのループの完璧な例です。ここでは、数式が 7 かかるたびに セルに作用し、特定の値を見つけます。

2. Excel で FOR ループを作成するための IF &OR 関数の使用

この例では、セルに値が含まれているかどうかを確認するとします。さらに、Excel VBA FOR ループでは、 これは簡単に実行できますが、ここでは Excel の数式を使用して実行します。

これで、 を使用できます IF 、および OR FOR ループを作成するための Excel 数式として機能します .さらに、好みに応じてこの式を変更できます。以下に手順を示します。

手順:

• まず、別のセルを選択する必要があります E5 ステータスを表示したい場所 .
• 次に、E5 で対応する数式を使用する必要があります。

=IF(OR(B5="",C5="",D5=""),"Info Missing","Done")

• 続いて、 ENTER を押します 結果を取得します。

式の内訳

ここで、 OR 関数は TRUE を返します 指定されたロジックのいずれかが TRUE になった場合 .

• まず、B5=”” 1位です セル B5 かどうかをチェックするロジック 値を含むかどうか
• 第二に、C5=”” 2番目です セル C5 かどうかをチェックするロジック 値を含むかどうか
• 3 番目に、 D5="" 3番目です 論理。同様に、セル D5 かどうかをチェックします 値を含むかどうか

さて、 IF 関数は、指定された条件を満たす結果を返します。

• OR の場合 関数は TRUE を返します その後、「情報がありません」と表示されます 」をステータスとして .それ以外の場合は、「完了」となります 」をステータスとして .

• その後、フィル ハンドルをドラッグする必要があります。 オートフィルのアイコン 残りのセルの対応するデータ E6:E13 . または、フィル ハンドルをダブルクリックすることもできます アイコン

最後に、すべての結果を取得します。

3. SUMIFS 関数を使用して Excel で FOR ループを作成する

特定の人の合計請求書を作成したいとします。その場合、FOR ループ を使用できます。 エクセルの数式を使用。ここでは、SUMIFS 関数を使用します。 FOR ループを作成する エクセルで。以下に手順を示します。

手順:

• まず、別のセルを選択する必要があります F7 ステータスを表示したい場所 .
• 次に、F7 で対応する数式を使用する必要があります。

=SUMIFS($C$5:$C$13,$B$5:$B$13,E7)

• 続いて、 ENTER を押します 結果を取得します。

式の内訳

• こちら、$C$5:$C$13 SUMIFSのデータ範囲です 関数は合計を行います。
• 次に、$B$5:$B$13 SUMIFSのデータ範囲です 関数は指定された基準をチェックします
• 最後に、E7 が基準です。
• だから、SUMIFS 関数は E7 の支払いを追加します セル値。

• その後、フィル ハンドルをドラッグする必要があります。 アイコンをクリックして、残りのセルに対応するデータを自動入力します F8:F10 .

最後に、結果が得られます。

結論

この記事がお役に立てば幸いです。ここでは、3 について説明しました FOR ループの作成に適した例 数式を使用して Excel で。当社のウェブサイト Exceldemy にアクセスできます Excel 関連のコンテンツをさらに学習します。コメント、提案、またはクエリがあれば、下のコメント セクションにドロップしてください。

続きを読む

• Excel VBA で Do While ループを使用する方法
• VBA Excel の次のループについて (ループをステップ実行して終了する方法)