SUMIF式とOFFSET式、検証リスト、およびコンボボックスを組み合わせて、月ごとに選択するデータから要約を返します。これはまさにパワーの組み合わせです。

ステップ1:検証リストからアイテムを選択する

検証リスト(スクリーンショットの列A)からアイテムを選択する

列CおよびDのセルに入力された数式でテキストを識別し、コンボボックスで選択された月の基準(選択された項目)を含む列から要約結果を返すことができます。

セルの範囲に検証リストを追加するには:

1.セルの範囲を選択します(前のページのスクリーンショットでは、選択したセルはA12:A15です)

2.セルA19:A23を選択します。

  1. [データ]メニューから、[検証]を選択します。

  2. [データ検証]ダイアログボックスで、[設定]タブを選択し、[許可]ボックスから[リスト]を選択します。

  3. [ソース]ボックスでF3キーを押し、リストに定義されている名前(この例ではレベル3、このヒントの最後のページのスクリーンショットを参照)を選択し、[OK]をクリックします。

ステップ2:選択したアイテムの要約残高を返す数式を入力するセルC12:C15の数式は次のとおりです。

SUMIF(DataLevel3、A12、OFFSET(DataLevel3,0、MonthSelectionNumber + 2))

セルD12:D15の数式は次のとおりです。

SUMIF(DataLevel3、A12、OFFSET(DataLevel3,0、MonthSelectionNumber + 2-12))

説明:

列CのSUMIF式は、2003年12月の列の残高を要約したものです。列DのSUMIF式は、2002年12月の列の残高を要約したものです。

SUMIF式には3つの引数があります:

最初の引数:

SUMIF式の2番目の引数に入力された基準に従って評価する範囲。この例では、範囲はDataLevel3です。これは、貸借対照表データベースの列Cに定義されている名前です。これには、現金、売掛金、棚卸資産など、貸借対照表のレベル3項目が含まれています。

2番目の引数:

基準は、検証リストLevel3から選択された項目です。

3番目の引数:

データが要約される列。これは、コンボボックスのMonthsListから選択された数値によって調整される月列のOFFSET式に従って選択されます。 OFFSET式を使用すると、選択した月を基本列(下のスクリーンショットの列C)から転用できます。

OFFSET式の動作方法

列29は2003年12月の列番号で、2002年12月の列番号は17で、これは12列前です(下のスクリーンショットを参照)。

シートの見出しのタイトルを文字から数字に変更する方法:{空} 1。 [ツール]メニューから、[オプション]を選択します。

  1. [全般]タブで、R1C1参照スタイルを確認します。

コンボボックスのドロップダウンリスト(月リスト)から2003年12月を選択すると、そのリストの月番号は24になります(これは、2002年1月から2003年12月までの月数を決定することによって計算されます:

2年* 12月= 24)。コンボボックスにリンクされたセルは、24の値を受け取ります。

データシートの列3は、SUMIF式がSUMIF式の2番目の引数の基準について評価する基本列です。

この場合、2003年12月は列Cから24+ 2 = 26(2 =列Dと列E)離れています。

3番目の引数では、Sum_rangeはベース列から26列離れている必要があります。 OFFSET数式は26の結果を返し、SUMIF数式で2003年12月の列の数値を要約します。

このヒントは、Financial Statements.xls、第2版の本から引用したものです。

スクリーンショット//パワーの組み合わせ