シート「明細シート」の内容を、複数の項目を検索値として別のシートに抽出する
INDIRECT関数と「配列数式」を使います。
添付ファイルを参照してください。
データは、シート「明細シート」の2行目から9999行目までとして数式を作成しています。
シート「集約シートA」、「集約シートB」の13行目から、25行目まで検索値を表示する数式を入力しています。
INDIRECT関数で、文字列で指定したシート名やセルのアドレスから対象のセルを参照する数式を作成します。
シート「集約シートA」、「集約シートB」いずれも同じ数式となります。
セルB1には、検索条件となる「調達方法」を入力します。
セルB2には、検索条件となる「決済日」を入力します。
まず、検索対象のシート名をセルに返すため
セルC1に、
=MID(CELL("filename",明細シート!A1),FIND("]",CELL("filename",明細シート!A1))+1,31)
と入力します。
数式の意味
対象ファイルの名前 (絶対パス名) を表す文字列の、
対象ファイルの名前 (絶対パス名) を表す文字列を検索し、検索文字列"]"の文字位置+1文字目から、
31文字の文字列を返します。
(シート名"明細シート"が返ります。)
セルD1には、セルB1と、セルB2の検索条件に合致するデータ数を返す数式を「配列数式」で、
=SUM(IF(明細シート!A2:A9999=B$1,IF(明細シート!J2:J9999=B2,1)))
と入力し、[Shift]+[Ctrl]+[Enter]で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で、{ }を入力してはいけません。
数式の意味
=SUM(IF(明細シート!A2:A9999=B$1,IF(明細シート!J2:J9999=B2,1)))
セルA13には、検索条件を、セルB1と、セルB2として、シート「明細シート」のB列のデータを行番号の小さ手順にすべて返す数式を「配列数式」で、
=IF(ROW()-12<=$D$1,INDIRECT($C$1&"!B"&SMALL(IF(明細シート!$A$2:$A$9999=$B$1,IF(明細シート!$J$2:$J$9999=$B$2,ROW(明細シート!$J$2:$J$9999))),ROW()-12)),"")
と入力し、[Shift]+[Ctrl]+[Enter]で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で、{ }を入力してはいけません。
数式の意味
もし、数式入力セルの行番号-12(この場合13-12=1)が、セル$D$1の値(この場合2)以下の場合、
セル$C$1の値(この場合"明細シート")と、
文字列"!B"と、
もし、シート「明細シート」のセル範囲$A$2:$A$9999の値が、セル$B$1と等しく、かつ、
もし、シート「明細シート」のセル範囲$J$2:$J$9999の値が、セル$B$2と等しい場合、
シート「明細シート」のセル範囲$J$2:$J$9999の行番号の、
数式入力セルの行番号-12(この場合13-12=1)番目に小さい値(この場合2)をかえします。
それ以外は、長さ0の文字列)を返します。
この場合、"明細シート!B2へのセル参照となります。
このセルをコピーして、下のセル範囲に貼り付けます。
同様に、
セルD13には、検索条件を、セルB1と、セルB2として、シート「明細シート」のI列のデータを行番号の小さ手順にすべて返す数式を「配列数式」で、
=IF(ROW()-12<=$D$1,INDIRECT($C$1&"!I"&SMALL(IF(明細シート!$A$2:$A$9999=$B$1,IF(明細シート!$J$2:$J$9999=$B$2,ROW(明細シート!$J$2:$J$9999))),ROW()-12)),"")
と入力し、[Shift]+[Ctrl]+[Enter]で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で、{ }を入力してはいけません。
数式の意味
もし、数式入力セルの行番号-12(この場合13-12=1)が、セル$D$1の値(この場合2)以下の場合、
セル$C$1の値(この場合"明細シート")と、
文字列"!I"と、
もし、シート「明細シート」のセル範囲$A$2:$A$9999の値が、セル$B$1と等しく、かつ、
もし、シート「明細シート」のセル範囲$J$2:$J$9999の値が、セル$B$2と等しい場合、
シート「明細シート」のセル範囲$J$2:$J$9999の行番号の、
数式入力セルの行番号-12(この場合13-12=1)番目に小さい値(この場合2)をかえします。
それ以外は、長さ0の文字列)を返します。
この場合、"明細シート!I2へのセル参照となります。
このセルをコピーして、下のセル範囲に貼り付けます。
同様に、
セルE13には、検索条件を、セルB1と、セルB2として、シート「明細シート」のG列のデータを行番号の小さ手順にすべて返す数式を「配列数式」で、
=IF(ROW()-12<=$D$1,INDIRECT($C$1&"!G"&SMALL(IF(明細シート!$A$2:$A$9999=$B$1,IF(明細シート!$J$2:$J$9999=$B$2,ROW(明細シート!$J$2:$J$9999))),ROW()-12)),"")
と入力し、[Shift]+[Ctrl]+[Enter]で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で、{ }を入力してはいけません。
数式の意味
もし、数式入力セルの行番号-12(この場合13-12=1)が、セル$D$1の値(この場合2)以下の場合、
セル$C$1の値(この場合"明細シート")と、
文字列"!G"と、
もし、シート「明細シート」のセル範囲$A$2:$A$9999の値が、セル$B$1と等しく、かつ、
もし、シート「明細シート」のセル範囲$J$2:$J$9999の値が、セル$B$2と等しい場合、
シート「明細シート」のセル範囲$J$2:$J$9999の行番号の、
数式入力セルの行番号-12(この場合13-12=1)番目に小さい値(この場合2)をかえします。
それ以外は、長さ0の文字列)を返します。
この場合、"明細シート!G2へのセル参照となります。
このセルをコピーして、下のセル範囲に貼り付けます。
データが表示された場合に、自動的にセルに罫線を表示するため、
セル範囲A13:F25を選択し、セルA13がアクティブの状態で、「条件付き書式の設定ダイアログボックス」の、「条件1」で、「数式が」を選択し、「=$A13<>""」と入力し、「書式」ボタンを押して、「罫線」タグを開き、「外枠」の罫線を引く用にボタンを押して、「ＯＫ」ボタンを押します。
「条件付き書式の設定ダイアログボックス」で、「ＯＫ」ボタンを押します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
「文字列関数」
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「統計関数」
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
MIN
http://kiyopon.sakura.ne.jp/kansuu/cell.html#min
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
「情報関数」
CELL
http://kiyopon.sakura.ne.jp/kansuu/cell.html#cell
を、参照してください。
連続しない複数のセルに対して順位を返す
連続しない複数のセルに対してRANK関数の範囲を指定するには、連続しない複数のセル範囲に名前を定義することで対応できます。
添付ファイルを参照してください。
具体的には、Ctrlキーを押しながら、セル範囲B1:B3,E1:E3を選択します。
「挿入」「名前」「定義」で
Excel2007以降の場合は、
「数式」「定義された名前」「名前の定義」から
「名前」を「データ」のように定義し、「参照範囲」を
B1:B3,E1:E3
として、「ＯＫ」ボタンを押します。
セルC1には、
=RANK(B1,データ)
と入力します
セルには、
2
が返ります。
このセルをコピーして下のセル範囲C2:C3に貼り付けます。
このセルをコピーして右のセル範囲E1:E3に貼り付けます。
Rakn関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
RANK
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#RANK
を、参照してください。
1ヶ月の合計を出すとき、途中で7日間空白がある場合は合計を0にする
添付ファイルを参照してください。
A列に日付が入力してあるとします。
データは、B列に入力してあるとします。
集計は、C列に返すものとします。
セルC2には、B2セの値(初期値)を返す数式
=B2
と入力します。
セルC3には、数式
=IF(C2=0,B3,IF(ROW()>7,IF(COUNTIF(OFFSET(B3,-6,0,7,1),"")>=7,0,C2+B3),C2+B3))
数式の意味
もし、セルC2の値が、0の場合、セルB3の値を返し、
それ以外は、
もし、数式入力セルの行番号(この場合3)が、7より大きい場合、
もし、セルB3のセル位置を基準として行を上に6、列を右に0の位置から、7行、1列のセル範囲の値が、""のセル数が、7以上の場合、0を返します。
それ以外はは、セルC2の値+セルB3の値を返します。
それ以外はは、セルC2の値+セルB3の値を返します。
このセルをコピーして下のセル範囲に貼り付けます。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
「検索+行列」
OFFSET
http://kiyopon.sakura.ne.jp/kansuu/address.html#offset
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
を、参照してください。
1件ごとにリスト形式のデータを同じコードのデータを1列に並べて表示させる
「配列数式」とINDIRECT関数を使います。
添付ファイルを参照してください。
まず、E列には、A列のコードの重複数をカウントする数式を入力します。
セルE2には、
=COUNTIF($A$2:$A2,$A2)
と入力します。
数式の意味
セル範囲$A$2:$A2の値が、セル$A2の値(この場合185058)と等しい場合、そのセル数を返します。
この場合1が返ります。
この値が、1のセルが重複しない値となり、リストの第一番目に表示され、2以上の場合はリストの2番目以降に表示されることになります。
このセルをコピーして下のセル範囲に貼り付けます。
セルF2には、E列のセルの値が、重複しない(1の値)セルのA列の値を返す数式を「配列数式」で
=INDIRECT("A"&SMALL(IF($E$2:$E$43=1,ROW($E$2:$E$43)),ROW()-1))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
数式の意味
文字列"A"と、
もし、セル範囲$E$2:$E$43の値が、1と等しい場合、該当のセル範囲$E$2:$E$43の行番号の
数式入力セルの行番号-1(この場合2-1=1)番目に小さい値を返し、文字列結合して"A1"形式のセル参照式とします。
この場合"A2"へのセル参照となり、185058が返ります。
このセルをコピーして下のセル範囲に貼り付けます。
セルG2には、E列のセルの値が、重複しない(1の値)セルのB列の値を返す数式を「配列数式」で
=INDIRECT("B"&SMALL(IF($E$2:$E$43=1,ROW($E$2:$E$43)),ROW()-1))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
数式の意味
文字列"B"と、
もし、セル範囲$E$2:$E$43の値が、1と等しい場合、該当のセル範囲$E$2:$E$43の行番号の
数式入力セルの行番号-1(この場合2-1=1)番目に小さい値を返し、文字列結合して"A1"形式のセル参照式とします。
この場合"B2"へのセル参照となり、Aが返ります。
このセルをコピーして下のセル範囲に貼り付けます。
セルH2には、E列のセルの値が、重複しない(1の値)セルのC列の値を返す数式を「配列数式」で
=INDIRECT("C"&SMALL(IF($E$2:$E$43=1,ROW($E$2:$E$43)),ROW()-1))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
数式の意味
文字列"C"と、
もし、セル範囲$E$2:$E$43の値が、1と等しい場合、該当のセル範囲$E$2:$E$43の行番号の
数式入力セルの行番号-1(この場合2-1=1)番目に小さい値を返し、文字列結合して"A1"形式のセル参照式とします。
この場合"C2"へのセル参照となり、5/11が返ります。(セルの書式設定で「日付」の表示形式にします)
このセルをコピーして下のセル範囲に貼り付けます。
セルI2には、E列のセルの値が、重複しない(1の値)セルのD列の値を返す数式を「配列数式」で
=INDIRECT("D"&SMALL(IF($E$2:$E$43=1,ROW($E$2:$E$43)),ROW()-1))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
数式の意味
文字列"D"と、
もし、セル範囲$E$2:$E$43の値が、1と等しい場合、該当のセル範囲$E$2:$E$43の行番号の
数式入力セルの行番号-1(この場合2-1=1)番目に小さい値を返し、文字列結合して"A1"形式のセル参照式とします。
この場合"D2"へのセル参照となり、500が返ります。
このセルをコピーして下のセル範囲に貼り付けます。
セルJ2には、E列のセルの値が、2のセルのC列の値を返す数式を「配列数式」で
=IF(COUNTIF($A$2:$A$43,$F2)>=2,INDIRECT("C"&MIN(IF($E$2:$E$43=2,IF($A$2:$A$43=$F2,ROW($E$2:$E$43))))),"")
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$2:$A$43の値が、セル$F2の値と同じ場合、そのセル数を返し、その値が2以上の場合、
文字列"C"と、
もし、セル範囲$E$2:$E$43の値が、2と等しい場合、かつ、
もし、セル範囲$A$2:$A$43の値が、セル$F2の値と等しい場合、
該当のセル範囲$E$2:$E$43の行番号の最小値を返し、文字列結合して"A1"形式のセル参照式とします。
それ以外は、長さ0の文字列を返します。
このセルをコピーして下のセル範囲に貼り付けます。
セルK2には、E列のセルの値が、2のセルのD列の値を返す数式を「配列数式」で
=IF(COUNTIF($A$2:$A$43,$F2)>=2,INDIRECT("D"&MIN(IF($E$2:$E$43=2,IF($A$2:$A$43=$F2,ROW($E$2:$E$43))))),"")
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$2:$A$43の値が、セル$F2の値と同じ場合、そのセル数を返し、その値が2以上の場合、
文字列"D"と、
もし、セル範囲$E$2:$E$43の値が、2と等しい場合、かつ、
もし、セル範囲$A$2:$A$43の値が、セル$F2の値と等しい場合、
該当のセル範囲$E$2:$E$43の行番号の最小値を返し、文字列結合して"A1"形式のセル参照式とします。
それ以外は、長さ0の文字列を返します。
このセルをコピーして下のセル範囲に貼り付けます。
セル範囲J2:K22をコピーして、
セル範囲M2:S22に貼りつけます。
セル範囲L2:M2を選択し、3番目の値を返すため、[Ctrl]キーを押しながら{H}キーを押して、「検索と置換」ダイアログボックスで、
「検索する文字列」に「=2」、
「置換後の文字列」に「=3」と入力し、「すべて置換」ボタンを押します。
セル範囲N2:O2を選択し、4番目の値を返すため、[Ctrl]キーを押しながら{H}キーを押して、「検索と置換」ダイアログボックスで、
「検索する文字列」に「=2」、
「置換後の文字列」に「=4」と入力し、「すべて置換」ボタンを押します。
セル範囲P2:Q2を選択し、5番目の値を返すため、[Ctrl]キーを押しながら{H}キーを押して、「検索と置換」ダイアログボックスで、
「検索する文字列」に「=2」、
「置換後の文字列」に「=5」と入力し、「すべて置換」ボタンを押します。
セル範囲R2:S2を選択し、6番目の値を返すため、[Ctrl]キーを押しながら{H}キーを押して、「検索と置換」ダイアログボックスで、
「検索する文字列」に「=2」、
「置換後の文字列」に「=6」と入力し、「すべて置換」ボタンを押します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
「文字列関数」
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「統計関数」
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
MIN
http://kiyopon.sakura.ne.jp/kansuu/cell.html#min
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を、参照してください。
D列の値がC列の値より大きい場合○、小さい場合×、同じなら-D列の値が0の場合*とE列に表示したい
IF関数を使います。
添付ファイルを参照してください。
セルE4には、
=IF(D4=0,"*",IF(D4>C4,"○",IF(D4<C4,"?","-")))
と入力します。
数式の意味
もし、セルD4の値が、0の場合、"*"を返します。
それ以外は、
もし、セルD4の値が、セルC4の値より大きい場合、"○"
もし、セルD4の値が、セルC4の値より小さい場合、"?"
それ以外は、"-"を返します。
IF関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を、参照してください。
1行おき・1列おきに合計を出す
参考になる質問が「計算式・関数」「2004/11/16」
「２行おきの足算について」にあります。
他の方法として、
私の作成した「拡張SUMユーザー定義関数」
を使えば1行おきや2行おきの足し算が簡単にできます。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/sumx.htm
「拡張SUMユーザー定義関数」を使う場合は、必ずアドインソフトを使う場合の注意点
http://kiyopon.sakura.ne.jp/soft/addin.html
をお読みください。
ご質問の場合は次のようになります。
添付ファイルを参照してください。
セルH3には、セル範囲B3:G3の偶数行列のセルの値を合計する数式を「配列数式」で、
=SUM(IF(MOD(COLUMN(B3:G3),2)=0,B3:G3))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛｝でくくられます。自分で｛｝を入力してはいけません。
数式の意味
もし、セル範囲B3:G3の列番号を2で割った余りが、0の場合、該当のセル範囲B3:G3の値を合計します。
セルには、
7
が返ります。
同様に、
セルI3には、セル範囲B3:G3の奇数行列のセルの値を合計する数式を「配列数式」で、
=SUM(IF(MOD(COLUMN(B3:G3),2)=1,B3:G3))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛｝でくくられます。自分で｛｝を入力してはいけません。
数式の意味
もし、セル範囲B3:G3の列番号を2で割った余りが、1の場合、該当のセル範囲B3:G3の値を合計します。
セルには、
12
が返ります。
セルL3には、セル範囲H3:H18の奇数行のセルの値を合計する数式を「配列数式」で、
=SUM(IF(MOD(ROW(H3:H18),2)=1,H3:H18))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛｝でくくられます。自分で｛｝を入力してはいけません。
数式の意味
もし、セル範囲H3:H18の行番号を2で割った余りが、1の場合、該当のセル範囲H3:H18の値を合計します。
セルには、
15
が返ります。
同様に、
セルM3には、セル範囲I3:I18の奇数行のセルの値を合計する数式を「配列数式」で、
=SUM(IF(MOD(ROW(I3:I18),2)=1,I3:I18))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛｝でくくられます。自分で｛｝を入力してはいけません。
数式の意味
もし、セル範囲I3:I18の行番号を2で割った余りが、1の場合、該当のセル範囲I3:I18の値を合計します。
セルには、
30
が返ります。
「配列数式」は結合したセルでは入力できませんので、セル範囲L4:M4のセル結合を解除します。
セル範囲L4:M4のセルの中央に値を表示するため、
セル範囲L4:M4を選択し、「書式」の「配置」で、「横位置」の「選択範囲で中央」として「ＯＫ」ボタンを押します。
セルL4には、セル範囲H3:H18の偶数行のセルの値を合計する数式を「配列数式」で、
=SUM(IF(MOD(ROW(H3:H18),2)=0,H3:H18))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛｝でくくられます。自分で｛｝を入力してはいけません。
数式の意味
もし、セル範囲H3:H18の行番号を2で割った余りが、1の場合、該当のセル範囲H3:H18の値を合計します。
セルには、
45
が返ります。
COLUMN関数の説明は、「関数の使い方説明」
の「検索+行列」の「COLUMN」
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
ROW関数の説明は、「関数の使い方説明」
の「検索+行列」の「ROW」
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
MOD関数の説明は、「数学+三角」の「MOD」
http://kiyopon.sakura.ne.jp/kansuu/abs.html#mod
IF関数の説明は、「論理関数」の「IF」
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を参照してください。
あらかじめA=5、B=6、C=7と認識させてA+B+Cの合計を出したい
参考になる質問が「計算式・関数」「2005/6/19」
「記号を数字に認識させて、記号同志の足し算をしたい。例えば○が1、△が0.5、合計1.5」にあります。
その内容
名前の定義を使います。
同じ質問が「質問と回答」の「計算式・関数」「2002/3/16」
「特定の文字をある数値として計算させたい」にあります。
ご質問の場合は、
「挿入」「名前」「定義」を選択し、「名前の定義」ダイアログボックスで、名前の入力欄に○と入力し、「参照範囲」の入力欄に数値1を入力し{ＯＫ}ボタンを押します。
「挿入」「名前」「定義」を選択し、「名前の定義」ダイアログボックスで、名前の入力欄に△と入力し、「参照範囲」の入力欄に数値0.5 を入力し{ＯＫ}ボタンを押します。
数式は、
=○+△
と入力します。
あるいは、
「計算式・関数」「2002/3/16」
「特定の文字をある数値として計算させたい」にあります。
その内容
Ａを５として定義する場合。
「挿入」「名前」「定義」を選択し、「名前の定義」ダイアログボックスを表示します。
名前の入力欄にＡと入力し、「参照範囲」の入力欄に数値５を入力し{ＯＫ}ボタンを押します。
これで、=Aとすれば５が表示され、文字Ａ＝数値５となります。
この場合、数式バーには=Aと表示され、セルの表示は５となります。
ご質問の場合
列や行を意味するRやC(小文字のr,cも含む)は予約語となっていますので使用できません。
Cでは定義できませんので
"_C"
としました。
添付ファイルを参照してください。
セルにシート名を表示する
参考になる質問が「質問と回答」http://kiyopon.sakura.ne.jp/situmon/index.htm
の「VBA・その他」「2007/02/18」
「セル値をシート名またはファイル名にする」にあります。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/cellname.htm
注意:「アドインソフト」または「ユーザー定義関数」を利用する場合は、「アドインソフトを使う場合の注意点」
http://kiyopon.sakura.ne.jp/soft/addin.html
を、必ずお読みください。
または、
「表示・書式」「2003/1/2」「シート名をセルに表示させる」にあります。
その内容
CELL関数を使います。
CELL("filename")は、ファイルの名前 (絶対パス名) を返します。
次のような書式でファイルの名前が帰ります。
絶対パス名[ファイル名]シート名
ファイル名からシート名のみを取り出すには、FIND関数でファイル名から、文字列"]"の位置を検索し、MID関数で"]"より右側 の文字列を取り出します。
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)
ファイルがまだ一度も保存されていない場合は、#VALUE!が帰ります。
ファイルの名前（保存先パス）を表示したい
http://kiyopon.sakura.ne.jp/situmon/kaitou/index07.htm#%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E5%90%8D%E5%89%8D%EF%BC%88%E4%BF%9D%E5%AD%98%E5%85%88%E3%83%91%E3%82%B9%EF%BC%89%E3%82%92%E8%A1%A8%E7%A4%BA%E3%81%97%E3%81%9F%E3%81%84
セルの値が入力してある右端の列番号を返す数式
「配列数式」を使います。
添付ファイルを参照してください。
対象のセル範囲で、セルの値が、0以外のセルの右端の列番号を返す数式
ただし、セルに数式が入力してある場合には予期しない結果となります。
セル範囲A2:I2の値が0以外のセルの列番号の最大値をかえすため、
セルM2には、
=MAX(IF($A2:$L2<>0,COLUMN($A2:$L2)))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛ ｝でくくられます。自分で｛ ｝を入力してはいけません。
数式の意味
もし、セル範囲、$A2:$L2の値が、0以外の場合該当のセル範囲$A2:$L2の列番号の最大値を返します。
この数式を配列数式で入力する理由は、
この数式を配列数式にすると、
複数のセルの列番号が配列で返ります。
IF($A2:$L2<>0,COLUMN($A2:$L2))
の数式で
例えば、A2,B2,D2,E2,H2の値が、0以外の場合
A,B,D,E,H
の列番号が、
1,2,4,5,8
という「配列」で返ります。
「配列」とは、1つの値ではなく複数の値を返すことが可能です。
MAX関数で、
1,2,4,5,8
の最大値を返すと
8
となります。
またセル範囲$A3:$L3の値が、""以外のセルの列番号の最大値を返す場合
セルM3には、
=MAX(IF($A3:$L3<>"",COLUMN($A3:$L3)))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛ ｝でくくられます。自分で｛ ｝を入力してはいけません。
数式の意味
もし、セル範囲、$A3:$L3の値が、0以外の場合該当のセル範囲$A3:$L3の列番号の最大値を返します。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索・行列」
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
「統計関数」
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を、参照してください。
複数セルの文字列を1つのセルに結合して表示する
ワークシート関数では、文字列結合関数[&]を使います。
具体的には、
B3セルにA3:A10のセルの文字列を結合して表示するには
セルB3に
=A3&A4&A5&A6&A7&A8&A9&A10
と入力します。
文字列結合関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
CONCATENATE
&
http://kiyopon.sakura.ne.jp/kansuu/val.html#concatenate
を、参照してください。
この方法ですと、対象のセル範囲が増減するたびに(例えばセル範囲A3:A20を文字列結合する場合)
(=A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A15&A16&A17&A18&A19&A20のように)
数式を変更しなければなりませんから面倒です。
「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に、私の作成した「セル結合」ユーザー定義関数があります。
選択セル範囲の文字列を指定の区切り文字で結合します。
対象のセル範囲にデータがない場合も、指定の区切り文字でセル範囲がセル結合されます。
区切り文字を"^"とすれば、セル内改行します。
区切り文字の既定値は""(文字無し)です。
引数[空白セル]に0を指定すると、空白セルを飛ばしてセルの文字を結合します。
通常の組み込み関数と同じようにワークシート上で使用できる関数です。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/cellketugo.html
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/cellketugo.exe
注意:「アドインソフト」または「ユーザー定義関数」を利用する場合は、「アドインソフトを使う場合の注意点」
http://kiyopon.sakura.ne.jp/soft/addin.html
添付ファイルを参照してください。
添付ファイルには、「セル結合」ユーザー定義関数が張り付けてあります。
ファイルには、マクロが記述してありますので、ファイルを開く際には「マクロ」を「有効」にしてください。
セルB4には、
=セル結合(A3:A10)
と入力します。
㈱を削除してふりがなを返す
SUBSTITUTE関数で、文字列
"㈱"
"(株)"
"(財)"
を、長さ0の文字列に置換します。
セルA1に文字列
㈱花村
と入力してあるとします。
セルB1には、
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PHONETIC(A1),"㈱",""),"(株)",""),"(財)","")
と入力します。
SUBSTITUTE関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「文字列関数」
SUBSTITUTE
http://kiyopon.sakura.ne.jp/kansuu/val.html#substitute
を、参照してください。
★マークの付いた項目名の日を工事日セルに表示させる方法
添付ファイルを参照してください。
「配列数式」を使います。
C6には、セル範囲$D6:$J9の文字列が"★"の場合、該当セル範囲D$4:J$4の最小値を返す数式を「配列数式」で、
=MIN(IF($D6:$J9="★",D$4:J$4))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛ ｝でくくられます。自分で｛ ｝を入力してはいけません。
数式の意味
もし、セル範囲$D6:$J9の値が、"★"と等しい場合、該当のセル範囲D$4:J$4の値の最小値を返します。
このセルをコピーして下のセル範囲に貼り付けます。
7項目で1行のデータリストから，項目ごとに2列分の帳票に表示する
添付ファイルを参照してください。
シート「データ」のデータを、シート「帳票」に、該当するデータを表示することにします。
INDIRECT関数と、INT関数、ROW関数MOD関数を使い、数式入力セルの行番号からセル参照式を作製します。
まず、7行毎に参照する行を2行移動する数式
INT((ROW()-1)/7)*2+2
数式の意味
数式入力セルの行番号-1を、7で割った値の整数値に2を掛け、+2した値
=INT(1/7)*2+2
=INT(0.1428)*2+2
=0*2+2
=0+2
が返ります。
この数式で、7行目までは、2が返り、8行目から14行目までは、
=INT(8-1)/7*2+2
=INT(7/7)*2+2
=1*2+2
=2+2
=4
数式入力セルの行番号から、1,2,3,4,5,6,7を返す数式
IF(MOD(ROW(),7)=0,7,MOD(ROW(),7))
数式の意味
もし、数式入力セルの行番号を7で割った余りが、0の場合(7の倍数の場合)は、7を返し、
それ以外は、
数式入力セルの行番号を7で割った余りを返します。
この数値をそれぞれ行番後と、列番号としてINDIRECT関数で数式に組み込み
"R1C1"形式のセル参照式とします。
Rは行、Cは列を示します。
R1C1は、行番号1で、列番号が1のセルで、セルA1のことです。
R1C2は、行番号1で、列番号が2のセルで、セルB1のことです。
シート「帳票」のセルB1には、シート「データ」の行番号2,列番号1のセル参照式を返す数式
=INDIRECT("データ!R"&INT((ROW()-1)/7)*2+2&"C"&IF(MOD(ROW(),7)=0,7,MOD(ROW(),7)),FALSE)
と入力します。
数式の意味
文字列"データ!R"と、
数式入力セルの行番号-1(この場合1-1=0)を7で割り整数値にした値を2倍した値に、2を加算した値(この場合0+2=2)
と、文字列"C"と、
もし数式入力セルの行番号(この場合1)を、7で割った余りが、0の場合、7を返し、
それ以外は、数式入力セルの行番号(この場合1)を、7で割った余りを返し、(この場合1)
"R1C1"形式のセル参照式とします。
この場合"データR2C1"("データA1"のこと)へのセル参照となります。
このセルをコピーして下のセル範囲B2:B42に貼り付けます。
同様にセルD1にはシート「データ」の行番号3,列番号1のセル参照式を返す数式
=INDIRECT("データ!R"&INT((ROW()-1)/7)*2+3&"C"&IF(MOD(ROW(),7)=0,7,MOD(ROW(),7)),FALSE)
と入力します。
数式の意味
文字列"データ!R"と、
数式入力セルの行番号-1(この場合1-1=0)を7で割り整数値にした値を2倍した値に、3を加算した値(この場合0+3=3)
と、文字列"C"と、
もし数式入力セルの行番号(この場合1)を、7で割った余りが、0の場合、7を返し、
それ以外は、数式入力セルの行番号(この場合1)を、7で割った余りを返し、(この場合1)
"R1C1"形式のセル参照式とします。
この場合"データR3C1"("データA1"のこと)へのセル参照となります。
このセルをコピーして下のセル範囲C2:C42に貼り付けます。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
を、参照してください。
マイナスの時間入力と、計算結果のマイナスの時間表示をしたい
マイナスの時間(例えば -8:00)をセルに入力すると、セルにマイナスの時刻は入力できず「注意」メッセージのダイアログボックスが「入力した数式は正しくありません。」と表示されます。
また、
=-"8:00"
とセルに入力すると
「負の日付または時間は####と表示されます。」
と、注意表示がされます。
添付ファイルを参照してください。
ご質問の場合は、
セルA2に、
2012/3/16
と入力してありますから、
「ツール」「オプション」（エクセル2007の場合は、「Office ボタン」「Excel のオプション」）の
「計算方法」タグの「ブックオプション」グルーブで、「1904年から計算する」にチェツクを入れ、「ＯＫ」ボタンを押します。
すると、
2012/3/16
と入力された日付が
2016/3/17
となってしまいます。
セルA2に、
2012/3/16
と入力し直します。
セルB3への、マイナスの時刻の入力は、
=-"8:00"
とします。
時刻入力セル範囲B2:B10を選択し「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「h:mm;-h:mm」と入力し、「OK」ボタンを押します。
セルには、
-8:00
と表示されます。
セルC3の計算式
=IF(B2<>"",SUM(B$2:B2),"")
の計算結果は
-7:00
となります。
勝率により順位を自動で並び替えて表示する
添付ファイル を参照してください。
順位を自動計算することは、RANK関数で求めます。
シート「入力」
シート「入力」の
セルA3には、セルG3の勝率から順位を返す数式
=RANK(G3,$G$3:$G$8)
と入力します。
数式の意味
セルG3の値(この場合0.75)が、セル範囲$G$3:$G$8の何番目に大きいかを返します。
(この場合1が返ります。)
セルに、
1位
と表示するため、セルを選択し「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「#位」と入力し、「OK」ボタンを押します。
このセルをコピーして下のセル範囲に貼り付けます。
シート「順位」
シート「入力」のセル範囲A3:I8のデータから自動的に並べ替えするには、INDIRECT関数と「配列数式」で
別のセル範囲A12:I17にデータを表示することになります。
セルA3:A8には、順位の数値を
1,2,3,4,5,6
と入力します。
セルに、
1位
と表示するため、セルを選択し「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「#位」と入力し、「OK」ボタンを押します。
このセルをコピーして下のセル範囲に貼り付けます。
シート「順位」のセルB3には、自動的に順位のチーム名を返す数式を「配列数式」で
=INDIRECT("入力!R"&MIN(IF(入力!$A$3:$A$8=$A3,ROW(入力!$A$3:$A$8)))&"C"&COLUMN(),FALSE)
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛ ｝でくくられます。自分で｛ ｝を入力してはいけません。
数式の意味
文字列"R"と、もし、シート「入力」のセル範囲$A$3:$A$8の値が、セル$A$3の値(この場合1)と等しい場合は、シート「入力」の該当のセル範囲$A$3:$A$8の行番号の最小値(この場合3)を返し、
文字列"C"と、数式の意味入力セルの列番号(この場合2)を文字列結合し、R1C1"形式のセル参照式とします。
この場合"入力!R3C2"(シート「入力」のセルB3)へのセル参照となり「広島」が返ります。
このセルをコピーして下のセル範囲に貼り付けます。
このセルをコピーして右のセル範囲に貼り付けます。
それぞれの関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
「統計関数」
MIN
http://kiyopon.sakura.ne.jp/kansuu/cell.html#min
を、参照してください。
常に最新の7回のデータのみを集計する
添付ファイルを参照してください。
INDIRECT関数と、配列数式を使います。
セルH2には、C列のデータが入力してあるセルの行番号の最大値を返す数式を「配列数式」で
=MAX(IF(C8:C65536<>"",ROW(C8:C65536)))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、｛ ｝でくくられます。自分で｛ ｝を入力してはいけません。
数式の意味
もし、セル範囲C8:C65536(Excel2003の最終行数まで)のセル値が、長さ0の文字列以外の場合、該当のセル範囲C8:C65536の行番号の最大値を返します。
(この場合58が返ります。)
コレで、最新データの行番号が取り出せました。
F2にはデータ数7
セルG2には、セルH2の数よりセルF2の数(この場合7)少ない値を返す数式
=H2-(F2-1)
と入力します。
コレで、最新の7つのセルセル番地を取得するための最小セル行番号と最大セル行番号が取得できました。
セルG3には、セルG2と、セルH2の数値から、C列からG列のセル範囲を文字列で返す数式
="$C$"&$G$2&":$G$"&$H$2
と入力します。
数式の意味
文字列"$C$"と、セル$G$2の値(この場合52)と、文字列":$G$"と、セル$H$2の値(この場合58)文字列結合関数&で結合します。
セルには、文字列"$C$52:$G$58"が返ります。
同様に、
セルG4には、セルG2と、セルH2の数値から、C列からH列のセル範囲を文字列で返す数式
="$C$"&$G$2&":$H$"&$H$2
と入力します。
数式の意味
文字列"$C$"と、セル$G$2の値(この場合52)と、文字列":$H$"と、セル$H$2の値(この場合58)文字列結合関数&で結合します。
セルには、文字列"$C$52:$H$58"が返ります。
セルL3には、セルG3の文字列(この場合"$C$52:$G$58")から、INDIRECT関数で、セル参照式を組み合わせた数式を
=COUNTIF(INDIRECT($G3),L$2)
と入力します。
数式の意味
セル$G3の値(この場合"$C$52:$G$58")から、A1形式のセル参照式として、セルL$2の値と同じセルの数を返します。
(この場合該当値がありませんから0が返ります。)
このセルをコピーして右のセル範囲(M3:AP3)に貼り付けます。
セル範囲L3:AP3をコピーして、下のセル範囲L4:AP4に貼りつけます。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
「統計関数」
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
を、参照してください。
関数を使って並べ替えをしたい
添付ファイルを参照してください。
並べ替えには、データがすべて数値となっている必要があります。
A列の「担当社員コード」が文字列ですので、CODE関数で、文字列から数値に変換します。
エクセルで数値として扱える桁数は15桁までで(16桁以上の桁の数値はすべて0として扱われます)すので、一部のデータ(この場合「納品伝票番号」)は、下3桁としています。
「担当社員コード」6桁+「売上先コード」5桁+「納品伝票番号」下3桁+「行番号」1桁の合計15桁を、並べ替えのデータとします。
桁数は、
=6+5+3+1
=15桁
となります。
まず、A列の「担当社員コード」が文字列となっていますので、CODE関数で文字コードを数値とします。
「担当社員コード」は文字列と数値の混在で3桁とします。
例えば、
セルA1の文字列"M34"
の場合、1文字づつの文字コードはそれぞれ
77
51
52
となります。
この文字コード毎に、10の4乗,10の2乗,10の0乗を掛け算して数値とします。
775152
6桁の数値となります。
その後ろに、
「売上先コード」が5桁
「納品伝票番号」の右3桁
「行番号」が1桁
を、それぞれ文字列結合関数(&)で結合し、
更に、VALUE関数で数値に変換します。
セルF2には、次の数式
=VALUE((MMULT(CODE(MID(A2,{1,2,3},1)),10^{4;2;0})&TEXT(B2,"00000")&RIGHT(C2,3)&D2))
と入力します。
数式の意味
セルA2の、1,2,3文字目からそれぞれ1)文字取り出し(この場合"M","3","4")、それぞれの文字コードを配列で(この場合{77,51,52})返します。
その値に、それぞれ、10の4乗、2乗、0乗を掛け算した値を返します。(この場合775152が返ります。)
セルB2の値(この場合418)を文字書式"00000"(先頭0の5桁)で返します。(この場合"00418")
セルC2の(61000061)の右から、3桁を文字列で、(この場合"061")返します。
セルD2の値(この場合1)を返します。
すべてを文字列結合します。
(この場合"775122004180611"が返ります。)
文字列を数値に変換します。
(この場合775122004180611が返ります。)
このセルをコピーして下のセル範囲F3:F10に貼り付けます。
F列にA列からD列のデータがすべて15桁の数値に置き換えられた値が返ります。
セルG2には、
セル範囲F2:F10の値を小さい順の順位を返す数式を入力します。
この場合、
検索するセル範囲は、絶対参照で、セル範囲$F$2:$F$10
返すデータ範囲は、相対参照で、A2:A10
順番は、数式入力セルの行番号-1となります。
データの列(F列)や、抽出するデータ範囲(A2:A10)が変更されたり、数式入力セルの行(2)が変わると正しく結果が反映されませんので注意してください。
セルG2には、データの順番を返す数式
=MATCH(SMALL($F$2:$F$10,ROW()-1),$F$2:$F$10,0)
と入力します。
数式の意味
セル範囲$F$2:$F$10の値の数式入力セルの行番号-1(この場合2-1=1)番目に小さい値を返し、
セル範囲$F$2:$F$10の何番目にあるかを返します。
(この場合3が返ります)
このセルをコピーして下のセル範囲G3:G10に貼り付けます。
セルH2には、データ範囲A2:A10の値のうち、セルL2の順番のデータを返す数式を入力します。
セルH2には、
=INDEX(A$2:A$10,$G2)
と入力します。
数式中で、注意する点は
最初の参照先セル範囲
A$2:A$10
は、行が固定の複合参照で、
右(列)にコピーすると、B$2:B$10
のように列番号が自動拡張し
下(行)にコピーしてもA$1:A$10で、行は固定のままとなります。
次の参照先セル
$G2は、列が固定の複合参照で、
右(列)にコピーしても、$G2で、列は固定のまま
下(行)にコピーすると、$G3
のように、行番号が自動拡張します。
数式の意味
セル範囲A2:A10の上から、行番号がセル$G2の値(この場合3)番目のセル値を返します。
この場合セルA2から3行目のセルA4の値、M30が返ります。
このセルをコピーして下のセル範囲H3:H10に貼り付けます。
セル範囲H2:H10をコピーして、右のセル範囲I2:K10に貼付けします。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
MMULT
http://kiyopon.sakura.ne.jp/kansuu/abs.html#MMULT
「統計関数」
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
「検索+行列」
INDEX
http://kiyopon.sakura.ne.jp/kansuu/address.html#index
MATCH
http://kiyopon.sakura.ne.jp/kansuu/address.html#match
「文字列関数」
VALUE
http://kiyopon.sakura.ne.jp/kansuu/val.html#value
CODE
http://kiyopon.sakura.ne.jp/kansuu/val.html#code
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
RIGHT
http://kiyopon.sakura.ne.jp/kansuu/val.html#right
&
http://kiyopon.sakura.ne.jp/kansuu/val.html#concatenate
を、参照してください。
セル毎に、昭和 ３３ １０ ２０ を別のセルに西暦年月日で、1958/10/20に変換する
参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
の「質問と回答」
の「計算式・関数」「2001/12/19」
「年、月、日の文字列を日付にする」にあります。
ご質問の場合は、次のようになります。
A B C D E 1 昭和 33 10 20 1958/10/20
元号が文字列で入力してありますので、DATEVALUE関数を使います。
セル
A1,B1,C1,D1に、それぞれ
昭和
33
10
20
と入力してあるとします。
セルE1には、
=DATEVALUE(A1&B1&"年"&C1&"月"&D1&"日")
と入力します。
数式の意味
セルA1の文字列(この場合"昭和")と、セルB1の値(この場合33)と、文字列"年"と、セルC1の値(この場合10)と、文字列"月"と、セルD1の値(この場合20)と、文字列"日"を文字列結合し、日付文字列を日付シリアル値に変換します。
セルには、日付シリアル値の
21478
が返ります。
セルに西暦の日付表示
1958/10/20
を表示するため、セルを選択し「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「日付」を選択し「種類」欄で「*2001/3/14」を選択し、「OK」ボタンを押します。
DATEVALUE関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「日付+時刻」
DATEVALUE
http://kiyopon.sakura.ne.jp/kansuu/date.html#datevalue
を、参照してください。
3ケタは切り捨て、なおかつ金額が50万以上のものは500,000と表示されるようにする
IF関数と、ROUNDDOWN関数を使います。
A1セルに、数値が
582,600
と入力してあるとします。
セルB1に、
=IF(A1>=500000,500000,ROUNDDOWN(A1,-3))
と入力します。
数式の意味
もし、セルA1の値が、500000以上の場合は、500000を返し、
それ以外は、
セルA1の値を3桁目(1,000の位)で切り捨てた値を返します。
この場合
50,000
が返ります。
それぞれの関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「丸め」
ROUNDDOWN
http://kiyopon.sakura.ne.jp/kansuu/round.htm#rounddown
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を、参照してください。
複数のセル内の文字を一つのセルに表示するには
文字列結合関数 &(アンパサンド)を使います。
具体的には、
セル範囲A1:A5の文字列を結合して
セルB1に表示する場合、
=A1&A2&A3&A4&A5
と入力します。
文字列結合関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「文字列関数」
CONCATENATE
&
http://kiyopon.sakura.ne.jp/kansuu/val.html#concatenate
を、参照してください。
また、
「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に「セル結合」ユーザー定義関数があります。
選択セル範囲の文字列を指定の区切り文字で結合します。
対象のセル範囲にデータがない場合も、指定の区切り文字でセル範囲がセル結合されます。
区切り文字を"^"とすれば、セル内改行します。
区切り文字の既定値は""(文字無し)です。
引数[空白セル]に0を指定すると、空白セルを飛ばしてセルの文字を結合します。
通常の組み込み関数と同じようにワークシート上で使用できる関数です。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/cellketugo.html
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/cellketugo.exe
注意:「アドインソフト」または「ユーザー定義関数」を利用する場合は、「アドインソフトを使う場合の注意点」
http://kiyopon.sakura.ne.jp/soft/addin.html
を、必ずお読みください。
セル内に入力した文字を別のセルの文章内に入力する方法
添付ファイルを参照してください。
文字列結合関数アンパサンドを使います。
具体的には、
A1セルに、文字列
愛
A2セルには、
="あなたのこころのなかに"&A1&"があります。"
と入力します。
文字列結合関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
CONCATENATE
&
http://kiyopon.sakura.ne.jp/kansuu/val.html#concatenate
を、参照してください。
生年月日から年齢を返す
本日の満年齢を返す場合
セルA1に生年月日を入力します。
例s50/4/6
満年齢を表示させるセルに次の式を入力します。
=DATEDIF(A1,TODAY(),"Y")
セルには、
36
が返ります。
本年の4/1現在の満年齢を返す場合
現在日付の替わりに基準日を入力すれば、基準日までの年数が返ります。
=DATEDIF(A1,DATE(YEAR(TODAY()),4,1),"Y")
セルには、
35
が返ります。
関数の説明
TODAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#today
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
を、参照してください。
住所内の町名のフリガナを表示したい
エクセルの関数で、セルの文字列のふりがなを返すには、
PHONETIC関数を使います。
しかし、この関数では、文字列を関数などで切り取って取り出した場合
(RIGHT関数などで文字列の一部を取り出した場合など)には、機能しません。
「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に、私の作成した「「カタカナ」ユーザー定義関数」があります。
文字列のふりがなを「ひらがな」または「カタカナ」で返します。
「ひらがな」と「カタカナ」という２つの関数
を、同じファイル名kana.exeでユーザー定義関数として登録してあります。
１つの登録でどちらの関数も利用できます。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/kana.htm#%E3%80%8C%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A%E3%80%8D%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%AE%9A%E7%BE%A9%E9%96%A2%E6%95%B0%E3%81%AE%E8%AA%AC%E6%98%8E
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/kana.exe
添付ファイルを参照してください。
ファイルには、マクロが記述してありますので、ファイルを開く際には「マクロ」を「有効」にしてください。
添付ファイルには、「カタカナ」ユーザー定義関数が添付してあります。
ファイルを開く際に、「マクロ」を「有効」にしてください。
RIGHT関数で、文字列"市"を区切りとして、その次の文字列以降の文字列を取り出し、「カタカナ」ユーザー定義関数でフリガナを返しています。
セルB2には、
=IF(C2="","",カタカナ(RIGHT(C2,LEN(C2)-FIND("市",C2))))
と入力します。
数式の意味
もし、セルC2に何も入力してない場合は、長さ0の文字列を返します。
それ以外は、
セC2の文字列("茨城県水戸市青柳町")の左から、セルC2の文字数(この場合9)から
セルC2の文字列の中から、文字列"市"を検索た文字位置(この場合6)を引いた文字数(この場合9-6=3)文字取り出し
(この場合"青柳町")ユーザー定義関数「カタカナ」で、フリガナを返します。
この場合"アオヤギチョウ"が返ります。
このセルをコピーして下のセル範囲に貼り付けます。
もし、フリガナ((この場合"アオヤギチョウ"）を半角(この場合"アオヤギチョウ"）で表示したいばあいは、
セルB2の数式を
=IF(C2="","",ASC(カタカナ(RIGHT(C2,LEN(C2)-FIND("市",C2)))))
とします。
(セルD2以降に入力してあります)
注意:「アドインソフト」または「ユーザー定義関数」を利用する場合は、「アドインソフトを使う場合の注意点」
http://kiyopon.sakura.ne.jp/soft/addin.html
を、必ずお読みください。
○を25,△を15として合計値を計算する
○を25
△を15
として計算するのですね。
COUNTIF関数で、
対象のセル範囲の○の数を計算し、*25
対象のセル範囲の△の数を計算し、*15
この合計を計算します。
添付ファイルを参照してください。
シート[Sheet2]の
セルB2には、
=COUNTIF(Sheet1!C3:C6,"○")*25+COUNTIF(Sheet1!C3:C6,"△")*15
と入力します。
数式の意味
シート「Sheet1」のセル範囲C3:C6の値が、"○"のセル数を返し(この場合1)、25倍します。
(この場合25が返ります。)
次の値を加算します。
シート「Sheet1」のセル範囲C3:C6の値が、"△"のセル数を返し(この場合2)、15倍します。
(この場合30が返ります。)
(この場合合計55が返ります。)
COUNTIF関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を、参照してください。
(1235)を1の位で四捨五入し、さらに10位で四捨五入した値(1200)を返す2回の四捨五入をすればいいのです。
具体的には、
A1セルに、数値
1235
が入力してあるとします。
セルB1に、数式
=ROUND(ROUND(A1,-1),-2)
数式の意味
セルA1の値を、1の位で四捨五入し、その値を10の位で四捨五入した値を返します。
ROUND関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
[丸め」
ROUND
http://kiyopon.sakura.ne.jp/kansuu/round.htm#round
を、参照してください。
A列の空白の行を詰めてB列に表示する方法
添付ファイルを参照してください。
INDIRECT関数と、［配列数式］を使います。
セルB1には、配列数式で、
=IF(ROW()>COUNTA($A$1:$A$7),"",INDIRECT("A"&SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)),ROW())))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、数式入力セルの行番号(この場合1)が、セル範囲$A$1:$A$7の値が空白以外のセル数より大きい場合は、長さ0の文字列を返します。
それ以外は、
文字列"A"と、
セル範囲$A$1:$A$7の値が、長さ0の文字列以外の場合、
対象のセル範囲$A$1:$A$7の行番号の、数式入力セルの行番号(この場合1)番目に小さい値(1)を返し、
"A1"形式のセル参照式とします。
この場合
"A1"
となり、
1
が返ります。
数式に、ROW関数を使っていますので、
数式入力セルの行番号が違う場合は、数式を変更する必要があります。
例えば、セルB2に最初の数値を表示したい場合は、次のように
ROW()-1として、数式入力セルの行番号(この場合2)-1として下さい。
=IF(ROW()-1>COUNTA($A$1:$A$7),"",INDIRECT("A"&SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)),ROW()-1)))
と入力し、[Shift]キーと[Ctrl]を押しながら、[Enter]キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。自分で{ }を入力してはいけません。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
「文字列関数」
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「統計関数」
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
MIN
http://kiyopon.sakura.ne.jp/kansuu/cell.html#min
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
COUNTA
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#COUNTA
を、参照してください。
セルに入力した"28年3ヶ月"と"2年2ヶ月""の合計値"30年5ヶ月"を計算する
添付ファイルを参照してください。
文字列
"2年2ヶ月"
から、年を数値として取り出す。
LEFT関数と、FIND関数で、文字列年までの文字の年を取り出し、VALUE関数で数値に変換します。
具体的には、
セルG4の文字列"2年2ヶ月"から年数を取り出すには、
=value(LEFT(G4,(FIND("年",G4)-1)))
セルH4の文字列"28年3ヶ月"から年数を取り出すには、
=value(LEFT(H4,(FIND("年",H4)-1)))
文字列
"2年2ヶ月"
から、月を数値としてとり出すには、
MID関数と、FIND関数でも文字列年と文字列ヶの間の文字の月を取り出し、VALUE関数で数値に変換します。
セルG4の文字列から月を取り出す
=MID(G4,FIND("年",G4)+1,(FIND("ヶ",G4)-1)-(FIND("年",G4)))
セルH4の文字列"28年3ヶ月"から月を取り出す
=MID(H4,FIND("年",H4)+1,(FIND("ヶ",H4)-1)-(FIND("年",H4)))
それぞれのセルG4とH4の年の数値を合計し、
=value(LEFT(G4,(FIND("年",G4)-1)))+value(LEFT(H4,(FIND("年",H4)-1)))
その値に、各セルの月の数値の合計値を12で割った整数値を加算し、
=value(LEFT(G4,(FIND("年",G4)-1)))+value(LEFT(H4,(FIND("年",H4)-1)))+int((MID(G4,FIND("年",G4)+1,(FIND("ヶ",G4)-1)-(FIND("年",G4)))+MID(H4,FIND("年",H4)+1,(FIND("ヶ",H4)-1)-(FIND("年",H4))))/12)&"年"
年数とします。
各セルの月の数値の合計値を12で割った余りを月の数値とします。
=mod((MID(G4,FIND("年",G4)+1,(FIND("ヶ",G4)-1)-(FIND("年",G4)))+MID(H4,FIND("年",H4)+1,(FIND("ヶ",H4)-1)-(FIND("年",H4)))),12)&"月"
全部つなげる
=VALUE(LEFT(G4,(FIND("年",G4)-1)))+VALUE(LEFT(H4,(FIND("年",H4)-1)))+INT((MID(G4,FIND("年",G4)+1,(FIND("ヶ",G4)-1)-(FIND("年",G4)))+MID(H4,FIND("ヶ",H4)-1,(FIND("ヶ",H4)-1)-(FIND("年",H4))))/12)&"年"&MOD((MID(G4,FIND("年",G4)+1,(FIND("ヶ",G4)-1)-(FIND("年",G4)))+MID(H4,FIND("ヶ",H4)-1,(FIND("ヶ",H4)-1)-(FIND("年",H4)))),12)&"ヶ月"
添付ファイルを参照してください。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
FIND
http://kiyopon.sakura.ne.jp/kansuu/val.html#find
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
VALUE
http://kiyopon.sakura.ne.jp/kansuu/val.html#value
「数学+三角」
INT
http://kiyopon.sakura.ne.jp/kansuu/abs.html#INT
MOD
http://kiyopon.sakura.ne.jp/kansuu/abs.html#mod
を、参照してください。
リストの右側の品名を検索して一致する左側の番号を返したい
添付ファイルを参照してください。
INDIRECT関数と、MATCH関数を使います。
セルG9には、セルF9の値(この場合「じゃがいも」)を検索値として、セル範囲B1:B100を検索し、該当のA列のセルの値を返す数式
=INDIRECT("A"&MATCH(F9,$B$1:$B$100,0))
と入力します。
数式の意味
文字列"A"と、
セルF9の値を検索値として、セル範囲$B$1:$B$100を検索し、該当する番号(この場合7)を返し、文字列結合します。
文字列結合し、"A1"形式のセル参照とします。
"A7"へのセル参照となり、
308500
が返ります。
ここで、MATCH関数の開始セルをB1としているのは該当する値を行番号で直接INDIRECT関数に文字列"A"と結合するためです。
開始セルをB3とした場合は、対象のセルの業として参照するため、+2としてください。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索・行列」
MATCH
http://kiyopon.sakura.ne.jp/kansuu/address.html#match
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
五捨六入する計算式
1の桁で五捨六入する
添付ファイルを参照してください。
1の桁で五捨六入するには、値-1の値を四捨五入すれば良いですね。
A5セルに数値が入力してあるとします。
セルC5には、セルA5の1桁目の値で,五捨六入する数値を返す数式
=ROUND(A5-1,-1)
と入力します。
数式の意味
A5の値-1を、1桁目で四捨五入します。
10の桁で五捨六入する
10の桁で五捨六入するには、値-10の値を2桁目で四捨五入すれば良いですね。
A5セルに数値が入力してあるとします。
セルE5には、セルA5の二桁目の値で,五捨六入する数値を返す数式
=ROUND(A5-10,-2)
と入力します。
数式の意味
A5の値-10を、2桁目で四捨五入します。
来場日を入力するごとに、同じ来場日ごとにナンバーを1～順に表示させたい
添付ファイルを参照してください。
COUNTIF関数を使います。
セルB4には，
=COUNTIF($A$4:A4,A4)
と入力します。
数式の意味
もし，セル範囲$A$4:A4の値が，セルA4と等しい場合は，そのセル数を返します。
このセルをコピーして下のセル範囲に貼りつけます。
COUNTIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を、参照してください。
INDIRECT関数と、「配列数式」を使います。
添付ファイルを参照してください。
セル範囲G1,I1,K1には，検索値となる店名を入力します。
セル範囲F2:F3には、検索値となる順位を1,2,3と入力します。
セルI2には、店ごとの上位番目の点数を返す数式を「配列数式」で、
=LARGE(IF($B$2:$B$13=G$1,$D$2:$D$13),$F2)
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$B$2:$B$13の値が，セルG$1の値(この場合A)と等しければ，該当のセル範囲$D$2:$D$13の値の、$F2の値(この場合1)番目の値を返します。
このセルをコピーして下のセル範囲に貼りつけます。
セルH2には、店ごとの上位番目の名前を返す数式を「配列数式」で、
=INDIRECT("C"&MIN(IF($B$2:$B$13=G$1,IF($D$2:$D$13=G2,ROW($D$2:$D$13)))))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
文字列"C"と、
もし、セル範囲$B$2:$B$13の値が，セルG$1(この場合A)の値と等しく，かつ、
もし，セル範囲$D$2:$D$13の値が、セルG2の値(この場合290)と等しい場合は，
該当のセル範囲$D$2:$D$13の行番号の最小値(この場合2)を返し、"A1"形式のセル参照式とします。
この場合"C2"へのセル参照式となり，
"ああ"か返ります。
このセルをコピーして下のセル範囲に貼りつけます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」の
LARGE
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#LARGE
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
「検索・行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
セル参照から作成した「金12,000,000円」を全角で「金１２，０００，０００円」と表示したい
文字列を全角に変換する関数JISを使います。
具体的には、シート「契約書」のセルC4には、
セルB4の数値
12000000
から、
金１２，０００，０００円
と表示するには、
セルに
="金"&JIS(TEXT(入力用!B4,"#,###"))&"円"
と入力します。
セルには、
金１２，０００，０００円
と表示されます。
JIS関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
JIS
http://kiyopon.sakura.ne.jp/kansuu/val.html#jis
を、参照してください。
日付表示を文字列に変更したい
TEXT関数を使います。
セルB1に、文字列で
A 1 2010/9/6
2010/9/6
と表示するには、
=TEXT(A1,"yyyy/m/d")
と入力します。
TEXT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を、参照してください。
関数に「DATEDIF」が入ってません
DATEDIF関数について
［関数の挿入］［関数の貼り付け］ダイアログに表示されないためキーボードから手入力する必要があるということです。
Excel 2000だけはかろうじてDATEDIF関数のヘルプが存在していますが、その他のバージョンではヘルプからも削除されています。
マイクロソフトのサイトでも「[XL2000] DATEDIF 関数が関数ウィザードの関数名一覧に含まれない」（KB414668）というサポート技術情報内の記事に明記されています。
「DATEDIF 関数は、他の表計算アプリケーションとの互換を保つために用意された関数のため、関数ウィザードには表示されません。」という記述がありますが、ようは Lotus1-2-3のユーザーをExcelに取り込むためにサポートした関数であるためMicrosoftではヘルプにも関数の貼付けにもありません。
ダイアログにも表示されない、ほとんどのバージョンでヘルプにも表示されないという、特殊な状況にあるDATEDIF関数です
DATEDIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
を、参照してください。
セルAに「2010/6/20」と入力し、セルBに「期間：5月21日～6月20日」と表示する
添付ファイルを参照してください。 TEXT関数と、DATE関数、YEAR関数、MONTH関数を使います。
A1セルには日付を 2010/6/20 と入力します。 セルB1には、前月の21日から当月の20日までの日付を、「期間：5月21日～6月20日」と表示する数式 ="期間："&TEXT(DATE(YEAR(A1),MONTH(A1)-1,21),"m月d日")&"～"&TEXT(DATE(YEAR(A1),MONTH(A1),20),"m月d日") と入力します。 数式の意味 文字列"期間："と、 セルA1の年(この場合2010)と、セルA1の月-1(この場合6-1=5)と、21から日付(この場合2010/5/21)を返し、表示形式"m月d日"とします。 文字列"～"と、 セルA1の年(この場合2010)と、セルA1の月(この場合6)と、20から日付(この場合2010/6/20) を返し、表示形式"m月d日"とします。 セルには、 期間：5月21日～6月20日 と表示されます。 それぞれの関数の説明は、「関数の使い方説明」 http://kiyopon.sakura.ne.jp/kansuu/index.htm 「文字列関数」 TEXT http://kiyopon.sakura.ne.jp/kansuu/val.html#text 「日付+時刻」 DATE http://kiyopon.sakura.ne.jp/kansuu/date.html#date YEAR http://kiyopon.sakura.ne.jp/kansuu/date.html#year MONTH http://kiyopon.sakura.ne.jp/kansuu/date.html#month を、参照してください。
A列の値が○の場合、B列の絶対値を加算、×の場合、絶対値を減算
添付ファイルを参照してください。
IF関数、SUM関数、ABS関数を「配列数式」で入力します。
この場合、絶対参照を数式に使っているところがポイントに成ります。
具体的には、
セルC2には、「配列数式」で
=IF(A2="","",SUM(IF($A$2:$A2="○",ABS($B$2:$B2)))-SUM(IF($A$2:$A2="×",ABS($B$2:$B2)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キ
ーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セルA2の値が、何も入力してない場合は、長さ0の文字列を返します。
それ以外は、
もし、セル範囲$A$2:$A2の値が、"○"と等しい場合は、セル範囲$B$2:$B2の値の絶対値の合計し、
その値から、次の値を減じます。
もし、セル範囲$A$2:$A2の値が、"×"と等しい場合は、セル範囲$B$2:$B2の値の絶対値の合計値
このセルをコピーして下のセル範囲C3:C8に貼りつけます。
数式のセル範囲、$A$2:$A2は、$A$2を「絶対参照」としてセルを固定した開始セルとし、セル$A2を相対参照として可変としたセル範囲ですから、このセルをコピーして下のセル範囲A5に貼りつけした場合、数式のセル範囲は、$A$2:$A5となります。
合計するセル範囲$B$2:$B2も同様に、$B$2:$B5となります。
したがってセルの範囲の値の累計値が返ります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の
「数学+三角」
ABS
http://kiyopon.sakura.ne.jp/kansuu/abs.html#abs
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を、参照してください。
リストのデータから品名別の平均値をF列に求めたい
エクセル2003での説明とします。 添付ファイルを参照してください。 「ピボットテーブル」と「数式」の二つの例で説明いたします。 シート「ピボットテーブル」を参照してください。
データのセル範囲A1:B6のいずれかのセルを選択し、 「データ」「ピボットテーブルとピボットグラフレポート」(エクセル2007の場合は「挿入」「テーブル」「ピボットテーブル」「ピボットテーブル」)を選択します。 「ピボットテーブル／ピボットグラフウィザード-1/3」ダイアログボックスの「分析するデータのある場所を選択してください。」で、 Excelのリスト／データベースを選択し、「作成するレポートの種類を指定したください。」の「ピボットテーブル」を選択して、「次へ」ボタンを押します。 「ピボットテーブル／ピボットグラフウィザード-2/3」ダイアログボックスの「範囲」に項目名を含んで正しくデータベースの範囲が選択されていることを確認し「次へ」ボタンを押します。（違っている場合は、セル範囲を選択し直します。） 「ピボットテーブル／ピボットグラフウィザード-3/3」ダイアログボックスの「ピボットテーブルの作成先を指定してください。」で「新規ワークシート」あるいは「既存のワークシート」（この場合は集計を表示する範囲の「セルの左上端」セルE1を指定します。）を選択し、「完了」ボタンを押します。 ピボットテーブルのフィールドリスト（メニューバーのように表示されている項目名のリスト）から、「品名」の項目を「ここに行のフィールドをドラッグします」というエリアにドラッグします。 集計したい「歩留」の項目を「ここにデータアイテムをドラッグします」というエリアにドラッグします。 セルE1の「合計/歩留」を「平均/歩留」にするため、マウスの右ボタンで選択し、「フィールドの設定」を選択し、「ピボットテーブル フィールド」ダイアログボックスで、「集計の方法」で、「平均」を選択し「ＯＫ」ボタンを押します。 各品名の計が表示されていますので、これを表示しないようにするため、ピボットテーブルの「品名」のいずれかのセルを選択し、「ピボットテーブルツールバー」の「フィールドの設定」を選択し、「集計」の「自動」のラジオボタンがおされていますので、「なし」のラジオボタンを押し、「ＯＫ」ボタンを押します。 「集計」の表示形式を「%」小数点以下１桁までにするため、ピボットテーブルの「集計」のいずれかのセルを選択し、「ピボットテーブルツールバー」の「フィールドの設定」を選択し、「表示形式」を選択し、「分類」ので「パーセンテージ」を選択し、「小数点以下の桁数」を「１」として「ＯＫ」ボタンを押します。 ピボットテーブルで集計したデータは、元のデータに変更が加えられた場合でも、ピボットテーブルが自動で更新されることはありません。ピボットテーブルを更新（再計算）するには、ピボットテーブルの任意のセルを1つ選択し、「ピボットテーブルツールバー」の「データの更新」ボタンを押してください。 シート「数式」を参照して下さい。
データは1000行まで自動計算できるようにしています。
品名と平均を自動で表示するため、セルC2には、重複している各品名の最初の行を求める数式
=COUNTIF($A$2:$A2,$A2)
と入力します。
数式の意味
セル範囲$A$2:$A2の値が、セル$A2と等しいセル数を返します。
この値が、1の行が、重複した品名の最初の行となります。
このセルをコピーして下のセル範囲に貼りつけます。
セルC1には、重複していない品名の数を返す数式
=SUMIF($C$2:$C$1000,1)
と入力します。
数式の意味
セル範囲$C$2:$C$1000の値が、1の値を合計します。
この場合、3が返ります。
セルE2には、品名を順に返すため「配列数式」で、
=IF($C$1>=ROW()-1,INDIRECT("A"&SMALL(IF($C$2:$C$1000=1,ROW($C$2:$C$1000)),ROW()-1)),"")
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル$C$1の値(この場合3)が、数式入力セルの行番号-1(この場合2-1=1)以上の場合は、
文字列"A"と、
もし、セル範囲$C$2:$C$1000の値が、1と等しい場合
対象のセル範囲$C$2:$C$1000の行番号の、
数式入力セルの行番号-1(この場合2-1=1)番目に小さい値を返し、"A1"形式のセル参照式とします。
それ以外は、長さ0の文字列を返します。
このセルをコピーして下のセル範囲に貼りつけます。
セルf2には、対象の品名の歩留まりの平均値を返す数式を「配列数式」で、
=IF($E2="","",AVERAGE(IF($A$2:$A$1000=$E2,$B$2:$B$1000)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル$E2の値が、長さ0の文字列の場合は、長さ0の文字列を返します。
それ以外は、
もし、セル範囲$A$2:$A$1000の値が、セル$E2と等しい場合、
該当のセル範囲,$B$2:$B$1000の値の平均値を返します。
このセルをコピーして下のセル範囲に貼りつけます。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「統計関数」
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
AVERAGE
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#AVEDEV
を、参照してください。
「配列数式」を使います。
添付ファイルを参照してください。
セルA6には、検索値の
5
と入力します。
セルB6には、「配列数式」で、
=INDIRECT("R"&MIN(IF($A$1:$C$3=$A$6,ROW($A$1:$C$3)))&"C"&MIN(IF($A$1:$C$3=$A$6,COLUMN($A$1:$C$3)))+1,FALSE)
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キ
ーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
文字列"R"と、
もし、セル範囲$A$1:$C$3の値が、セル$A$6(この場合5)と等しい場合は、該当のセル範囲$A$1:$C$3
の行番号の最小値(この場合2)を文字列結合し、
文字列"C"と、
もし、セル範囲$A$1:$C$3の値が、セル$A$6(この場合5)と等しい場合は、該当のセル範囲$A$1:$C$3
の列番号の最小値+1(この場合3+1=4)を文字列結合し、
"R1C1"形式のセル参照式とします。
この場合"R2C4"(D2)へのセル参照となります。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
「統計関数」
MIN
http://kiyopon.sakura.ne.jp/kansuu/cell.html#min
を、参照してください。
一度ON閾値以上となったら、OFF閾値以下となるまで0とならないようにする
「配列数式」を使います。
添付ファイルを参照してください。
具体的には、
セルE1には、OFFのしきい値
11
セルC1には、ONのしきい値
13
と入力します。
まず、セルC4には、
OFFのしきい値(この場合11)以下の数値が、対象セルの行までに入力してある場合は、その行番号の最大値を返す数式は、「配列数式」で
=MAX(IF($A$4:$A4<=$E$1,ROW($A$4:$A4)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$4:$A4の値が、セル$E$1の値(この場合11)以下の場合は、対象のセル範囲$A$4:$A4の行番号の最大値を返します。
このセルをコピーして下のセル範囲に貼りつけます。
同様に、セルD4には、
ONのしきい値(この場合13)以上の数値が、対象セルの行までに入力してある場合は、その行番号の最大値を返す数式は、「配列数式」で
=MAX(IF($A$4:$A4>=$C$1,ROW($A$4:$A4)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$4:$A4の値が、セル$E$1の値(この場合11)以下の場合は、対象のセル範囲$A$4:$A4の行番号の最大値を返します。
このセルをコピーして下のセル範囲に貼りつけます。
結果
OFFしきい値までの最大の行番号と、ONしきい値までの最大の行番号と、比較して、OFFしきい値の行番号が大きい場合、0、それ以外は、1を返す数式
=IF(C4>D4,0,1)
と入力します。
このセルをコピーして下のセル範囲に貼りつけます。
これら、3つの数式を合成すれば、セルB4には、「配列数式」で、
=IF(MAX(IF($A$4:$A4<=$E$1,ROW($A$4:$A4)))>MAX(IF($A$4:$A4>=$C$1,ROW($A$4:$A4))),0,1)
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }で、くくられます。
自分で{ }を入力してはいけません。
このセルをコピーして下のセル範囲に貼りつけます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htmを、参照してください。
の「統計関数」
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
「検索・行列」
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
日毎の合計を別シートのカレンダーに反映させる添付ファイルを参照してください。
シート「シート1」
A列には、全てのデータ入力行に日付を入力します。
これで、日付ごとの合計が簡単に出来ます。
セルC2には、日毎の合計値を返す数式
=IF(A2="",SUMIF($A1:A$2,A1,$B1:B$2),"")
と入力します。
数式の意味
もし、セルA2の値が何も入力してない場合は、
それ以外は、セル範囲$A1:A$2の値が、セルA1と等しい場合は、該当のセル範囲$B1:B$2の合計値を返しいます。
それ以外は、長さ0の文字列を返します。
シート「シート２」
セルA3には、カレンダーの日付を自動的に表示し、合計値を日付から算出するため、該当月の1日の「日付シリアル値」を返す数式
=DATEVALUE(B3+IF(B3<100,1988,0)&"/"&D3&"/1")
と入力します。
数式の意味
セルB3の値に、もし、セルB3の値が、100未満の場合は、1988それ以外は、0を加算し、
文字列"/"と、セルD3の値と、文字列"/1"を文字列結合し(この場合2010/4/1)、日付シリアルを返します。
セルには、なにも表示しないようにするため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「;;;」と入力し「OK」ボタンを押します。
曜日を表示しているセル範囲A4:G4には、数値を1,2,3,4,5,6,7と入力します。
セルに曜日を表示するため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「aaa」と入力し「OK」ボタンを押します。
セルには、日,月,火,水,木,金,土が表示されます。
セルA5には、該当曜日の日付を表示するため
=$A$3-WEEKDAY($A$3,$A$4)+A$4
と入力します。
数式の意味
セル$A$3の値(この場合2009/4/1)、-セル$A$3の曜日(この場合木曜日)を、セル$A$4の数値(この場合1ですから日曜日)を開始値とした数値(この場合5)を返し、セルA$4の値(1)を加算します。
この場合は、日付シリアル値で(2010/4/1)-5+1=(2010/3/28)が返ります。
セルに日付シリアル値から、日付のみを表示するため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「d」と入力し「OK」ボタンを押します。
セルに該当月以外は日付を表示しないように、「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、「条件1」に「数式が」
エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を
「=MONTH(A5)<>MONTH($A$3)」と入力し、「書式」ボタンを押して、「フォント」の「色」を「白」にして、「罫線」を「なし」にして、「ＯＫ」ボタンを押します。
セルB5には、次の日付を返す数式
=A5+1
と入力します。
セルに日付シリアル値から、日付のみを表示するため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「d」と入力し「OK」ボタンを押します。
セルに該当月以外は日付を表示しないように、「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、「条件1」に「数式が」
エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を
「=MONTH(A5)<>MONTH($A$3)」と入力し、「書式」ボタンを押して、「フォント」の「色」を「白」にして、「罫線」を「なし」にして、「ＯＫ」ボタンを押します。
このセルをコピーして右のセル範囲C5:G5に貼りつけます。
セルA6には、該当日のデータ合計を返す数式
=IF(MONTH(A5)<>MONTH($A$3),"",SUMIF(シート１!$A$2:$A$65536,A5,シート１!$B$2:$B$65536))
と入力します。
数式の意味
もし、セルA5の月が、セル$A$3の月と違う場合は、長さ0の文字列を返します。
それ以外は、シート「シート１」のセル範囲$A$2:$A$65536の値が、セルA5と等しい場合、シート「シート１」の該当のセル範囲$B$2:$B$65536」の合計値を返します。
セルに該当月以外は日付を表示しないように、「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、「条件1」に「数式が」
エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を
「=MONTH(A5)<>MONTH($A$3)」と入力し、「書式」ボタンを押して、「フォント」の「色」を「白」にして、「罫線」を「なし」にして、「ＯＫ」ボタンを押します。
このセルをコピーして右のセル範囲B6:G6に貼りつけます。
セルA7には、次の週の日付を返す数式
=A5+7
と入力します。
セルに日付シリアル値から、日付のみを表示するため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「d」と入力し「OK」ボタンを押します。
セルに該当月以外は日付を表示しないように、「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、「条件1」に「数式が」
エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を
「=MONTH(A7)<>MONTH($A$3)」と入力し、「書式」ボタンを押して、「フォント」の「色」を「白」にして、「罫線」を「なし」にして、「ＯＫ」ボタンを押します。
セルB7には、次の火を返す数式
=A7+1
と入力します。
セルに日付シリアル値から、日付のみを表示するため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「d」と入力し「OK」ボタンを押します。
セルに該当月以外は日付を表示しないように、条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、「条件1」に「数式が」
エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を
「=MONTH(A5)<>MONTH($A$3)
」と入力し、「書式」ボタンを押して、「フォント」の「色」を「白」にして、「罫線」を「なし」にして、「ＯＫ」ボタンを押します。
このセルをコピーして右のセル範囲C7:G7に貼りつけます。
以下同様です。
5月,6月のカレンダーも同様ですが、各数式や条件付き書式の数式の参照先が変わりますので、修正が必要です。
数式の結果の0を表示しないようにする場合は「ツール」「オプション」の「表示」で、「ウィンドウオプション」の「ゼロ値」のチェツクを外し「ＯＫ」ボタンを押します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「数学+三角」
SUMIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
「日付+時刻」
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
WEEKDAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#weekday
を、参照してください。
ｂシートの 月別・氏名別の合計額をａシートの 月別・氏名欄に表示したい
参考になる質問が、「エクセルの質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
の「データベース」「2001/09/28」
「複数の検索条件に一致するセルの値を合計する方法、個数を数える方法(配列数式)」
にあります。
添付ファイルを参照してください。
シート「a」
まず、検索条件となるA列の月を数値のみを入力します。
セルA2には、数値の
3
と入力します。
セルに全角の
３月
と表示するため、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「[DBNum3]#"月"」と入力し「OK」ボタンを押します。
セルの値は、数値の3のままで、セルの表示のみ[３月]となります。
このセルをコピーして下のセルA8,A14に貼りつけます。
セルA8には、数値の
4
と入力します。
セルの値は、数値の4のままで、セルの表示のみ[４月]となります。
セルA14には、数値の
5
と入力します。
セルの値は、数値の5のままで、セルの表示のみ[５月]となります。
セルC3には、シートBのセル範囲$A$2:$A$13の値が、セルA2の値と等しく、かつ、シートBのセル範囲$C$2:$C$13の値が、セルB3の値と等しい場合という、複数の検索条件に合致するセルの値を合計する数式を「配列数式」で、
=SUM(IF(MONTH(b!$A$2:$A$13)=$A$2,IF(b!$C$2:$C$13=B3,b!$B$2:$B$13)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{}で、くくられます。
自分で{}を入力してはいけません。
数式の意味
もし、シート「b」のセル範囲$A$2:$A$13の月が、セル$A$2の値(この場合3)と等しく、かつ、もし、シート「b」のセル範囲$C$2:$C$13の値が、セルB3の値(この場合Ａ)と等しい場合は、シート「b」の該当のセル範囲$B$2:$B$13の値を返し、合計します。
このセルをコピーして下のセル範囲C4:C5に貼りつけます。
同様に、
セルC9には、「配列数式」で、
=SUM(IF(MONTH(b!$A$2:$A$13)=$A$8,IF(b!$C$2:$C$13=B9,b!$B$2:$B$13)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{}で、くくられます。
自分で{}を入力してはいけません。
数式の意味
もし、シート「b」のセル範囲$A$2:$A$13の月が、セル$A$8の値(この場合4)と等しく、かつ、もし、シート「b」のセル範囲$C$2:$C$13の値が、セルB9の値(この場合Ａ)と等しい場合は、シート「b」の該当のセル範囲$B$2:$B$13の値を返し、合計します。
このセルをコピーして下のセル範囲C10:C11に貼りつけます。
同様に、
セルC15には、「配列数式」で、
=SUM(IF(MONTH(b!$A$2:$A$13)=$A$14,IF(b!$C$2:$C$13=B15,b!$B$2:$B$13)))
と入力し、{Shift}キーを押しながら{Ctrl}キーを押しながら、さらに{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{}で、くくられます。
自分で{}を入力してはいけません。
数式の意味
もし、シート「b」のセル範囲$A$2:$A$13の月が、セル$A$14の値(この場合5)と等しく、かつ、もし、シート「b」のセル範囲$C$2:$C$13の値が、セルB15の値(この場合Ａ)と等しい場合は、シート「b」の該当のセル範囲$B$2:$B$13の値を返し、合計します。
このセルをコピーして下のセル範囲C16:C17に貼りつけます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
を、参照して下さい。
時間の計算結果の1時間を24倍したら1.0にならない
時間の計算で注意するところは、割り切れない少数となる点です。
1日は、1
1時間は1÷24(時間)=0.0416666666666667
1分は1÷24(時間)÷60(分)0.000694444444444444
ですね。
1時間シリアル値を24倍した値をそのまま表示しています。
1:00は、
0.999999999999996
となり、1未満ですから、小数点以下を切り捨てると0となります。
小数点以下の時間を、四捨五入する数式にします。
セルJ2に時間の計算式が入力してあるとします。
=ROUND(J2*24,0)
これで、
1.0
となります。
セルの文字列に"Aー１"とか"Aー２"とか"A"とか文字列"A"を含むセル数をカウントしたい検索値に、ワイルドカード文字("*")を使います。
具体的には、セル範囲A1:A100にデータが入力してあるとします。
文字列"A"を含むセルの数を返すには、
=COUNTIF(A1:A100,"*A*")
と入力します。
MOD(6,1.2)とした場合返り値は、0ではないのか小数点以下の数値に限らず、コンピュータは、10進数を2進数で計算しています。
エクセルでは、数値の桁数を15桁までしか正しく認識できません。
ご質問の場合は、
6÷1.2=5ですから、余りはないのですが、
セルの書式設定で、「表示形式」の「分類」で、「数値」「小数点以下の桁数」で、
小数点以下30桁まで表示してみると
0.000000000000000222044604925031
となり、0ではないことがわかります。
これを回避するには、ROUND関数で小数点以下4桁程度までに丸めます。
=ROUND(MOD(6,1.2),4)
0
が返ります。
時間を分に換算する方法
セルA1に、
25:15
と入力してあるとします。
セルの書式設定で、分の値を
1515
と、表示可能です。
分の値に表示するには、セルを選択し、「書式」「セル」を選択し「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で、「ユーザー定義」を
選択し、「種類」欄に「[m]」と入力し「OK」ボタンを押します。
セルには、
1515と表示されます。
セルの値は、あくまで、
25:15
です。
これを数値の
1215
とするには、別のセルに数式を入力します。
時刻シリアル値では、1日は1で、24を掛れば、時間となります。
1時間は60を掛れば、分になります。
セルB1に
=A1*24*60
と入力します。
B1セルを選択し、「書式｣｢セル｣を選択し｢セルの書式設定｣ダイアログボックスで｢表示形式｣の｢分類｣で｢標準」として｢OK｣ボタンを押します。
セルには、数値の
1515
が返ります。
表形式の単価と数量から全ての合計値を一度に計算する「配列数式」を使います。
添付ファイルを参照してください。
具体的には、
セルB4には、単価をセル範囲$D$3:$M$3として、数値を、セル範囲D4:M4としてかけ算した答えを合計する数式を「配列数式」で、
=SUM($D$3:$M$3*D4:M4)
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
このセルをコピーして、下のセル範囲に貼り付けます。
ＥＯＭＯＮＴＨ関数を使わないで該当日の月末の日付を返す
当月の月末は、次月の1日の1日前ですから、
セルA8に日付
2009/11/25
が入力してあるとします。
セルにセルA8の月末を返すには、
=DATE(YEAR(A8),MONTH(A8),1)-1
となります。
数式の意味
セルA8の年(2009)と、セルA8の月(11)と、1日から日付を返し、その1日前の日付
を返します。
この場合、2009/11/1の1日前ですから、2009/10/31が返ります。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
を、参照してください。
選択セルに一定数を乗じる方法同じ質問が「質問と回答」
の「計算式・関数」「2008/07/21」
「選択セルに同じ数字を乗算（除算、加算、減算）する方法」にあります。
計算式がRCとかR[-1]CなどになってしまうRは、行(ROW)
Cは、列(COLUMN)
で、セルの行番号と列番号をR1C1形式で表示していませんか。参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
の「表示・書式」「2007/09/17」
「A,B,C・・・という列の表記が1,2,3・・・に変わってしまいました」
にあります。
一致する名前の人のそれぞれの得点を表示したい添付ファイルを参照してください。
SUMIF関数を使います。
添付ファイルを参照してください。セルB8には、検索値としての
山下
を入力します。セルB10には、
=SUMIF($B$2:$B$6,$B$8,$C$2:$C$6)
と入力します。
数式の意味
もし、セル範囲$B$2:$B$6の値が、セル$B$8の値（山下）と等しければ、該当のセ
ル範囲$C$2:$C$6の値を合計して返します。
この場合、セルB2の値が、等しいので、該当のセルC2の値(この場合15)が返りま
す。同様に、
セルB11には、
=SUMIF($D$2:$D$6,$B$8,$E$2:$E$6)
と入力します。
数式の意味
もし、セル範囲$D$2:$D$6の値が、セル$B$8の値（山下）と等しければ、該当のセ
ル範囲$E$2:$E$6の値を合計して返します。
この場合、セルE4の値が、等しいので、該当のセルE4の値(この場合24)が返りま
す。SUMIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
SUMIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
を、参照してください。
25:00を25.0 25:30を25.5と表示させたい時刻シリアル値は、24:00で、1ですから、時刻シリアル値を24倍すれば良いです。
ただし、時刻入力セルとは別のセルに表示することになります。
添付ファイルを参照してください。
具体的には、
A B
1 25:00 25.0
2 25:30 25.5セルA1に時刻
25:00
と入力してあるとします。
セルに、
25:00
と表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を選択し、｢種類｣欄に「[h]:mm」と入力し、「ＯＫ」ボタンを押します。セルB1に、
=A1*24
と入力します。
セルに
25.0
と表示するため、B1セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「数値」を選択し、「小数点以下の桁数」に「1」を入力して、「ＯＫ」ボタンを押します。また、参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
「計算式・関数」「2003/7/4」
「時間を１５分単位で丸めて切り上げ、切り捨て」
があります。
2年11ヵ月を1年を1とした数値にしたい小数を含む数値で、1年を1としたい場合は、次のような注意が必要です。
単に
2年5ヵ月を
2.5
とすれば、
2年11ヵ月は、
2.11
となって、2.10ヵ月以降は数値が、2.2よりも小さくなってしまいます。
したがって、「勤続年数」で、計算するには、
2年と(12分の5)ヵ月
=2+5/12
=2.4166666672年と12分の11ヵ月
=2+11/12
=2.916666667
とすることが必要です。
この場合、セルには、年数+月数÷12となる数式
A1セルに開始日付(シリアル値)
B1セルに終了日付(シリアル値)
が入力してある場合
=DATEDIF(A1,B1,"Y")+DATEDIF(A1,B1,"ym")/12
と入力します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
EOMONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#eomonth
を参照してください。
ローンの借入金10,000,000円、95,000円/月の元利均等返済、金利1%/月では、何年で支払が終わるNPER関数を使います。
=NPER(借入金利(年)/12,定期返済額*12,将来価値,借入金)
借入金10,000,000円、月95,000円支払いで、金利1%の場合
=NPER(1%,-95000*12,0,10000000)
=8.45028
ですから、8.5年
となります。
NPER関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「財務関数」
NPER
http://kiyopon.sakura.ne.jp/kansuu/zaim.htm#NPER
を、参照してください。
添付ファイルを参照してください。
D列の各セルには、指定の数値を入力します。
セルA8には、指定の期(nヵ月後を例えば10ヵ月後の場合)を
10
と入力します。セルB8には、指定の期までの定期返済額累計を返す数式
=D2*A8
と入力します。セルC8には、指定の期までの金利累計を返す数式
=CUMIPMT(D5/12,D4*12,D1,1,10,0)セルD8には、指定の期までの元金返済額累計を返す数式
=CUMPRINC(D5/12,D4*12,D1,1,A8,0)
と入力します。セルE8には、指定の期後の借入残高を返す数式
=D1+D8
と入力します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「財務関数」
CUMIPMT
http://kiyopon.sakura.ne.jp/kansuu/zaim.htm#CUMIPMT
CUMPRINC
http://kiyopon.sakura.ne.jp/kansuu/zaim.htm#CUMPRINC
を、参照してください。
8.3と入力し、8:30と認識させる入力セルと同じセルに返すのは無理です。
8.3とセルB7に入力し、別のセルに8:30と時刻で表示させるには、
=TRUNC(B7,0)/24+(MOD(B7,1)/0.6)/24
または、
=(TRUNC(B7,0)+MOD(B7,1)/0.6)/24
あるいは、
=(INT(B7)+MOD(B7,1)/0.6)/24
や、
=(INT(B7)+(B7-INT(B7))/0.6)/24
でも良いです。
それぞれ関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
TRUNC
http://kiyopon.sakura.ne.jp/kansuu/abs.html#TRUNC
MOD
http://kiyopon.sakura.ne.jp/kansuu/abs.html#mod
INT
http://kiyopon.sakura.ne.jp/kansuu/abs.html#INT
を、参照してください。
1億482.3万という文字列データを数値に変える添付ファイルを参照してください。
A B 1 4836.2万 48,362,000 2 1億482.3万 104,823,000 億の桁の数値と、万の桁の数値をそれぞれ取りだして加算します。
セルB1には、
=IF(ISERR(FIND("億",A1)),0,LEFT(A1,FIND("億",A1)-1)*100000000)+RIGHT(SUBSTITUTE(A1,"万",""),LEN(SUBSTITUTE(A1,"万",""))-IF(ISERR(FIND("億",A1)),0,FIND("億",A1)))*10000
と入力します。
数式の意味
もし、検索文字列"億"が、セルA1に含まれない場合は、0を、
それ以外は、セルA1の左から、検索文字列"億"が、セルA1の何文字目にあるかの
文字数-1(この場合7-1=6)文字取りだし、1億倍し、
セルA1に含まれる"万"の文字を、長さ0の文字列とした文字列の左から、
セルA1に含まれる"万"の文字を、長さ0の文字列とした文字列の文字の長さ-
もし、検索文字列"億"が、セルA1に含まれない場合は、0を、
それ以外は、セルA1の左から、検索文字列"億"が、セルA1の何文字目にあるかの
文字数文字を取りだし
一万倍します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
FIND
http://kiyopon.sakura.ne.jp/kansuu/val.html#find
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
RIGHT
http://kiyopon.sakura.ne.jp/kansuu/val.html#right
SUBSTITUTE
http://kiyopon.sakura.ne.jp/kansuu/val.html#substitute
「情報関数」
ISERR
http://kiyopon.sakura.ne.jp/kansuu/cell.html#iserr
を、参照してください。
対象者毎の最小値最大値を返す最小値を返す関数は、MIN関数
最大値を返す関数は、MAX関数
です。
しかし、単なる最小値と最大値ではないですね。
対象者各人の最小値と最大値ですから、
検索条件に合致するセル範囲の最大値と最小ですね。
この場合「配列数式」を使います。
具体的には、
添付ファイルを参照してください。
セルE7には検索値としてデータ
△
と入力します。セルF7には、検索値△に合致する名前(A列)の、最小値(B列)の最小値を返す数式
=MIN(IF($A$2:$A$60=$E7,$B$2:$B$60))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$2:$A$60の値が、セル$E7と等しい場合は、該当のセル範囲$B$2:$B$60
の最小値を返します。
このセルをコピーして、下のセル範囲に貼り付けます。同様に、セルG7には、検索値△に合致する名前(A列)の、最大値(C列)の最大値を
返す数式
=MAX(IF($A$2:$A$60=$E7,$C$2:$C$60))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$2:$A$60の値が、セル$E7と等しい場合は、該当のセル範囲$C$2:$C$60
の最大値を返します。
このセルをコピーして、下のセル範囲に貼り付けます。
ひとつのセルに記入された年月日を年・月・日にセルを分けたいYEAR関数とMANTH関数とDAY関数を使います。
具体的には、セルA1に日付入力形式で日付が
2009/9/10
と入力してあるとします。
セルB1には、年を返す数式
=YEAR(A1)
と入力します。
2009
が返ります。セルC1には、月を返す数式
=MONTH(A1)
と入力します。
9
が返ります。セルD1には、日を返す数式
=DAY(A1)
と入力します。
10
が返ります。年を元号
21
で返すためには、TEXT関数を使います。
=TEXT(A1,"e")
と入力します。
21
が返ります。それぞれの関数の説明は、「関数の使のい方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
MANTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を、参照してください。
セルの値"1km0-4人口"から"1km","人口","男","女"を削除したい添付ファイルを参照してください。
SUBSTITUTE関数を使います。
セルA2には、文字列
"1km0-4人口"
が入力してあるとします。セルA1には、セルA2の文字列から、
"1km","人口","男","女"
の文字列を除いた文字列
"0-4"
を返す数式
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"1km",,1),"人口",,1),"男",,1),"女",,1)
と入力します。
数式の意味
セルA2の文字列から"1km"を検索し、何もない文字列に置き換えます。
さらに、その文字列から"人口"を検索し、何もない文字列に置き換えます。
さらに、その文字列から"男"を検索し、何もない文字列に置き換えます。
さらに、その文字列から"女"を検索し、何もない文字列に置き換えます。
このセルをコピーして、右のセル範囲に貼り付けます。
SUBSTITUTE関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
SUBSTITUTE
http://kiyopon.sakura.ne.jp/kansuu/val.html#substitute
を、参照してください。
○が何個で×が何個かを計算する添付ファイルを参照してください。
COUNTIF関数を使います。
具体的には、
セル範囲A1:A100に
"○"または、"×"が入力してあるとします。
セルB1には○のセル数を返す数式
=COUNTIF(A1:A100,"○")セルC1には×のセル数を返す数式
=COUNTIF(A1:A100,"×")と入力します。
COUNTIF
関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を、参照してください。
1つのセルに入力した文字列をA5セルから右下に20文字改行で１文字ずつ取り出す半角も1文字として認識されます。
添付ファイルを参照してください。
左上から右下に20文字で改行する場合
セルA5を開始セルとします。
開始列は、1
開始行は、5
です。
開始行が5行目からですから、(ROW()-5)*20で、20文字改行の計算をします。
開始行が、10行目の場合は、(ROW()-10)*20となります。
開始列が1列目ですからCOLUMN()で開始列を計算します。
開始列が10列目の場合は、COLUMN()-9となります。セルA5には、文字数の開始位置を行番号と列番号から計算して、1文字取り出します。
=MID($F$1,COLUMN()+(ROW()-5)*20,1)
と入力します。
数式の意味
セル$F$1の文字列の、数式入力セルの列番号(この場合1)+数式入力セルの行番号-5(この場合5-5=0)*20文字目(この場合1)から、1文字返します。
このセルをコピーして、右のセル範囲に貼り付けます。
このセルをコピーして、下のセル範囲に貼り付けます。右上から左下に20文字で改列する場合
セルT12を開始セルとします。
開始列は、20
開始行は、12
です。
開始行が、12行目からですから、数式入力セルの行番号-11を開始行としています。
開始列が、左から20列目ですから、(20-COLUMN())*20で、20文字で改列の計算をします。
開始列が30列目の場合は、(30-COLUMN())*20となります。セルT12には、文字数の開始位置を行番号と列番号から計算して、1文字取り出します。
=MID($F$1,(20-COLUMN())*20+(ROW()-11),1)
と入力します。
数式の意味
セル$F$1の文字列の、数式入力セルの列番号(この場合1)+数式入力セルの行番号-5(この場合5-5=0)*20文字目(この場合1)から、1文字返します。
このセルをコピーして、右のセル範囲に貼り付けます。
このセルをコピーして、下のセル範囲に貼り付けます。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
「検索+行列」
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
を、参照してください。
セルの3行毎の参照をA2=F2,A5=F3,A8=F4,A11=F5としたい添付ファイルを参照してください。
セルA2には、
=INDIRECT("$F"&(ROW()-2)/3+2,TRUE)
と入力します。
数式の意味
文字列"$F"と、(数式入力セルの行番号-2)/3+2(この場合(2-2)/3+2=2)を文字列結
合して"A1"形式のセル参照とします。
この場合$F2へのセル参照となります。このセルをコピーして、下のセル範囲A5,A8,A11に貼り付けます。
A5に貼り付けると(ROW()-2)/3+2
の部分は
(5-2)/3+2=(3/3)-2=1+2=3
となり、"$F3"へのセル参照A8に貼り付けると(ROW()-2)/3+2
の部分は
(8-2)/3+2=(6/3)+2=2+2=4
となり、"$F4"へのセル参照A11に貼り付けると(ROW()-2)/3+2
の部分は
(11-2)/3+2=(9/3)+2=3+2=5
となり、"$F5"へのセル参照
となります。INDIRECT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
文字列に対応した得点表から文字で入力した得点を合計添付ファイルを参照してください。
「配列数式」を使います。
セルI3には、「配列数式」で、
=SUM(IF($B$3:$H$3=$B$6:$B$8,$C$6:$C$8))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$B$3:$H$3の値が、$B$6:$B$8と等しい場合、セル範囲$C$6:$C$8の値を返し、合計します。
セルの文字数を１６文字にするために文字列の前又は後ろに空白を挿入するA1セルに文字列を入力します。
B1セルに、全角の空白を文字列の前に入力する数式
=REPT(" ",16-LEN(A1))&A1
と入力します。
数式の意味
文字列" "を、16-セルA1の文字数の数数繰り返した文字列を作成し、セルA1の文字列と結合します。B1セルに、全角の空白を文字列の後ろに入力する数式
=A1&REPT(" ",16-LEN(A1))
と入力します。
数式の意味
セルA1の文字列と、文字列" "を、16-セルA1の文字数の数数繰り返した文字列を作成し、結合します。B1セルをコピーして、A1セルに「編集」「形式を選択して貼り付け」で「値」にチェツクを入れて「ＯＫ」ボタンを押します。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
REPT
http://kiyopon.sakura.ne.jp/kansuu/val.html#rept
LEN
http://kiyopon.sakura.ne.jp/kansuu/val.html#len
を、参照してください。
指定の項目の最新データだけを抽出したい添付ファイルを参照してください。
IF関数と、MAX関数を「配列数式」で使います。
B列の発注番号の項目で、F列の[納入日]が最新のものを抽出するとします。
セルG2には、B列の「発注番号」が同じで、F列の「納入日」が最大の場合は"○"を返す数式
=IF(B2&F2=B2&MAX(IF($B$2:$B$7=B2,$F$2:$F$7)),"○","")
と入力します。
数式の意味
もし、セルB2の値と、セルC2の値を文字列結合し(この場合"Ａ０００１39933")、
セルB2の値と、もし、セル範囲$B$3:$B$7の値が、セルB2と等しい場合、該当のセル範囲($F$2:$F$7)の値の最大値を文字列結合し(この場合"Ａ０００１39938")、
それぞれが等しい場合は、文字列"○"を返し、それ以外は何も返しません。
このセルをコピーして、下のセル範囲に貼り付けます。「データ」「フィルタ」「オートフィルタ」で、G列の「最新」で、「○」を検索します。
COUNTIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if「統計関数」
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
を、参照してください。
0010200003というデーターを001-02-00003と置換したい文字列の"0010200003"というデータを入力したセルとは別のセルに、数式で"001-02-00003"
と置換することで対応します。
A1セルに文字列の表示形式で
0010200003
と入力してあるとします。
B1セルには、
=LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,5)
と入力します。A1セルの値と入れ替えるには、
B1セルを「編集」「コピー」して、A1セルに「編集」「形式を選択して貼り付け」で「値」にチェックを入れて「ＯＫ」ボタンを押します。
B1セルは削除します。
負の数を「０」とみなして合計を算出したいSUMIF関数を使います。
具体的には、セル範囲A1:A100の値を、0より大きい数値のみ合計する場合、
=SUMIF(A1:A100,">0")
と入力します。
SUMIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
SUMIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
を、参照してください。
常に18行おきのセルC1=A18,C2=A36,C3=A54を参照したい参考になる質問が「質問と回答」
の「計算式・関数」「2003/10/31」
「数式が10行毎のデータ(1,11,21,31行目)を返すようにコピーしたい」にあります。御質問の場合は、C1→A18,C2→A36,C3→A54を参照ですから、
具体的には、次のようになります。
セルC1に、セルA18の参照となる数式
=INDIRECT("$A"&ROW()*18,TRUE)
と入力します。
数式の意味
文字列"$A"と、数式入力セルの行番号(この場合1)×18の値を文字列結合し、"A1"形式のセル参照式とします。
この場合、"$A18"
へのセル参照式となります。このセルをコピーして、下のセル範囲に貼り付けます。
セルC2には、数式入力セルの行番号が、2ですから2×18=36
"A36"
のセル参照式となります。同様に、セルC3には、数式入力セルの行番号が、3ですから3×18=54
"A54"
のセル参照式となります。
顧客ID顧客名のリストを参照し、顧客名を入力すれば顧客IDも自動で表示する方法添付ファイルを参照してください。顧客IDと顧客名のリストで、顧客名が、顧客IDより左にあれば、VLOOKUP関数が利用できるのですが、御質問の場合は、顧客名が顧客IDよりも右にありますので、VLOOKUP関数は利用できません。
(リストの顧客IDと顧客名を入れ替えれば可能です。)御質問の場合は、INDIRECT関数と、「配列数式」を使います。
シート[顧客ID一覧]
シート[注文履歴]
INDIRECT関数と「配列数式」を使います。
シート[注文履歴]のセルB2には、「配列数式」で、
=IF(C2="","",INDIRECT("顧客ID一覧!A"&MIN(IF(C2=顧客ID一覧!$B$2:$B$65536,ROW(顧客ID一覧!$B$2:$B$65536)))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セルC2の値が、何も入力してない場合は、何も返しません。
文字列"顧客ID一覧!A"と、
もし、セルC2の値が、シート[顧客ID一覧]のセル範囲$B$2:$B$65536と等しい場合は、該当のシート[顧客ID一覧]のセル範囲$B$2:$B$65536の行番号の最小値を返し、
"A1"形式のセル参照式とします。
この場合"顧客ID一覧!A4"
へのセル参照式となります。このセルをコピーして、下のセル範囲に貼り付けます。
INDIRECT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
曜日別の売上を返す添付ファイルを参照してください。
「配列数式」の場合と「SUMIF関数」の場合の二つの例を紹介します。「配列数式」を使う場合を示します。
曜日は、WEEKDAY関数で求められます。
セルE5には、検索値となる月曜日としての数値
2
と入力します。
下のセル範囲E9まで、3,4,5,6の連続データを入力します。
その方法：このセルの右下端の「フィルハンドル」(マウスカーソルが[+]となります。)を[Ctrl]キーを押しながら下のセル範囲E9までドラッグします。セルE10には、
日曜日としての数値
1
と入力します。
セルに[月][火][水][木][金][土][日]と、曜日を表示するため、セル範囲[E5:E10]を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「aaa」と入力し、「ＯＫ」ボタンを押します。セルF5には、曜日に該当するデータの合計を返す数式を「配列数式」で
=SUM(IF(WEEKDAY($A$4:$A$34)=$E5,$C$4:$C$34))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もしのセル範囲$A$4:$A$34の曜日の数値(日曜日を1,月曜日2,火曜日3,・・土曜日7とした数値)が、セル$E5と等しい場合、該当のセル範囲$C$4:$C$34の値を返し、合計します。
このセルをコピーして、下のセル範囲セル範囲F6:F11に貼り付けます。同様に、セルG5には、曜日に該当するデータの数の合計を返す数式を「配列数式」で
=SUM(IF(WEEKDAY($A$4:$A$34)=$E5,1))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もしのセル範囲$A$4:$A$34の曜日の数値(日曜日を1,月曜日2,火曜日3,・・土曜日7とした数値)が、セル$E5と等しい場合、1を返し、合計します。
このセルをコピーして、下のセル範囲セル範囲G6:G11に貼り付けます。セルH5には、曜日に該当するデータの平均値を返す数式を「配列数式」で
=AVERAGE(IF(WEEKDAY($A$4:$A$34)=$E5,$C$4:$C$34))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もしのセル範囲$A$4:$A$34の曜日の数値(日曜日を1,月曜日2,火曜日3,・・土曜日7とした数値)が、セル$E5と等しい場合、該当のセル範囲$C$4:$C$34の値を返し、平均値を返します。
このセルをコピーして、下のセル範囲セル範囲H6:H11に貼り付けます。「SUMIF関数」を使う場合
曜日は、WEEKDAY関数で求められます。
セルD5には、セルA4の曜日を返す数式
=WEEKDAY(A4)
と入力します。
このセルをコピーして、下のセル範囲D5:D34に貼り付けます。セルE15には、検索値となる月曜日としての数値
2
と入力します。
下のセル範囲E9まで、3,4,5,6,7の連続データを入力します。
その方法：このセルの右下端の「フィルハンドル」(マウスカーソルが[+]となります。)を[Ctrl]キーを押しながら下のセル範囲E9までドラッグします。セルE21には、
日曜日としての数値
1
と入力します。セルに[月][火][水][木][金][土][日]と、曜日を表示するため、セル範囲[E6:E21]
を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「aaa」と入力し、「ＯＫ」ボタンを押します。セルF15には、曜日に該当するデータの合計を返す数式を
=SUMIF($D$4:$D$34,WEEKDAY($E15),$C$4:$C$34)
と入力します。
このセルをコピーして、下のセル範囲F16:F21に貼り付けます。セルG15には、曜日に該当するデータの個数を返す数式を
=COUNTIF($D$4:$D$34,WEEKDAY($E15))
と入力します。
このセルをコピーして、下のセル範囲G16:G21に貼り付けます。セルH15には、曜日に該当するデータの平均値を返す数式を
=F15/G15
と入力します。
このセルをコピーして、下のセル範囲H16:H21に貼り付けます。それぞれの関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
「数学+三角」
SUMIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
AVERAGE
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#AVEDEV
を、参照してください。
指定した数だけセルに指定した色を付ける添付ファイルを参照してください。
セルF2には、セルB2の値と、セルの列番号からセルに点数の数値を返す数式
=IF(COLUMN()-5<=$B2,$B$1,IF(COLUMN()-5<=$B2+$C2,$C$1,IF(COLUMN()-5<=$B2+$C2+$D2,$D$1,1)))
と入力します。数式の意味
もし、数式入力セルの列番号-5(この場合6-5=1)が、セル$B2の値以下の場合は、セル$B$1の値を返し、
もし、数式入力セルの列番号-5(この場合6-5=1)が、セル$B2+$C2の値以下の場合は、セル$C$1の値を返し、もし、数式入力セルの列番号-5(この場合6-5=1)が、セル$B2+$C2+$D2の値以下の場合は、セル$D$1の値を返し、それ以外は、1を返します。
セルには、[4]が返ります。このセルをコピーして、セル範囲F3:F4に貼り付けます。
このセルをコピーして、セル範囲G2:T4に貼り付けます。
セルの値によって、セルの色を変えるため、
セル範囲F2:T4を選択し、セルF2がアクティブの状態で、「書式」「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、「条件1」で、「セルの値が」を選択し、「次の値に等しい」で「4」と入力し、「書式」ボタンを押して、「パターン」の「色」を「赤」にして、「ＯＫ」ボタンを押します。
「追加」ボタンを押して、「条件付き書式の設定」ダイアログボックスで、「条件2」で、「セルの値が」を選択し、「次の値に等しい」で「3」と入力し、「書式」ボタンを押して、「パターン」の「色」を「水色」にして、「ＯＫ」ボタンを押します。
「追加」ボタンを押して、「条件付き書式の設定」ダイアログボックスで、「条件3」で、
「セルの値が」を選択し、「次の値に等しい」で「2」と入力し、「書式」ボタンを押して、「パターン」の「色」を「黄色」にして、「ＯＫ」ボタンを押します。「条件付き書式の設定」は、3つまでしかできません。
セルの値が、[1]の場合の条件付き書式設定はできませんので、セルの値に関係なく「黄緑」に設定します。
セル範囲「F1:T4」を選択し、「書式」「セル」を選択し、「パターン」の「色」を、「黄緑」にして「ＯＫ」ボタンを押します。セルの書式設定よりも「条件付き書式」の色が優先されます。
セルに数値を表示したくない場合は、セル範囲「F2:T4」を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「;;;」と入力し、「ＯＫ」ボタンを押します。
セルには、数値は表示されず、セルの「条件付き書式設定」によって、セルの値によって色塗りがされます。
期間が1ヵ月に満たない場合も、1ヵ月として在職期間を○年○ヵ月と返すにはEOMONTH関数を使います。
例えば
B1セルに、入社日2008/4/10
B2セルに、退職日2009/3/31
が入力してある場合
=DATEDIF(EOMONTH(B1,-1),EOMONTH(B2,0),"y")&"年"&DATEDIF(EOMONTH(B1,-1),EOMONTH(B2,0),"ym")&"ヵ月"
と入力します。
1年0ヵ月
が返ります。
数式の意味
セルB1の1ヵ月前の月末日(この場合2008/3/31)から、セルB2の月末日(この場合2009/3/31)
までの間の年を返し、文字列"年"と結合し、
セルB1の1ヵ月前の月末日(この場合2008/3/31)から、セルB2の月末日(この場合2009/3/31)
までの間の1年に満たない月数をかえし、文字列"ヵ月"と結合します。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
EOMONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#eomonth
を参照してください。
最小値と平均値に0とマイナスの値を除外して計算する「配列数式」を使います。
セル範囲A1:A5に、数値が入力してあるとします。
最小値は、
=MIN(IF(A1:A5>0,A1:A5))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲A1:A5の値が、0より大きければ、該当のセル範囲A1:A5の値の最小値を返します。平均値の場合は、
=AVERAGE(IF(A1:A5>0,A1:A5))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲A1:A5の値が、0より大きければ、該当のセル範囲A1:A5の平均値を返します。
ファイル名をセルに入力し、ファイルへの参照先を設定する方法INDIRECT関数を使います。
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
INDIRECT関数は、参照文字列を介した間接的なセルの指定を行います。参考になる質問が「計算式・関数」「2002/9/29」
「セルの"C"と"2"の文字列の示すセルC2の内容を参照したい
セルに入力したシート名"10月"でシートを指定してセルを参照したい」にあります。
御質問の場合は、
INDIRECT関数で、ファイルを参照する場合は、そのファイルが開いているか、ファイルの保存先のフォルダを記述しておく必要があります。
下に、参照式の例を示します。
セルA1にファイル名が、
0903.xls
と入力してあるとします。
そのファイルのシート[Sheet1]のセルC19を参照する場合、
=INDIRECT("["&A1&"]Sheet1!$C$19")
と入力します。参照文字列で他のブックを参照している場合 (外部参照)、そのブックを開いておく必要があります。参照先のブックを開いていない場合、INDIRECT 関数は、エラー値 #REF! を返します。
3つのセルに入力した数値2,5,4の中央値(4)を返すMEDIAN関数を使います。
A 1 2 2 5 3 4
メジアンとは、引数リストの数値を小さいものから大きなものに順に並べたとき、その中央にくる数値のことです。
つまり、メジアンより小さな数値と、メジアンより大きな数値の個数が等しくなります。最大値
=MAX(A1:A3)最小値
=MIN(A1:A3)中央値
=MEDIAN(A1:A3)
となります。
数式の意味
セル範囲A1:A3の、中央値を返します。
この場合2番目に小さい値は、4です。MADIAN関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MEDIAN
を、参照してください。
全角の文字列「平２１．３．９」 の表示を「平２１．０３．０９」とする方法別のセルに数式で、表示することになります。
まず、全角の文字列で入力した日付を、ASC関数で半角に変換する必要があります。
つぎに、文字列"平"を"H"に置き換えます。
その後、DATEVALUE関数で日付シリアル値として認識できるようにします。
セルの表示形式で[dbnum3]gge"."mm"."ddとし、これを全角表示とします。
具体的には、セルA1に、全角の文字列で
"平２１．３．９"
と入力してあるとします。
セルB1には、
=DATEVALUE(SUBSTITUTE(ASC(A1),"平","H"))
と入力します。
セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「[dbnum3]gge"."mm"."dd」と入力し、「ＯＫ」ボタンを押します。
添付ファイルを参照してください。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
SUBSTITUTE
http://kiyopon.sakura.ne.jp/kansuu/val.html#substitute
ASC
http://kiyopon.sakura.ne.jp/kansuu/val.html#asc
「日付+時刻」
DATEVALUE
http://kiyopon.sakura.ne.jp/kansuu/date.html#datevalue
を、参照してください。
数値(0.15)を指定すれば、表の縦軸(0～1.0)と横軸(0.00～0.09)の交点に対応する値を返す添付ファイルを参照してください。
ROUNDDOWN関数と、VLOOKUP関数と、MATCH関数を使います。
セルE19には検索値としての数値
0.15
が入力してあります。
この数値を、小数点以下1桁目の部分と、小数点以下2桁目の部分に分けるには、ROUNDDOWN
関数を使います。
=ROUNDDOWN(E19,1)
で、小数点以下1桁目の数値
この場合0.1
を返します。=E19-ROUNDDOWN(E19,1)
で、小数点以下2桁目の数値
この場合0.05
を返します。
小数点以下2桁目の値については、完全に合致するデータを返すため、TEXT関数で"0.00"
の表示に統一します。MATCH関数で、列に合致する小数点以下2桁目の数値の位置を返し、
VLOOKUP関数で、行に合致する小数点以下1桁目の値を検索します。セルF19には、検索値に対するリストの行の左端と列の上端の数値に合致する値を返す数式を「配列数式」で、
=VLOOKUP(ROUNDDOWN(E19,1),$B$3:$L$13,MATCH(TEXT(E19-ROUNDDOWN(E19,1),"0.00"),TEXT($C$2:$L$2,"0.00"),0)+1,0)
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
セルE19の値を小数点以下1桁で切り捨てた値(この場合0.1)を検索値として、
セル範囲$B$3:$L$13の左端列(この場合B列)を検索し、
セルE19の値-セルE19の値を小数点以下1桁で切り捨てた値(この場合0.15-0.1=0.05)
を、表示形式を"0.00"にして検索値として、セル範囲$B$2:$L$2表示形式を"0.00"にした値と完全に一致する値の位置を返し、
+1した列目の値を返します。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「丸め」
ROUNDDOWN
http://kiyopon.sakura.ne.jp/kansuu/round.htm#rounddown
「検索+行列」
VLOOKUP
http://kiyopon.sakura.ne.jp/kansuu/address.html#vlookup
MATCH
http://kiyopon.sakura.ne.jp/kansuu/address.html#match
を、参照してください。
