利用日と利用金額からクレジットカードの請求金額を計算する添付ファイルを参照してください。
クレジットカード名と締め日と支払日のセルには、計算でデータが求められようにデータのみ入力します。
具体的には、
セルF3には、
DC
と入力します。
DCカード:
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「@"カード:"」と入力し、「ＯＫ」ボタンを押します。
同様に、セルF4には、
VISA
と入力します。
VASAカード:
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「@"カード:"」と入力し、「ＯＫ」ボタンを押します。セルG3には、
15
と入力します。
15日締め、
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「@"日締め、"」と入力し、「ＯＫ」ボタンを押します。セルG4には、
4
と入力します。
4日締め、
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「@"日締め、"」と入力し、「ＯＫ」ボタンを押します。セルH3には、
10
と入力します。
翌月10日支払い
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「"翌月"@"日支払い"」と入力し、「ＯＫ」ボタンを押します。セルH4には、
4
と入力します。
翌月4日支払い
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「"翌月"@"日支払い"」と入力し、「ＯＫ」ボタンを押します。翌月の日付を返すには、
EDATE関数を使います。
日付を指定日にするには、
DATE関数に、
年を、YEAR関数で求め
月を、MONTH関数で求め+をプラスすれば翌月
日を、指定日
入力して求めます。
具体的には、セルD2には、セルB2のカード名に対する支払日を返す数式
=IF(AND(A2="",B2=""),"",DATE(YEAR(A2),MONTH(A2)+IF(DAY(A2)>VLOOKUP(B2,$F$3:$H$4,2,FALSE),2,1),VLOOKUP(B2,$F$3:$H$4,3,FALSE)))
と入力します。
数式の意味
もし、セルA2に何も入力してなく、かつ、セルB2も何も入力してない場合は、何も返しません。
セルA2の年と、セルA2の月と、
もし、セルA2の日と、次の値を比較し、
セル、B2を検索値として、セル範囲$F$3:$H$4の左端列(この場合F列)を検索し、該当する2列目(この場合G列)の値を返し、その値が、小さい場合は、2を、それ以外は1を返し、月とし、セルB2の値を検索値としてセル範囲$F$3:$H$4の左端列(この場合F列)を検索し、該当する、3列目の値を返し、日として、日付シリアル値を返します。
日付表示を
09/02/10
のようにするため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「yy/mm/dd」と入力し、「ＯＫ」ボタンを押します。このセルをコピーして、下のセル範囲に貼り付けます。
セルF7には、
DC
と入力します。
DCカード
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「@"カード"」と入力し、「ＯＫ」ボタンを押します。
同様に、セルF8には、
VISA
と入力します。
VASAカード
と表示するため、、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「@"カード"」と入力し、「ＯＫ」ボタンを押します。セルG7には、セル範囲A2:A28の日付の最新の日付から計算した、該当のカード名の支払日を返す数式
=DATE(YEAR(MAX(A$2:A$28)),MONTH(MAX(A$2:A$28))+IF(DAY(MAX(A$2:A$28))<VLOOKUP(F7,$F$3:$H$4,3,FALSE),2,1),VLOOKUP(F7,$F$3:$H$4,3,FALSE))
と入力します。
数式の意味
セル範囲A2:A28の最大の日付から、年、月と、
もし、本日の日付が、
セルF7の値を検索値として、セル範囲$F$3:$H$4の左端列(この場合F列)を検索し、該当する3列目(この場合G列)の値を返た日付が、大きい場合は、2を、それ以外は1を加算し、月とし、セルF7を検索値として、セル範囲$F$3:$H$4の左は端列(この場合F列)を検索し、該当する3列目(この場合H列)の値を返し、日として、日付シリアル値を返します。このセルをコピーして、下のセル範囲に貼り付けます。
セルH7には、該当の日付の支払額を返す数式を「配列数式」で入力します。
=SUM(IF($B$2:$B$28=$F7,IF($D$2:$D$28=$G7,$C$2:$C$28)))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$B$2:$B$28の値が、セル$F7と等しい場合、かつ、
もし、セル範囲$D$2:$D$28の値が、セル$G7と等しい場合、
該当のセル範囲$C$2:$C$28の値を返し、合計します。このセルをコピーして、下のセル範囲に貼り付けます。
セルHIには、該当の日付より大きい支払日の支払額を返す数式を「配列数式」で入力します。
=SUM(IF($B$2:$B$28=$F7,IF($D$2:$D$28>$G7,$C$2:$C$28)))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$B$2:$B$28の値が、セル$F7と等しい場合、かつ、
もし、セル範囲$D$2:$D$28の値が、セル$G7より大きい場合、
該当のセル範囲$C$2:$C$28の値を返し、合計します。このセルをコピーして、下のセル範囲に貼り付けます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
「統計関数」
MAX
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MAX
「検索+行列」
VLOOKUP
http://kiyopon.sakura.ne.jp/kansuu/address.html#vlookup
を参照してください。
複数の検索条件に合致するセルの文字列を返す添付ファイルを参照してください。
INDIRECT関数と「配列数式」を使います。
セルK6には、「配列数式」で、
=IF(AND(I6="",J6=""),"",INDIRECT("E"&MIN(IF($C$6:$C$35=I6,IF($D$6:$D$35=J6,ROW($E$6:$E$35))))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、セルI6に、何も入力してない場合、かつ、セルJ6に何も入力してない場合は、何も返しません。
それ以外は、文字列"E"と、
もし、セル範囲$C$6:$C$35の値が、セルI6と等しく、かつ、
もし、セル範囲$D$6:$D$35の値が、セルJ6と等しい場合は、
対象のセル範囲$E$6:$E$35の行番号の最小値を返し、文字列結合して、"A1"形式のセル参照式とします。このセルをコピーして、下のセル範囲に貼り付けます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
AND
http://kiyopon.sakura.ne.jp/kansuu/if.html#and「検索+行列」
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
複数の検索条件に合致するセルの値を合計する「配列数式」を使います。
添付ファイルを参照してください。
具体的には、
シート「1月度集計」のセルA1には、月の検索条件を
1
と入力します。
セルに
1月
と表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に、「#月」と入力し、「ＯＫ」ボタンを押します。セルB3には、「配列数式」で、
=SUM(IF(歴!$A$3:$A$17<>0,IF(MONTH(歴!$A$3:$A$17)=$A$1,IF(歴!$B$3:$B$17=$A3,歴!$C$3:$C$17))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、シート歴のセル範囲$A$3:$A$17の値が、0以外で、かつ、
もし、シート歴のセル範囲$A$3:$A$17の月が、セル$A$1の値と等しく、かつ、
もし、シート歴のセル範囲$B$3:$B$17の値が、セル$A3の値と等しい場合は、
シート歴のセル範囲$C$3:$C$17の値を返し、合計します。このセルをコピーして、下のセル範囲に貼り付けます。
シート「2月度集計」「3月度集計」も同様にします。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumを、参照してください。
１つの条件に合致するデータの合計値を求める添付ファイルを参照してください。
SUMIF関数を使います。
セルG3には、
=SUMIF($B$3:$B$12,F3,$C$3:$C$12)
と入力します。
このセルをコピーして、下のセル範囲に貼り付けます。
SUMIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学三角」
SUMIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
を、参照してください。
学年もわかる年齢早見表
添付ファイルを参照してください。
年齢は誕生日以後の満年齢です。誕生日までの年齢は記載の年齢より１を引いて下さい。セルL1には、本日の日付から西暦年を返す数式
=YEAR(TODAY())
と入力しています。
セルL1に、別の年(例えば2010)を上書きすれば、指定した年の年齢早見表となります。生年の列には、日付シリアル値(例えば、セルB3には、M40/1/1)を入力することで、西暦と年齢・学年を自動計算できるようにします。
元号の切り替わりの日付シリアル値については、明治→大正、大正→昭和、昭和→平成それぞれの元号を表示するため、２行を使っています。
セルに元号年を表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「e」と入力します。
これで、元号年を表示します。セルC3には、西暦年を返すため、
=B3
と入力し、セルに元号年を表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「yyyy」と入力します。
これで、西暦年を表示します。
このセルをコピーして、下のセル範囲に貼り付けます。セルD3には、年齢を返す数式
=IF(B3>=DATE($L$1,12,31),"",DATEDIF(B3,DATE($L$1,12,31),"y"))
と入力します。
数式の意味
もしセルB3の値が、セル$L$1の年の1月1日の日付より大きい場合は、何も返しません。
それ以外は、
セルB3の日付と、セル$L$1の年の12月21日の日付の間の満年数を返します。
このセルをコピーして、下のセル範囲に貼り付けます。学年は、年度の4月1日が切り替えですので、簡単にいきませんが、4月2日から12月31日(早生まれ以外)までの年齢に対応する学年として考えてください。
セルM3には、高校までの学年を返す数式を
=IF(AND(L3>18,L3<=22),"大学"&L3-18&"年生",IF(AND(L3>15,L3<=18),"高校"&L3-15&"年生",IF(AND(L3>12,L3<=15),"中学"&L3-12&"年生",IF(AND(L3>6,L3<=12),"小学"&L3-6&"年生",""))))
と入力します。
このセルをコピーして、下のセル範囲に貼り付けます。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
を、参照してください。
セルに、シートの総ページ数を表示したい
エクセル4.0のマクロを使います。
「挿入」「名前」定義で、「名前」に、「ページ数」と入力し、「参照範囲」に
「=GET.DOCUMENT(50) 」と入力し、「ＯＫ」ボタンを押します。
ページ数を表示したいセルに、名前への参照式
=ページ数
と入力します。また、ページ番号を自動で印刷するには、参考になる質問が「質問と回答」
の「印刷」「2007/02/18」
「フッダーにページ番号を-１-、-２-のように表示する」
にあります。
本日及び昨日の項目別の合計を出したい
添付ファイルを参照してください。
複数の検索条件に合致するセルの値を合計する場合は、「配列数式」を使います。セルD16には、本日の日付と、該当の項目に合致する成績の合計値を返す数式
=SUM(IF($B$2:$H$2=TODAY(),IF($A$4:$A$12=$B16,$B$4:$H$12)))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、セル範囲$B$2:$H$2の値が、本日と同じで、かつ、
もし、セル範囲$A$4:$A$12の値が、セル$B16と同じ場合は、対象のセル範囲$B$4:$H$12
の値の合計を返します。
このセルをコピーして、下のセル範囲に貼り付けます。同様に
セルC16には、昨日の日付と、該当の項目に合致する成績の合計値を返す数式
=SUM(IF($B$2:$H$2=TODAY()-1,IF($A$4:$A$12=$B16,$B$4:$H$12)))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、セル範囲$B$2:$H$2の値が、本日-1（昨日）と同じで、かつ、
もし、セル範囲$A$4:$A$12の値が、セル$B16と同じ場合は、対象のセル範囲$B$4:$H$12
の値の合計を返します。
このセルをコピーして、下のセル範囲に貼り付けます。
セルの中の文字列の先頭と最後の文字の後にダブルクォーテイションを入れたい
文字列結合関数[&]を使います。
具体的には、セルA1に文字が入力してあるとします。
セルB1には、
=""""&A1&""""
と入力します。
セルB1を「編集」「コピー」して、貼り付け先のセルで、「編集」「形式を選択して貼り付け」で「値」にチェックを入れ、「ＯＫ」ボタンを押します。また、「ダブルクォーテーション」アドインソフト があります。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/dubble.htm
選択範囲のセルのデータを"(ダブルクォーテーション)でくくります。
セルのショートカットメニューに「ﾀﾌﾞﾙｸｫｰﾃｼｮﾝ」が追加されます。
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
空白行を除いて番号をふるには
添付ファイルを参照してください。
セルA4には、
=IF(B4<>"",MAX($A$3:A3)+1,"")
と入力します。
数式の意味
もし、セルB4の値が入力してない以外は、セル範囲$A$3:A3の最大値+1の値を返し、それ以外は何も返しません。
このセルをコピーして、下のセル範囲に貼り付けます。
左に１の付く文字列セル数がいくつあるか数えたい
LEFT関数と「配列数式」を使います。
セルには、
=SUM(IF(LEFT($B$3:$B$10,1)="1",1))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$B$3:$B$10の左から、1文字取りだした文字が"1"の場合は、1を返し合計します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
を参照してください。
参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
「計算式・関数」「2002/9/2」
「セ ルに日付を入力したら隣のセルに前月26日を表示する」にあります。別の方法
A1セルに日付が2002/9/2と入力されている場合。
１ヶ月前は
=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))
１ヶ月後は
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
となります。
前月の26日は
=DATE(YEAR(A1),MONTH(A1)-1,26)
来月の25日は
=DATE(YEAR(A1),MONTH(A1)+1,25)
となります。それぞれの関数の説明は、「関数の使い方説明」
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
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
を参照してください。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
御質問の場合は次のようになります。
セルA1に日付が
2008/9/30
と入力してあるとします。
前月の21日は
=DATEVALUE(TEXT(EDATE(A1,-1),"yyyy/mm/")&"21")
当月の20日は
=DATEVALUE(TEXT(A1,"yyyy/mm/")&"20")
となります。
それぞれの表示を平成○年○月○日とするため
日付シリアル値の表示形式を
"ggge年m月d日"
とします。
具体的には、
セルB1に
平成20年8月21日〜9月20日
と表示するには、
=TEXT(DATEVALUE(TEXT(EDATE(A1,-1),"yyyy/mm/")&"21"),"ggge年m月d日") &"〜"&TEXT(DATEVALUE(TEXT(A1,"yyyy/mm/")&"20"),"ggge
年m月d日")
と入力します。
添付ファイルを参照してくださ い。
行番号39:45に、曜日に該当したデータを返す数式を入力しています。
セルA41には、
=A33
と入力します。
このセルをコピーして下のセル範囲に貼り付けます。セルB41には、該当日の曜日を、日曜日を1として返します。
=WEEKDAY(A41)
と入力します。
このセルをコピーして下のセル範囲に貼り付けます。セルA40には、
=MAX(B41:B45)
と入力します。セルC39には、
=C40
と入力します。
日付として表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を
選択し、「種類」欄に「d」と入力し、「ＯＫ」ボタンを押します。
このセルをコピーして右のセル範囲に貼り付けます。セルC40には、該当の月の1日から曜日数をシフトした日付を返し、曜日として表示します。
=$A$41-$A$40+COLUMN()-2
と入力します。
曜日として表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を
選択し、「種類」欄に「aaa」と入力し、「ＯＫ」ボタンを押します。
このセルをコピーして右のセル範囲に貼り付けます。セルC41には、列をシフトした値を返す数式
=IF(COLUMN()-$A$40+$B$41-$B41+IF($B41>$B$41,7,0)<2,0,INDIRECT("R"&ROW()-8&"C"&COLUMN()-$A$40+$B$41-$B41+IF($B41>$B$41,7,0),FALSE))
と入力します。数式の意味
数式入力セルの行番号(この場合3)-セル$A$40の値(この場合7)、+
セル$B$41の値(この場合3)-セル$B41の値(この場合3)+
もし、セル$B41の値が、セル$B$41の値より大きい場合は、7を返し、それ以外は、0を返します。
それらの合計値が、2未満の場合は、0を返します。
この場合は、0が返ります。
それ以外は、
文字列"R"と、数式入力セルの行番号(この場合41)-8と、
文字列"C"と、数式入力セルの列番号(この場合3)-セル$A$40の値(この場合7)+セル$B$41の値(この場合3)-セル$B41の値(この場 合3)+
もし、セル$B41の値(この場合3)が、セル$B$41の値(この場合3)より小さい場合は、7を、それ以外は、0を返し、"R1C1"形式のセル参照 式として返します。
(この場合は、R33-C4)へのセル参照ですからエラーです。このセルをコピーして下のセル範囲に貼り付けます。
右のセル範囲に貼り付けます。セル範囲A39:AM45で、折れ線グラフを作成します。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
WEEKDAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#weekday
「検索+行列」
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
別々のセルに入力した日付を2008/4/1〜2009/3/31と表示したいセルB3に開始日付が、
2008/4/1
セルD3に終了日付が、
2009/3/31
と、入力してあるとします。セルF3には、
=TEXT(B3,"yyyy/m/d")&"〜"&TEXT(D3,"yyyy/m/d")
と入力します。2008/4/1〜2009/3/31
が返ります。参考
セルB3の開始日付
2008/4/1
に対して、終了日付を年度末の、2009/3/31を自動で計算する式
セルD3には、
=date(year(b3)+1,3,31)
と入力します。TEXT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#textdate
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
year
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
を参照してください。
最終行に記載 されている数字の次の数字を自動的に表示したい添付ファイルを参照してください。
「配列数式」を使います。
シート[Sheet1]のセルB3以降に数値が入力してあります。
A B 1 2 表A 3 2 4 3 5 4 シート[Sheet2]のセルC11には、
=INDIRECT("Sheet1!B"&MAX(IF(Sheet1!$B$2:$B$65536<>"",ROW(Sheet1!$B$2:$B$65536))))+1
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
文字列"Sheet1!B"と、もし、セル範囲$B$2:$B$65536の値が、何も入力してない以外の場合は、該当のセル$B$2:$B$65536 の行番号の最大値(この場合5行目ですから5)を返し、"A1"形式のセル参照式とします。(この場合シート[Sheet1]のセルB5へのセル参照式と なり、4が返ります)
その値に1を加算します。(この場合4+1=5が返ります。)また、別の方法として、「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に、私の作成した「下端データの検索」ユーザー定義関数があります。
選択範囲のデータが入力されているもののうち、列の一番下端のデータを返します。
注意：データの定義として空白もデータとして認識しますので、空白を入力したセルを返すこともあります。値が0となる場合は、データとして認識しません。通常の組み込み関数と同じようにワークシート上で使用できる関数です。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/sita.htm
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/hasi.exe
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
Sheet1のV1,V5,V9,V13として以下同じ列で4行毎に参照式をドラッグしたいINDIRECT関数を使います。
具体的には、
A1セルに、シート「Sheet1」のV1セルのデータを
A2セルに、シート「Sheet1」のV5セルのデータを
参照させる式は
セルA1に、
=INDIRECT("Sheet1!V"&(ROW()-1)*4+1)
と入力します。
数式の意味
文字列"Sheet1!V"と、数式入力セルの行番号-1*4+1(この場合(1-1=0)*4+1=1)で、シート「Sheet1」のセル"V1"への セル参照となります。このセルをコピーして下のセルA2に貼り付ければ、
文字列"Sheet1!V"と、数式入力セルの行番号-1*4+1(この場合(2-1=1)*4+1=4+1=5)で、シート「Sheet1」のセル "V5"へのセル参照となります。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。
０９０ −２５６８−３７５６を０９０２５６７３７５６と−だけを消したい「置換」を使います。
まず、セルのデータ(０９０−２５６８−３７５６)を数値に変換した後でも、先頭に0が表示されるように、文字列の先頭に'アポストロフィを入力します。
'０９０−２５６８−３７５６次に、対象のセル範囲を選択し、「編集」「置換」を選択し、「検索する文字列」に「−」と入力し、「置換後の文字列」に何も入力しないで、 「全て置換」ボタンを押します。
あるいは、セル(例えばA1)に入力された値(０９０−２５６８−３７５６)を、数式でとなりのセルに"−"を無しにしたデータ （０９０２５６８３７５６）として返すには、
=SUBSTITUTE(A1,"−","")
と入力します。SUBSTITUTE関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
SUBSTITUTE
http://kiyopon.sakura.ne.jp/kansuu/val.html#substitute
を、参照してください。参考に、複数のセル範囲のデータに一度に先頭にアポストロフィを入力するには、「「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htmの 「アポストロフィ」アドインソフトを使宇ことで簡単に対応できます。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/appostrofie.htm
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/appostrofie.exe
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.htmlを 必ずお読みください。
データ未入力のセルに前の行の 値を入れたい
隣の列に数式を入力し、データをコピーすることで対応します。
添付ファイルを参照してください。
具体的には、A列に数値が入力してあるとします。
セルB4には、
A B 1 2 3 4 2001 2001 5 2001 6 2002 2002 7 2002 8 2003 2003 9 2003 10 2004 2004 11 2004 12 2005 2005
=IF(A4="",B3,A4)
と入力します。
数式の意味
もし、セルA4に何も入力してない場合は、セルB3の値を返しそれ以外はセルA4の値を返します。A列に値を貼り付けます。
このセルをコピーして、下のセル範囲に貼り付けます。
セル範囲B4:B32までコピーして、A4セルを選択し、「編集」「形式を選択して貼り付け」で、「値」にチェツクを入れ、「ＯＫ」ボタンを押します。不要となった、B列は削除します。
単一セル内の数値列 1234の合計10を表示したい「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に「配列」ユーザー定義関数があります。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/hairetu.html
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/hairetu.exe
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。A1セルに
1234
と入力してあるとします。
セルに
=SUM(配列(A1))
と入力します。配列(A1)
で、{1,2,3,4}
と言う一次配列が返りますので、その配列の合計をSUM関数でもとめます。
1+2+3+4の合計値
10
が返ります。
勤続年数を２０日締めで何年 何ヶ月かを返す参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
「計算式・関数」「2003/7/25」
「入社日と退社日を入力し、在職の期間は何年何ヶ月」にあります。御質問の場合は、20日締めですから、IF関数と、DAY関数で入社日を20以前と以後
で、返す月を変えればいいですね。
具体的には、
A1セルに入社年月日が
2000/7/15
と入力してあるとします。
B1セルには、現在の勤続年月を返す数式
=DATEDIF(DATE(YEAR(A1),MONTH(A1)+IF(OR(DAY(A1)>20,DAY(A1)<=7),0,1),DAY(A1)),TODAY(),"y")&"
年"&DATEDIF(DATE(YEAR(A1),MONTH(A1)+IF(OR(DAY(A1)>20,DAY(A1)<= 7),0,1),DAY(A1)),TODAY(),"ym")&"
ケ月"
と入力します。
数式の意味
セルA1の年と、セルA1の月に、もし、セルA1の日が、20より大きいか、もしくは
、セルA1の日が7以下の場合、0を返し、それ以外は、1を加算し、セルA1の日から
日付シリアル値とし、本日の日付との差の年を返します。
文字列"年"と
セルA1の年と、セルA1の月に、もし、セルA1の日が、20より大きいか、もしくは
、セルA1の日が7以下の場合、0を返し、それ以外は、1を加算し、セルA1の日から
日付シリアル値とし、本日の日付との差の１年未満の月数を返し、文字列"ケ月"
と結合します。7年11ヵ月
が返ります。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
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
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
TODAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#today
を参照してください。
TRANSPOSE 関数で、セルの行・列を入れ替え表示させるには添付ファイルを参照してください。
TRANSPOSE 関数ですね。
TRANSPOSE 関数は、配列の列数および行数とそれぞれ同数の行数および列数のセル範囲に、配列数式として入力する必要があります。TRANSPOSE 関数を利用すると、ワークシート上にある配列の縦と横を逆転させることができます。
セル範囲A11:A15を選択し、
=TRANSPOSE($A$1:$E$1)
と入力し、
{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。同様に、セル範囲A21:A25を選択し、
=TRANSPOSE($A$2:$E$2)
と入力し、
{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。同様に、セル範囲A28:A32を選択し、
=TRANSPOSE($A$3:$E$3)
と入力し、
{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。TRANSPOSE関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
TRANSPOSE
http://kiyopon.sakura.ne.jp/kansuu/address.html#transpose
を参照してください。
同じ列に入力した男女別の平均年 齢を返す添付ファイルを参照してください。
「配列数式」を使います。
セルC22には、
=AVERAGE(IF(B6:B19="男子",C6:C19))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もしセル範囲B6:B19の値が、"男子"と等しい場合は、該当するセル範囲C6:C19の平均値を返します。同様にセルC23には、
=AVERAGE(IF(B6:B19="女子",C6:C19))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もしセル範囲B6:B19の値が、"女子"と等しい場合は、該当するセル範囲C6:C19の平均値を返します。
同じ列に入力した データの本年・前年別々に順位をつける添付ファイルを参照してください。
「配列数式」を使います。
セルF2には、
=IF(COUNTIF($B$2:$B$13,$F$1)<ROW()-1,"",LARGE(IF($B$2:$B$13=F$1,$C$2:$C$13),ROW()-1))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$B$2:$B$13の値が、セル$F$1と同じセル数(木の場合4)が、数式入力セルの行番号-1(この場合2-1=1)より小さい場合 は、何も返しません。
もし、セル範囲$B$2:$B$13の値が、セルF$1と等しければ、セル範囲$C$2:$C$13の値の大きい方から、数式入力セルの行番号-1番目 (この場合2-1=1)の値を返します
。
このセルをコピーして、下のセル範囲F3]:F13に貼り付けます。
このセルをコピーして、右のセル範囲G3]:G13に貼り付けます。セルD2には、
=SUM(IF(B2=$F$1:$G$1,IF(C2=$F$2:$G$13,ROW($F$2:$G$13))))-1
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」
として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セルB2の値が、セル範囲$F$1:$G$1の値と同じで、かつ、もし、セルC2の値が、セル範囲$F$2:$G$13と同じ場合は、該当するセル 範囲$F$2:$G$13の行番号-1
数値を返します。
セルの書式設定で、＋の値の場合にのみ #"位"と表示するように、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、 「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「#位」と入力し、「ＯＫ」ボタンを押します。
このセルをコピーして、下のセル範囲D3:D13に貼り付けます。添付ファイルでは、前比の項目の順位も返しています。
Ａ列に企業コードを入れると、Ｂ列にYahooファイナンスへのハイパーリンクを設定したい添付ファイルを参照してください。
HYPERLINK関数を使います。
セルB9には、A9セルに入力した企業コードからハイパーリンクを作成する数式
=HYPERLINK("http://quote.yahoo.co.jp/q?s="&A9&"&d=c&m25,m75&t=3m&h=on")
と入力します。
数式の意味
文字列"http://quote.yahoo.co.jp/q?s="と、
セルA9と、
文字列"&d=c&m25,m75&t=3m&h=on"
を文字列結合関数&で結合し、ハイパーリンクを作成します。HYPERLINK関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
HYPERLINK
http://kiyopon.sakura.ne.jp/kansuu/address.html#hyperlink
を、参照してください。
コードをキーに、別 の一覧表から一部の項目を抜き出す添付ファイルを参照してください。セルB4には、リストから、A列に入力した検索値に該当する社名を返す数式
A b C D E F G H I J 1 （一覧表） 2 （出欠表） 3 社名 出席or欠席 コード 社名 出席 欠席 4 1111 AA 出席 1111 AA ○ 5 2222 BB 欠席 2222 BB ○
=IF(A4="","",VLOOKUP(A4,$G$3:$J$5,2,FALSE))
と入力します。
数式の意味
もし、セルA4の値が、何も入力してない場合は、何も返しません。
セルA4の値を検索値として、セル範囲$G$3:$J$5の左端列(この場合G列)を検索し、該当する行の2列目(この場合H列)の値を返します。セルC4には、複数の検索条件(この場合コードと出席の列に何か入力してある)を検索しますので、「配列数式」を使います。
=SUM(IF(A4=$G$4:$G$5,IF($I$4:$I$5<>"",1)))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、セルA4の値が、セル範囲$G$4:$G$5と等しく、かつ、もし、セル範囲$I$4:$I$5に何も入力してない以外（何か入力してあれば）、1 を返し、合計します。
出席の場合は、1が返り、それ以外は0が返ります。セルの表示を、セルの値が1の場合に"出席",
セルの値が0の場合に"欠席"
と表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し
、「種類」欄に「"出席";;"欠席"」と入力し、「ＯＫ」ボタンを押します。
書式の意味
セルの値が、正の値の書式;負の値の書式;0の値の書式
ですから、セルの値が1の場合"出席"
セルの値が、負の値の場合何も表示しません。
セルの値が、0の場合"欠席"
と表示します。
VLOOKUP関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」の
VLOOKUP
を、参照してください。
http://kiyopon.sakura.ne.jp/kansuu/address.html#vlookup
数値を0の桁で切り捨てしたいROUNDDOWNは、桁数に0を指定すると切り捨てになりません。
=ROUNDDOWN((118.54-118.55)/0.01,0)
ですと、数値は最も近い整数とてして四捨五入となります。
整数値に、切り捨てするには
=INT((118.54-118.55)/0.01)
とします。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「丸め」
ROUNDDOWN
http://kiyopon.sakura.ne.jp/kansuu/round.htm#rounddown
INT
http://kiyopon.sakura.ne.jp/kansuu/round.htm#int
を、参照してください。
随時変更される範囲にお ける検索値の数を返すには添付ファイルを参照してください。
複数の検索条件に合致するセル数を返すには、「配列数式」を使います。
セルD4には、
=SUM(IF($B$2:$B$13=$C4,IF($A$2:$A$13>=$D$2,IF($A$2:$A$13<=$F$2,1))))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、セル範囲$B$2:$B$13の値が、セル$C4と等しく、かつ、もし、セル範囲$A$2:$A$13
の値が、セル$D$2の値以上で、かつ、もし、セル範囲$A$2:$A$13の値が、セル$F$2
以下の場合は、1を返し、合計します。このセルをコピーして、下のセル範囲に貼り付けます。
これで、A:B列のデータから変更になっても自動的に記号とNo.から該当の条件に合致するデータの数を集計できます。
選択セルに同じ数 字を乗算（除算、加算、減算）する方法1.2倍にする場合関係のないセルに
1.2
と入力します。
そのセルを選択して「コピー」します。1.2倍にしたいセル範囲を選択します。
「編集」「形式を選択して貼り付け」を選択し、「演算」の「乗算」のボタンを押し、「ＯＫ」ボタン押します。同様にセル範囲を選択して、「編集」「形式を選択して貼り付け」を選択し、「演算」の「除算」や「加算」あるいは「減算」のラジオボタンを 押せば、選択中のセルの値を一度に指定数の除算」や「加算」あるいは「減算」ができます。
当月の日付のセルに色を付けるセル範囲A1:A100を選択し、A1セルがアクティブの状態で、「書式」「条件付き書式」を選択し、「条件付き書式の設 定」ダイアログボックスで、「条件１」で、
「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
を選択し、「=MONTH(A1)=MONTH(today())」と入力し、 「書式」ボタンを押して、「フォント」（あるいは「パターン」）
の「色」を「橙色」等にして、「ＯＫ」ボタンを押します。数式の意味
セルA1の月と、本日の月が等しい場合
となります。
５月の第１日曜日、第３ 土曜日、第３日曜日を返す参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
の「計算式・関数」「2005/12/28」
「指定した月の指定した「曜日」に対応する「日」を表示させる」にあります。参考になる質問が「質問と回答」の「計算式・関数」「2005/6/29」
「日付に応じたその週の始まり(月曜日）の日付を表示するには」にあります。御質問の場合は、次のようになります。
添付ファイルを参照してください。セルA1に対象の月の初日を日付シリアル値で
H20/5/1
のように入力します。
その月の月始めの日曜日を返すのは、
B1セルに曜日を指定する数値
1(月曜)?7(日曜)の範囲の整数
を入力します。
A B C D E F G H 1 5月 曜日 2 第1 日 7 7 5月4日(日) 月 1 3 第3 土 6 20 5月17日(土) 火 2 4 第3 日 7 21 5月18日(日) 水 3 5 木 4 6 金 5 7 土 6 8 日 7 セルA1には、該当月の1日を、日付で、
5/1
のように入力します。
セルA2には、第1週を示す
1
を入力します。
セルに「第1」と表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー 定義」を選択し「種類」欄に「"第"#」と入力し、「ＯＫ」ボタンを押します。セルB2には、曜日をリストから入力できるように、セルを選択し、「データ」「入力規則」で、「データの入力規則」ダイアログボックスの 「設定」タグを開き、「入力値の種類」で、「リスト」を選択し、「元の値」にセル範囲「=$G$2:$G$8」を選択入力し、「ＯＫ」ボタンを押します。
セル範囲G2:H8には、曜日の選択入力と曜日の計算のため月曜日を1として、に日曜日を7とするリストを入力します。セルC2には、セルB2の曜日に対する数値を返す数式
=VLOOKUP(B2,$G$2:$H$8,2,FALSE)
と入力します。
数式の意味
セルB2を、検索値として、セル範囲$G$2:$H$8の左端列(この場合G列)を検索し、該当する2列目の値を返します。セルD2には、セルC2の数値が週の初めから何日かを返す数式
=(A2-1)*7+C2
と入力します。セルE2には、該当月の指定した曜日の日付を返す数式
=$A$1+$D2-WEEKDAY($A$1,2)+IF(WEEKDAY($A$1,2)>$D2,7,0)
と入力します。
数式の意味
=$A$1+$D2-WEEKDAY($A$1,2)+IF(WEEKDAY($A$1,2)>$D2,7,0)
A1セルの値+セルD2の数値-セルA1の曜日に対する数値(1(月曜)?7(日曜)の範囲の整数)+もしA1セルの曜日に対する数値がセルD2の数値よ り大きい場合は、7を加算します。
セルには、日付シリアル値
39572
が返ります。この日付シリアル値を日付表示にするには、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」 の「分類」で「ユーザー定義」を選択し、「種類」欄に「m月d日(aaa)」と入力し、「ＯＫ」ボタンを押します。
セルには、
5月4日(日)
が返ります。WEEKDAY関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」WEEKDAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#weekday
を参照してください。
セ ル範囲A1:A100で、数値の偶数が何個入っているか個数を表示したいMOD関数で、2で割った余りが、0であるセル数を「配列数式」で合計します。
具体的には、セル範囲A1:A100までに偶数が入力してあるセル数を合計するには、
=SUM(IF(A1:A100>0,IF(MOD(A1:A100,2)=0,1)))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。、
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲A1:A100の値が、0より大きく、かつ、セル範囲A1:A100の値を、2で割った余りが、0の場合は、1を返し、その合計を返しま す。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
MOD
http://kiyopon.sakura.ne.jp/kansuu/abs.html#mod
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を、参照してください。
同じ名称のセルの複数列の 数値の小計を出したい
添付ファイルを参照してください。COUNTIF関数で、名称が同じセルの先頭のセルかどうかを返し、SUMIF関数で、合計を返します。
セルD2には、
=IF(COUNTIF($A$2:$A2,$A2)=1,SUMIF($A$2:$A$11,A2,$B$2:$B$11)+SUMIF($A$2:$A$11,A2,$C$2:$C$11),"")
と入力します。
数式の意味
もし、セル範囲$A$2:$A2の値が、セルA2と等しい場合、そのセル数を返し、その値が1の場合、
セル範囲$A$2:$A$11の値が、セルA2と等しい場合、対象のセル範囲$B$2:$B$11の値を合計して返します。
その値に、次の値を合計します。
セル範囲$A$2:$A$11の値が、セルA2と等しい場合、対象のセル範囲$C$2:$C$11の値を合計して返します。このセルをコピーして下のセル範囲に貼り付けます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countifSUMIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
を、参照してください。
指定日までの予算と実績の合計方法
添付ファイルを参照してください。セルG2には、「配列数式」で次のように入力します。
=SUM(IF($A$3:$A$12<=H1,$B$3:$B$12))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$3:$A$12の値が、セルH1以下の場合、セル範囲$B$3:$B$12の値を返し、合計します。セルG3には、「配列数式」で次のように入力します。
=SUM(IF($A$3:$A$12<=H1,$C$3:$C$12))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$A$3:$A$12の値が、セルH1以下の場合、セル範囲$C$3:$C$12の値を返し、合計します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
RIGHT
http://kiyopon.sakura.ne.jp/kansuu/val.html#right
「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
FIND
http://kiyopon.sakura.ne.jp/kansuu/val.html#find
「情報関数」
ISERRPOR
http://kiyopon.sakura.ne.jp/kansuu/cell.html#iserror
を、参照してください。
数値を3桁の桁区切り毎に別のセルに全角で表示 させる
添付ファイルを参照してください。
対象の値が、マイナスの場合は、数値の先頭に▲を表示します。
セルの書式設定を「標準」にしておく必要があります。
セルB2には、
=IF(LEN(ABS($F2))>=6,IF($F2<0,"▲","")&JIS(LEFT(ABS($F2),LEN(ABS($F2))-6)),"")
と入力します。
数式の意味
もし、セル$F2の絶対値の文字数が6以上の場合、かつ、
もし、セル$F2の値が、マイナスの場合、"▲"を、それ以外は長さ0の文字列""を次の値と結合します。
セル$F2の絶対値の文字を左から、セル$F2の絶対値の文字数-6文字取り出し、全角表示します。
それ以外は何も表示しません。
セルC2には、
=IF(LEN(ABS($F2))>=6,JIS(MID(ABS(F2),LEN(ABS($F2))-5,3)),IF(AND(LEN(ABS($F2))>=3,LEN(ABS($F2))<6),IF($F2<0,"▲","")&JIS(LEFT(ABS($F2),LEN(ABS($F2))-3)),""))
と入力します。
数式の意味
もし、セル$F2の絶対値の文字数が6より大きい場合、セルF2の文字をセル$F2の絶対値の文字数-5文字目から、3文字取り出し、全角表示します。
それ以外は、もし、セル$F2の絶対値の文字数が、3以上で、かつ、
セル$F2の絶対値の文字数が、6未満の場合は、"▲"を、それ以外は長さ0の文字列""を次の値と結合します。
セル$F2の絶対値の文字の左から、セル$F2の絶対値の文字数-3文字取り出し、全角表示します。
それ以外は何も表示しません。
セルD2には、
=IF(F2<>"",IF(AND(LEN(ABS($F2))<3,$F2<0),"▲","")&JIS((RIGHT(ABS(F2),3))),"")
と入力します。
数式の意味
もし、セルF2の値が、何も入力していない以外の場合、
セルF2の絶対値の文字数が、3未満で、かつ、
セルF2の値が、マイナスの場合は、
それ以外は長さ0の文字列""を次の値と結合します。
セルF2の絶対値の文字の右から3文字取り出し、全角表示します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
LEN
http://kiyopon.sakura.ne.jp/kansuu/val.html#len
RIGHT
http://kiyopon.sakura.ne.jp/kansuu/val.html#right
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
VALUE
http://kiyopon.sakura.ne.jp/kansuu/val.html#value「数学+三角」
INT
http://kiyopon.sakura.ne.jp/kansuu/abs.html#INT
を、参照してください。
日付の2007/05/01を文 字列の20070501にするA1セルに日付が日付シリアル値
2007/05/01
と入力してあるとします。
そのまま文字列に変換すると39203となってしまいます。
B1セルに
=TEXT(A1,"yyyymmdd")
と入力します。
B1セルを編集」「コピー」して、「編集」「形式を選択して貼り付け」で、「値」として貼り付けます。
TEXT関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を、参照してください。
月平均を算出するために該当月の日数を返したい月の日数を返すには、EOMONTH関数とDAY関数を使います。
A1セルに日付入力形式で
2008/4/28
と入力してあるとします。
A1セルの日付から該当月の日数を返す数式
=DAY(EOMONTH(A1,0))
と入力します。
数式の意味
セルA1の月末日を日付シリアル値で返し、その日付を返します。
この場合、数値の
30
が返ります。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
EOMONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#eomonth
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
を、参照してください。
9月21日から3月20日は、3月20日を、3月21日から9月20日までは9月20日を定年退職日とする
A B C 1 生年月日 60歳の年月日 退職日 2 1940/3/19 2000/3/19 2000/3/20 3 1940/3/20 2000/3/20 2000/3/20 4 1940/3/21 2000/3/21 2000/9/20 5 1940/9/19 2000/9/19 2000/9/20 6 1940/9/20 2000/9/20 2000/9/20 7 1940/9/21 2000/9/21 2001/3/20 セルA2に、生年月日が入力してあるとします。
セルB2に、満60歳になる日を返すには、
=EDATE(A2,60*12)
と入力します。
数式の意味
セルA2の日付から60年×12ヵ月後の日付を返します。セルC2に、満60歳の直後の
9月21日から3月20日は、3月20日を、
3月21日から9月20日までは9月20日を
定年退職日とするを返すには
=IF(OR(VALUE(TEXT(B2,"mmdd"))>=921,VALUE(TEXT(B2,"mmdd"))<=320),DATE(YEAR(B2)+IF(AND(VALUE(TEXT(B2,"mmdd"))>=921,MONTH(B2)>=9),1,0),3,20),DATE(YEAR(B2),9,20))
と入力します。
数式の意味
もし、セルB2の月日が、921以上、または、セルB2の月日が、320以下の場合、
セルB2の年に、もし、セルB2の月日が、921以上場合、かつ、セルB2の値が、9以
上の場合、1それ以外は、0を加算して年とし、月を、3、日を20として日付を返し
ます。
それ以外は、セルB2の年と、月を9、日を20として日付を返します。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
VALUE
http://kiyopon.sakura.ne.jp/kansuu/val.html#value
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「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
AND
http://kiyopon.sakura.ne.jp/kansuu/if.html#and
OR
http://kiyopon.sakura.ne.jp/kansuu/if.html#or
を、参照してください。
82.05 を1982/5/1に06.02.14を2006/2/14に変更したいセルC2には、B2セルの文字列82.05を、日付の1982/5/1にする数式
=DATE(IF(INT(B2)<50,2000,1900)+INT(B2),(B2-INT(B2))*100,1)
と入力します。
数式の意味
もし、セルB2の整数値の値が、50より小さい場合は、2000を、それ以外は1900を返し、セルB2の整数値を加算して(この場合1900+82= 1982)年とし、
セルB2の値からセルB2の値の整数値を引いた値を100倍(この場合0.05*100=5)して月とし、日を1として日付シリアル値を返します。
この場合1982年5月1日となります。セルF2には、E2セルの文字列06.02.14を、日付の2006/2/14にする数式
=DATE(IF(VALUE(LEFT(E2,2))<50,2000,1900)+LEFT(E2,2),MID(E2,4,2),RIGHT(E2,2))
と入力します。
数式の意味
セル、E2の左から、2文字を取りだして数値とし、その値が50より小さい場合は、2000を、それ以外は1900を返し、その値と、セルE2の左から2 文字取り出して、年とし、セル、E2の4文字目から2文字取り出して、月とし、セルE2の右から2文字取り出して、日として日付シリアル値を返します。
――――――――――――――――――――――――――――――
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
「数学+三角」
INT
http://kiyopon.sakura.ne.jp/kansuu/val.html#value
「文字列関数」
LEFT
http://kiyopon.sakura.ne.jp/kansuu/val.html#left
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
RIGHT
http://kiyopon.sakura.ne.jp/kansuu/val.html#right
VALUE
http://kiyopon.sakura.ne.jp/kansuu/val.html#value
を、参照してください。
開始年月日〜終了年 月日までの平日・土・日・祝日の回数をそれぞれ出したい添付ファイルを参照してください。
複数の検索条件に合致するデータ数を返すには、「配列数式」を使います。
まず、シート[Sheet2]のA列に「日」,B列に「曜日」,C列に「祝日」を入力します。
計算に使用するのはA列の「日」と、C列の「祝日」の文字です。シート{Sheet1]のセルD1には、期間中の平日の日数を返す数式
=SUM(IF(Sheet2!$A$2:$A$33>=Sheet1!$B$1,IF(Sheet2!$A$2:$A$33<=Sheet1!$C$1,IF(WEEKDAY(Sheet2!$A$2:$A$33,2)<=5,IF(Sheet2!$C$2:$C$33<>"
祝日",1)))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$B$1以上で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$C$1以下で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の曜日を月曜を1とした場合、5以下の場合で、かつ、
もし、シート「Sheet2」のセル範囲$C$2:$C$33の値が、"祝日"以外の場合は、1を返し、合計します。同様に、セルD2には、
=SUM(IF(Sheet2!$A$2:$A$33>=Sheet1!$B$2,IF(Sheet2!$A$2:$A$33<=Sheet1!$C$2,IF(WEEKDAY(Sheet2!$A$2:$A$33,2)=6,IF(Sheet2!$C$2:$C$33<>"
祝日",1)))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$B$2以上で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$C$2以下で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の曜日を月曜を1とした場合、6の場合で、かつ、
もし、シート「Sheet2」のセル範囲$C$2:$C$33の値が、"祝日"以外の場合は、1を返し、合計します。同様に、セルD3には、
=SUM(IF(Sheet2!$A$2:$A$33>=Sheet1!$B$3,IF(Sheet2!$A$2:$A$33<=Sheet1!$C$3,IF(WEEKDAY(Sheet2!$A$2:$A$33,2)=7,IF(Sheet2!$C$2:$C$33<>"
祝日",1)))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$B$3以上で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$C$3以下で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の曜日を月曜を1とした場合、7の場合で、かつ、
もし、シート「Sheet2」のセル範囲$C$2:$C$33の値が、"祝日"以外の場合は、1を返し、合計します。
同様に、セルD4には、
=SUM(IF(Sheet2!$A$2:$A$33>=Sheet1!$B$4,IF(Sheet2!$A$2:$A$33<=Sheet1! $C$4,IF(Sheet2!$C$2:$C$33="祝日",1))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$B$4以上で、かつ、
もし、シート「Sheet2」のセル範囲$A$2:$A$33の値が、シート「Sheet1」のセル$C$4以下で、かつ、
もし、シート「Sheet2」のセル範囲$C$2:$C$33の値が、"祝日"以外の場合は、1を返し、合計します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
WEEKDAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#weekday
を、参照してください。
セルに入力した19900612から年齢を返すセルA1に、数値で
19900612
と入力してあるとします。セルB1に年齢を返すには、
=DATEDIF(DATEVALUE(TEXT(A1,"####""/""##""/""##")),NOW(),"Y")
と入力します。数式の意味
セルA1の文字列から表示形式を、"####""/""##""/""##"として取り出します。
(この場合、文字列"1990/06/12"が返ります)
その文字列を日付シリアル値に変換します。
その日付シリアル値を開始日として、現在の日付と時刻を終了日として、その期間の満年数を返します。それぞれの関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
「日付+時刻」
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
DATEVALUE
http://kiyopon.sakura.ne.jp/kansuu/date.html#datevalue
「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を、参照してください。
A1セルの=NOW ()からB1セルに時を表示し、C1セルに分を表示するHOUR関数で、時を取りだし、MINUTE関数で分を取り出します。
具体的には、
B1セルに
=HOUR(A1)C1セルに
=MINUTE(A1)
と入力します。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「日付+時刻」の
HOUR
http://kiyopon.sakura.ne.jp/kansuu/date.html#hour
MINUTE
http://kiyopon.sakura.ne.jp/kansuu/date.html#minute
を、参照してください。
リストから重複しないデータのみを返す添付ファイルを参照してください。
「COUNTIF」関数を使います。
まず、リストの重複しているデータを返すために、セルC2には、重複データ数を返す数式
=COUNTIF($A$2:A2,A2)
と入力します。
数式の意味
もし、セルし範囲$A$2:A2の値がセル、A2の値と等しければその数を返します。
1が返ったセルが、重複データの一番初めのデータになります。
このセルをコピーして下のセル範囲に貼り付けます。
「データ」「フィルタ」「オートフィルタ」で、C列「1」を抽出してコピー貼り付けすれば、重複しないデータが取り出せます。
あるいは、計算式で、常にセルに重複しないデータを返す方法を紹介します。
「配列数式」を使います。
先ほどのC列にはCOUNTIF関数を入力して、重複データの最初の1を抽出することで対応します。セルE2には、
=IF(COUNTIF($C$2:$C$22,1)<ROW()-1,"",INDIRECT("A"&SMALL(IF($C$2:$C$22=1,ROW($C$2:$C$22)),ROW()-1)))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$C$2:$C$22の値が、1のセル数の合計(この場合6)が、、数式入力セルの行番号-1(この場合2-1=1)より大きい場合は、何 も返しません。
文字列"A"と、もし、セル範囲($C$2:$C$22の値が、1の場合、そのセル範囲$C$2:$C$22の行番の、数式入力セルの行番号-1(この場 合2-1=1)番目に小さい値(この場合セ
ルC2で、2行目ですから2)を文字列結合関数&で結合し、"A1"形式のセル参照式とします。
この場合、"A2"へのセル参照式が返ります。
このセルをコピーして下のセル範囲に貼り付けます。同様に、F2には、
=IF(COUNTIF($C$2:$C$22,1)<ROW()-1,"",INDIRECT("B"&SMALL(IF($C$2:$C$22=1,ROW($C$2:$C$22)),ROW()-1)))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲$C$2:$C$22の値が、1のセル数の合計(この場合6)が、、数式入力セルの行番号-1(この場合2-1=1)より大きい場合は、何 も返しません。
文字列"B"と、もし、セル範囲($C$2:$C$22の値が、1の場合、そのセル範囲$C$2:$C$22の行番の、数式入力セルの行番号-1(この場 合2-1=1)番目に小さい値(この場合セルC2で、2行目ですから2)を文字列結合関数&で結合し、"A1"形式のセル参照式と
します。
この場合、"B2"へのセル参照式が返ります。
このセルをコピーして下のセル範囲に貼り付けます。それぞれの関数の説明は、「関数の使い方説明」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
「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を、参照してください。
セル範囲の数値から70に近い数字を抽出する 方法添付ファイルを参照してください。
セル範囲A1:A100に数値が入力してあるとします。
70に近い数値を返すには、セル範囲の数値から70を引いて、その絶対値が一番小さい値を返すことになります。
その場合、「配列数式」で複数のセルの数値から70を引きます。
具体的には、次のようになります。
A1:A100に数値が入力してあるとします。
B1に検索値としての数値
70
が入力してあるとします。
=MIN(IF(MIN(ABS(A1:A100-B1))=ABS(A1:A100-B1),A1:A100))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲A1:A100の値-B1セル(この場合70)の値を引いた絶対値の位置番小さい値が、セル範囲A1:A100の値-B1セル(この場合 70)の絶対値と等しい場合、該当のセル範囲A1:A100の最小値値を返
します。この数式では、70に近い値で、小さい値を返します。
70との差が同じ、1である
69
71
とあれば、
69
を返します。
別シートの前年当月までの累計対比の計算式添付ファイルを参照してください。
「配列数式」を使います。
シート「H19」のセルO10には、
=O9/SUM(INDIRECT("'Ｈ18'!R9C3:R9C"&MAX(IF($C$10:$N$10>0,COLUMN($C $10:$N$10))),FALSE))*100
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
セルO9の値÷
文字列"'Ｈ18'!R9C3:R9C"と、もし、セル範囲$C$10:$N$10の値が、0より大きい場合、その列番号の最大値(この場合13)を返 し、文字列結合関数&で文字列結合し、"R1C1"形式のセル参照式とし(この場合'Ｈ18'!R9C3:R9C13へのセル参照式となりま す。)
セル範囲の数値を合計します。
その値*100を返します。
一つのセルに入力した文字列"5 10 20 30"の合計65を別のセルに返す添付ファイルを参照してください。
このファイルには、「数式の計算」ユーザー定義関数が添付してあります。
A1セルに
"5 10 20 30"
と入力してあるとします。
全角文字を半角に置き換え、半角スペース" "を、"+"という文字列に置き換えま
す。
=SUBSTITUTE(ASC(A1)," ","+")
と入力します。
文字列
5+10+20+30
が返ります。それぞれの関数の説明は、「関数の使い方説明」
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
を、参照してください。次に、文字列の数式を計算するためには、
「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に「数式の計算」ユーザー定義関数があります。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/susikikeisan.htm
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。これを利用すれば、
=数式の計算(SUBSTITUTE(ASC(A1)," ","+"))
と入力します。
65が返ります。
VLOOKUPの範囲で 他ブック(あるいはシート)のリストを選びたいまず、検索対象のリスト範囲を選択し、「挿入」「名前」「定義」で、「リスト」などと名前を付けます。
そうすれば、セル範囲をドラッグしなくても名前を入力することで、対応できます。
具体的には、
ブック"Book3"のセル範囲A1:B10に、「リスト」という名前を定義したとします。ブック"Book3"を開いた状態で、
別のブックの、セルA1に検索値を入力し、
セルB1に、数式
=VLOOKUP(A1,Book3!リスト,2)
と入力します。検索対象のブック"Book3"を閉じると、数式は
=VLOOKUP(A1,'C:\保存先フォルダ名\Book3.xls'!リスト,2)
のようになります。
各セルを1の位10の位100 の位・・・にした時の合計の出し方添付ファイルを参照してください。
二つの例を示します。
シート「例1」では、数値をセルに一桁毎に入力した場合の計算例を示します。
一桁毎に数値を入力するのは、非常に面倒ですね。
セル範囲A2からF2にそれぞれの数値が一桁ずつ入力してあります。
A B C D E F G 1 見 積 金 額 (円） 2 1 0 5 0 0 10,500 3 4 0 2 0 0 40,200 4 3 0 1 5 0 30,150 5 2 0 1 0 0 20,100 6 6 0 3 0 0 60,300 7 7 0 3 5 0 70,350 8 8 0 4 0 0 80,400 9 1 0 5 0 0 10,500 10 9 0 4 5 0 90,450 11 4 2 0 0 4,200 12 3 1 5 0 3,150 13 4 2 0 3 0 0 420,300
セルG2には、つの列の一桁毎の数値を、1つの数値にする数式
=IF(SUM(A2:F2)=0,0,VALUE(A2&B2&C2&D2&E2&F2))
と、入力します。
数式の意味
もし、セル範囲A2:F2の合計値が、0の場合、0を返します。
それ以外は、セルAと、セルB2と、セルC2と、セルD2と、セルE2と、セルF2の値を文字列結合して1つの文字列として、数値に変換します。
このセルをコピーして、下のセル範囲G3:G12に貼り付けます。セルG13には、数値の合計を返す数式
=SUM(G2:G12)
と入力します。セルA13には、セル$G13の値の、10万の位の数値を返す数式
=IF(LEN($G13)<COLUMN($G13)-COLUMN(),"",VALUE(MID($G13,LEN($G13)-(COLUMN($G13)-COLUMN()-1),1)))
と入力します。
数式の意味
もし、セル$G13の文字数(この場合6)より、セル$G13の列番号-数式入力セルの列番号(この場合7-1=6)より小さい場合は、何も返しません。
セル$G13の文字列の、セル$G13の文字数(この場合6)-セル$G13の列番号-数式入力セルの列番号-1(この場合6-(7-1-1)=6-5= 1)番目の文字から、1文字取り出します。
この場合、420300の1番目の文字列から、1文字取り出します。このセルをコピーして、右のセル範囲B13:H13まで貼り付けます。
G列を選択し、「書式」「列」を選択し、「表示しない」設定にするか、セル範囲G2:G13を選択し、「書式」セルを選択し、「書式」「セ ル」を選択し、「セルの書式設
定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し 、「種類」欄に「;;;」と入力して、表示しないようにすればいいです ね。
――――――――――――――――――――――――――――――
シート「例2」では、数値をそのままG2:G12に入力すれば、自動的にA列からF列の各セルに位に応じた数値を返し、計算結果もそれに応じて表示しま す。
セルG13には、数値の合計を返す数式
=SUM(G2:G12)
と入力します。セルA13には、前の例で示した数式と同じ数式を入力します。
=IF(LEN($G13)<COLUMN($G13)-COLUMN(),"",VALUE(MID($G13,LEN($G13)-(COLUMN($G13)-COLUMN()-1),1)))
と入力します。
数式の意味
もし、セル$G13の文字数(この場合6)より、セル$G13の列番号-数式入力セルの列番号(この場合7-1=6)より小さい場合は、何も返しません。
セル$G13の文字列の、セル$G13の文字数(この場合6)-セル$G13の列番号-数式入力セルの列番号-1(この場合6-(7-1-1)=6-5= 1)番目の文字から、1文字取り出します。
この場合、420300の1番目の文字列から、1文字取り出します。このセルをコピーして、セル範囲A2:G13まで貼り付けます。
G列を選択し、「書式」「列」を選択し、「表示しない」設定にするか、セル範囲G2:G13
を選択し、「書式」セルを選択し、「書式」「セル」を選択し、「セルの書式設
定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択
し 、「種類」欄に「;;;」と入力して、表示しないようにすればいいですね。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
LEN
http://kiyopon.sakura.ne.jp/kansuu/val.html#len
MID
http://kiyopon.sakura.ne.jp/kansuu/val.html#mid
VALUE
http://kiyopon.sakura.ne.jp/kansuu/val.html#value
「検索+行列」
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
を、参照してください。
販売数が同じでも1から3位の名前と個数 を表示したい添付ファイルを参照してください。
セルD2には、順位を返す数式
=RANK(B2,$B$2:$B$44)
と入力します。
数式の意味
セルB2の値が、セル範囲$B$2:$B$44の値の大きい方から何番目の順位かを返します。
このセルをコピーして、下のセル範囲D3:D44に貼り付けます。
ここでは、重複した販売個数の場合は同じ順位が返ります。セルE2には、重複した順位の場合は、その重複累計を返素数式
=COUNTIF($D$2:D2,D2)
と入力します。
数式の意味
もし、セル範囲$D$2:D2の値が、セルD2と同じ場合はその累計を返します。
このセルをコピーして下のセル範囲E3:E44に貼り付けます。
1の数値となったセルのD列の値が重複しない販売個数となります。セルH2には、1番の順位の販売個数を返す数式
=LARGE($F$2:$F$44,1)
と入力します。
数式の意味
セル範囲$F$2:$F$44の一番大きな数値を返します。セルH3には、2番目の順位の販売個数を返す数式
=LARGE($F$2:$F$44,2)
と入力します。
数式の意味
セル範囲$F$2:$F$44の二番目に大きな数値を返します。セルH2には、3番目の順位の販売個数を返す数式
=LARGE($F$2:$F$44,3)
と入力します。
数式の意味
セル範囲$F$2:$F$44の三番目に大きな数値を返します。
項目毎の最大値を求める添付ファイルを参照してください。
A B C D E F 1 区分 区分連番 氏名 2 1 101 Ａ 3 1 102 Ｂ 4 1 103 Ｃ 5 1 104 Ｄ 区分 連番の最大値 6 2 101 あ 1 104 7 2 103 い 8 1 105 う 「配列数式」を使います。
セルF6には、
=MAX(IF(A2:A8=E6,B2:B8))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲A2:A8の値が、セルE6と等しい場合、対象のセル範囲B2:B8の値(配列として101,102,103,104が返ります)の最大値 (この場合104)を返します。
リストの列データを行インデックス別に表示し たい添付ファイルを参照してください。
A B C D E F 1 Aさん あ い う お 2 Bさん い え 3 Cさん あ う お 4 Dさん あ い う え お 5 6 7 8 9 10 あ Aさん Cさん Dさん 11 い Aさん Bさん Dさん 12 う Aさん Cさん Dさん 13 え Bさん Dさん 14 お Aさん Cさん Dさん 15 INDIRECT関数と、「配列数式」を、使います。
セル範囲A10:A14には、検索条件となる文字列"あ","い","う","え","お"をそれぞれ入力します。
セルB10には、
=IF(SUM(IF($B$1:$F$4=$A10,1))<COLUMN()-1,"",INDIRECT("A"&SMALL(IF($B$1:$F$4=$A10,ROW($A$1:$A$4)),COLUMN()-1),TRUE))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{}でくくられます。
自分で{}を入力してはいけません。
数式の意味
もし、セル範囲$B$1:$F$4の値が、検索条件となるセル$A10と等しい場合、1を返しその合計数が、数式入力セルの列番号(この場合2)より小さ い場合は、何も返しません。
それ以外は、
文字列"A"と、もし、セル範囲$B$1:$F$4の値が、検索条件となるセル$A10と等しい場合、該当のセル範囲$A$1:$A$4の行番号の最小値 を返し、数式入力セルの列番号-1(この場合2-1=1)番目に小さい値(この場合セルB1,B3,B4が合致し、1番目に小さい行番号は、B1セルの1 となります)を返します。
この場合、セルA1への参照式が返ります。
このセルをコピーして下のセル範囲B11:B14に貼り付け、右のセル範囲C10:E14まで貼り付けます。
それぞれの関数の説明は、「関数の使い方説明」
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
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
を、参照してください。
――――――――――――――――――――――――――――――
お試しください。
常に前のシートのセルI3を参照し たいエクセルには、前のシート名を返すワークシート関数がありません。
そこで、
「エクセルで使えるソフト」に、私の作成した、「前シート名」ユーザー定義関数 があります。
このユーザー定義関数を使えば、前のシート名が返りますので、INDIRECT関数と組み合わせて、セル参照式を作成します。説明はこちら
http://kiyopon.sakura.ne.jp/soft/sheetname.html
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/sheetname.exe
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。添付ファイルを参照してください。
ファイルには、「前シート名」ユーザー定義関数が貼り付けてあります。
ファイルを開く際に「マクロ」を「有効」にしてください。
シートSheet2のセルI3には、
=INDIRECT(前シート名()&"!I3")+G3+H3
と入力します。
数式の意味
前シート名と、文字列"!I3"を文字列結合し、"A1"形式のセル参照式とし(この場合Sheet1のI3へのセル参照となります。)、その値とセル3 と、セルH3を加算します。INDIRECT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を参照してください。
誕生日の月なら隣のセルに○を返す
誕生日は、年以外の月と今月が同じかどうかで判定します。
生年月日から月を取り出すには、
MONTH関数を使います。
本日の日付は、
TADAY関数を使います。
A1セルに誕生日が、日付入力形式で
H10/12/21
と、入力してあるとします。
B1セルには、
=IF(MONTH(A1)=MONTH(TODAY()),"○","")
と入力します。
数式の意味
もし、セルA1の月と、本日の月が同じ場合、"○"を返し、それ以外は何も返しません。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「日付+時刻」
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
TODAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#today
参照先に文字を入力しても計算結果がエラーを表示しないようにする「ツール」「オプション」の「移行」タグを開き、「シート オプション」 セクションの「計算方法を変更する」のチェックを入れ、「OK」ボタンを押します。
これで、文字列は「0」とみなして計算が行われるようになり、計算結果がエラーになりません。この「移行」では、[シート オプション] のオプションをオンまたはオフにして、ブックを開いたときの式の検証と数式の変換について、Lotus 1-2-3 の規則で行うか Microsoft Office Excel の規則で行うかを指定します。
計算方式を変更する 情報の損失や変更なしに Lotus 1-2-3 ファイルを開いて検証します。このオプションをオンにすると、Lotus 1-2-3 で使用される規則に基づいて、テキスト文字列、ブール式、およびデータベースの検索条件が評価され、テキスト文字列は 0 (ゼロ)、ブール式は 0 または 1 となります。
VLOOKUP関数で数 式の列番号も相対にしたい１つのセルに、VLOOKUPを作成し、フィルハンドルで右に式をコピーすると列番号も2列目→3列目と動くようにしたいの ですね入力セルの列番号を計算式に指定することで対応します。
入力セルがC列(行番号3)の場合
=VOOKUP(A1,$G$1:$K$13,2,0)
は、
=VOOKUP(A1,$G$1:$K$13,column()-1,0)
となります。
数式の意味
セルA1を検索値として、セル範囲$G$1:$K$13の左端列(この場合G列)を検索し該当する、数式入力セルの列番号-1(この場合3-1=2)列目 の値を返します。このセルをコピーして、右のセルD列(行番号4)に貼り付けると
=VOOKUP(B1,$G$1:$K$13,column()-1,0)
数式の意味
セルB1を検索値として、セル範囲$G$1:$K$13の左端列(この場合G列)を検索し該当する、数式入力セルの列番号-1(この場合4-1=3)列目 の値を返します。それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」のCOLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
VLOOKUP
http://kiyopon.sakura.ne.jp/kansuu/address.html#vlookup
を参照してください。
A1 の10を010にし、B1の5を005に変更し、C1にA1とB1をつなげた文字を010005と表示TEXT関数と文字列結合関数&を使います。
具体的には、
C1セルに
=TEXT(A1,"000")&text(B1,"000")
と入力します。
C1セルには、文字列で
010005
が返ります。
ただし、A1セルB1セルに何も入力してない場合は、文字列
000000
が返ります。
この、000000を表示しないようにするには、
C1セルに
=IF(A1<>0,TEXT(A1,"000"),"")&IF(B1<>0,TEXT(B1,"000"),"")
と入力します。TEXT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を参照してください。
日付の平成19年11月9日か ら、元号の年19だけを取り出すご質問の場合は、日付シリアル値ですから日付シリアル値から元号の年を返すには、TEXT関数を使います。セルD4に、日付シリアル値で(H19/11/9)入力してあり、表示が
平成19年11月9日
の場合
元号の年を返すには、
=TEXT(D4,"e")
と入力します。TEXT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
TEXT
http://kiyopon.sakura.ne.jp/kansuu/val.html#text
を参照してください。
フィルターのかかっているセルの偶 数行、奇数行を計算したい参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index
の「データベース」「2006/9/19」
「オー トフィルタの抽出結果のデータだけを自動的に合計したい」にあります。
--------------------------------------------
ご質問の場合
偶数行・奇数行の集計には「mod」関数で別の列に偶数行・奇数行の値を算出します。
フィルタのかかった状態では、SUBTOTAL関数を使います。
具体的には、セル範囲A2:A100にデータが入力してあり、偶数行の集計はB列に、奇数行の集計はC列にする場合
セルB2には、偶数行の値を返す式
=IF(MOD(ROW(),2)=0,A2,0)
と入力します。
数式の意味
数式入力セルの値を2で割った余りが、0の場合セルA2の値を返し、それ以外は、0を返します。セルC2には、奇数行の値を返す式
=IF(MOD(ROW(),2)=1,A2,0)
と入力します。
数式の意味
数式入力セルの値を2で割った余りが、1の場合セルA2の値を返し、それ以外は、0を返します。セルB1には、偶数行の合計値を返す数式
=SUBTOTAL(9,B2:B100)
と入力します。セルC1には、偶数行の合計値を返す数式
=SUBTOTAL(9,C2:C100)
と入力します。それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
SUBTOTAL
http://kiyopon.sakura.ne.jp/kansuu/abs.html#SUBTOTAL
MOD
http://kiyopon.sakura.ne.jp/kansuu/abs.html#mod
を参照してください。
2(24)のように入力した括弧内の文 字24を取り出す2(24)のように入力した括弧の前の数値を取り出す
セルA1には、文字列が、
2(24)
のように入力してあります。
セルに、
=VALUE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))
と入力します。
セルA1には、文字列が、
2(24)
のように入力してあります。
セルに
=VALUE(LEFT(A1,FIND("(",A1)-1))
と入力します。それぞれの関数の説明は、「関数の使い方説明」
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
を、参照してください。
タイムレースで、5人中上位3人の合計 タイムを出したい添付ファイルを参照してください。
「配列数式」を使います。
A B C D E F G 1 選手名 a b c d e 上位3人合計 2 時間 1:44:14 1:52:24 1:36:49 1:47:36 1:42:19 5:03:22
セルG2には、
=SUM(IF(B2:F2<=SMALL(B2:F2,3),B2:F2))
と入力し、{Shift}+{Ctrl}+{Enter}で、配列数式として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲B2:F2の値が、セル範囲B2:F2の三番目に小さい値以下の場合、そのセル範囲B2:F2の値を合計します。
SMALL関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
SMALL
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#SMALL
を、参照してください。
データの１項目おきにデータリストに色を付ける
「条件付き書式」を使います。
下の行と上の行の値が違ったらデータ毎に行に色を付けるのですね。
添付ファイルを参照してください。
まず、関係ない列を1列挿入します。(この場合B列)
セルB2には、データの種類を数値で返す数式
=IF(A1=A2,B1,B1+1)
と入力します。数式の意味
=IF(A1=A2,B1,B1+1)
もし、セルA1の値とA2セルの値が同じならば、セルB1の値を返し、それ以外はセルB1の値に+1した値を返します。セル範囲A1:K10を選択し、セルA1がアクティブの状態で、「条件付き書式」を選択し、「条件付き書式の設定」ダイアログボックスで、 「条件1」で、「数式が」を選択し、
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
「=MOD($B1,2)=1」と入力し「書式」ボタンを押して「パターン」の「色」を指定して「ＯＫ」ボタンを押 し、「条件付き書式の設定」ダイアログボックスで、「ＯＫ」ボタンを押します。=MOD($B1,2)=1
数式の意味
セル$B1の値を2で割った余りが1の場合(奇数のデータ種類)はセルに色を付けることになります。
セルA1に17、セルB1に45(17:45の意味),セルC1に19、セルD1に00で(19:00の意味)セルE1 に1.25(1:15の意味)と表示したいE1セルには、
=(TIMEVALUE(C1&":"&D1)-TIMEVALUE(A1&":"&B1))*24
と入力します。
数式の意味
セルC1の値と文字列":"とセルD1の値を文字列結合し、時刻として変換し、その値からセルA1の値と文字列":"とセルB1の値を文字列結合し、時刻 として変換し、減算します。
さらにその値を24倍して、10進数の整数値にします。時刻を10進数で表すには、1:15→1.25
参考になる質問が「質問と回答」http://kiyopon.sakura.ne.jp/situmon/index.htm
の「計算式・関数」
「2002/11/30」
「時刻を10進数で表す8:30→8.5」にあります。TIMEVALUE関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
TIMEVALUE
http://kiyopon.sakura.ne.jp/kansuu/date.html#timevalue
を、参照してください。
1.3と入力したセルに1.5と表示するのですか。
入力セルと同じセルに違う数値を返すのは無理です。
添付ファイルを参照してください。入力セルとは別のセルに数値で、1.5と表示することはできます。
A1セルに、1:30のつもりで
1.3
と入力したとします。
B1セルに、1.5整数値を返す数式
=INT(A1)+(A1-INT(A1))*100/60
と入力します。
数式の意味
セルA1の値を整数にした値(この場合1)+セルA1の値-ルA1の値を整数にした値(この場合1.3-1=0.3)×100÷60
1+(0.3×100/60)=(1+30÷60)=1+0.5=1,51.5(1.5時間の意味で、1時間30分)
が返ります。1.45(1.45時間の意味)
と入力すれば、
1.75(1.75時間の意味で、1時間45分)が返ります。この二つの数値(1.5+1.75)の合計を合計すると
3.25(3.25時間の意味で、3時間15分)となります。
入力日付のその月の1日を返す日付を入力したセルで、そのような表示にすることはできませんので、別のセルに表示することになります。
EOMONTH関数を使います。
EOMONTH は、開始日から起算して、指定した月数だけ前または後の月の最終日に対応するシリアル値を返します
A1セルに日付が
2007/3/15
と、入力してあるとします。
B1セルには、その月の1日を返す数式
=EOMONTH(A1,-1)+1
と入力します。
数式の意味
セルA1の日付(この場合2007/3/15)の、１ヵ月前の最後の日付(この場合2007/2/28)+1
日の日付シリアル値を返します。
入力日付の次に来る4/1を返すこの場合は、DATE関数と、MONTH関数とYEAR関数と、IF関数と組み合わせて使います。
A1セルに日付が
2007/3/15
と、入力してあるとします。
B1セルには、その次にくる4月1日を返す数式
=DATE(YEAR(A1)+IF(MONTH(A1)<4,0,1),4,1)
と、入力します。
数式の意味
セルA1の年に、もし、セルA1の月が4より小さい場合は、0それ以外は1を加算し、4,1で日付シリアル値とします。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「日付+時刻」
EOMONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#eomonthDATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateMONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#monthYEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
を、参照してください。
=VLOOKUP（$B3,品名料金表！$B$78：D＄147,2,0)を右列にコピーし参照列の2を3,4,5,6 と変えたいセルの列番号を返す関数COLUMN()を使えばできます。数式を入力したセルの列番号が2(B列)なら次のようになります。
=VLOOKUP($B3,品名料金表!$B$78:D$147,COLUMN(),0)
数式の意味
セル$B3の値を検索値として、シート「品名料金表」のセル範囲$B$78:D$147の左端列(この場合B列)を検索し、数式入力セル の列番号(この場合2)番目列(この場合C列)の値を返します。このセルをコピーして右のセル範囲C:Zに貼り付けたとします。
Z列では、数式が次のようになります。
=VLOOKUP($B3,品名料金表!$B$78:AB$147,COLUMN(),0)
数式の意味
セル$B3の値を検索値として、シート「品名料金表」のセル範囲$B$78:AB$147の左端列(この場合B列)を検索し、数式入力セ ルの列番号(この場合26)番目列(この場合AA列)の値を返します。数式入力セルがD列(列番号4)の場合、スタートの値を2とするため(数式入力セルの列番号4から2を引けば2となります)
=VLOOKUP($B3,品名料金表!$B$78:D$147,COLUMN()-2,0)
と入力します。COLUMN関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
COLUMN
http://kiyopon.sakura.ne.jp/kansuu/address.html#column
を、参照してください。
B1セルに入力した日付の１ヵ月後の日付を返すセルB1には、
1998/1/5
と入力してあるとします。
日付シリアル値として認識されています。
セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「日付」を選択し、「種類」欄に、 「yyyy/mm/dd」として、「ＯＫ」ボタンを押します。
セルの表示は、
1998/01/15
となります。セルB1の日付の1ヵ月後の日付を返すには、
セルには、
=EDATE(B1,1)
と入力します。1998/02/15
が返ります。EDATE関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
EDATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#edate
を、参照してください。
文字列として入力された数字データを数値に 変換する関係のないセルに1と数値を入力し、このセルを選択し、「編集」「コピー」します。
次に、「編集」「ジャンプ」で、「セル選択」ボタンを押し、「定数」のラジオボタンを押し、「ＯＫ」ボタンを押します。
これで、文字列をすべて選択できました。
「編集」「形式を選択して貼り付け」を選択し、「演算」グループの「乗算」にラジオボタンを押して、「ＯＫ」ボタンを押します。
これで、文字列として入力された値が、数値になります。
数値の7.5を時刻の7:30に 43.5を43:30にする10進数で表示(7.5)した時刻(7:30)を時刻シリアル値にするには、24で割れば良いです。時刻表示にするため、セルを選択し、「書式」「セル」を選択し、「セルの書式
設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「[h]:mm」と入
力し、「ＯＫ」ボタンを押します。
24:00以上の時刻表示にするため、セルを選択し、「書式」「セル」を選択し、「セルの書式
設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「[h]:mm」と入
力し、「ＯＫ」ボタンを押します。
セルの値は、時刻シリアル値となり、1日が1で24時間、1時間は、1/24となりますので、整数値として計算する場合は、24倍する必要があります。
セ ルに1と入力したら30.1と表示し、(ただし30未満の29.9なら29.9)さらに合計計算をしたい添付ファイルを参照してください。
B C 1 測定値 2 1 29.9 3 2 30.1 4 3 30.2 5 4 30.3 6 5 30.4 7 6 30.5 8 7 30.2 9 8 30.3 10 9 30.4 11 10 30.5 12 合計 302.8
まず、敷居値30としていますが、セル数値が10以上の場合、そのまま表示し、それ以外は数値の前に"30."を数値の前に 結合するように条件付きセルの書式設定とします。
具体的には、セル範囲C2:C11を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー 定義」を選択し「種類」欄に「[>=10]#.0;"30."0」と入力し、「ＯＫ」ボタンを押します。
セルC12の合計計算には「配列数式」を使います。
=SUM(IF(C2:C11<10,C2:C11/10+30))+SUM(IF(C2:C11>=10,C2:C11))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
セル範囲C2:C11の値が、10未満の場合は、対象のセル範囲C2:C11の値に+30した値を合計します。
セル範囲C2:C11の値が、10以上の場合は、対象のセル範囲C2:C11の値を合計します。
０-７の数字が 入っており０以外の数字（１-７）が何個入っているか数えたいCOUNTIF関数を使います。
セル範囲A1:A100に、
0から7までの数値が入力されているとします。
セルB1には、
=COUNTIF(A1:A100,">0")
と入力します。
数式の意味
セル範囲A1:A100の値が、0より大きい場合そのセルの個数を返します。COUNTIF関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を、参照してください。
時間毎のデータの個数を集計する添付ファイルを参照してください。
二つの検索条件に合致するセルの数を返すのですね。「配列数式」を使います。
まず、検索条件としてのセル範囲F3:F15に入力してある
13:00-14:00
14:30-15:00
…
1:30-2:00
のセルを数式として認識できるように二つのセルにわけます。
セル範囲F3:F15を選択し、「データ」「区切り位置」を選択し「物とデータの形式」で、「カンマやタブなどの区切り文字によってフィールド毎に区切ら れたデータ(D)」のラジオボタンを選択し、「次へ」ボタンを押し、「その他」のチェックを入れ、「-」と入力し「完了」ボタンを押します。
これで、セル範囲F2:G15の二つの列にデータ分割できました。セルH3には、
=SUM(IF($D$3:$D$27>=F3,IF($D$3:$D$27<=G3,1)))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
セル範囲$D$3:$D$27の値が、セルF3以上で、かつ、セル範囲$D$3:$D$27の値が、セルG3以下の場合、1を返し、その合計を返します。
このセルをコピーして下のセル範囲に貼り付けます。
2,450,956という金額 を金245万956円と表示したい同じセルにそのままセルの書式設定で、2,450,956という金額を金245万956円と表示するのは無理です。別のセルに表示することになります。
A1セルに
2,450,956
と入力してあるとします。B1セルに
金245万956円と表示するには
="金"&IF(INT(A1/10000)>0,INT(A1/10000)&"万","")&A1-INT (A1/10000)*10000&"円"
と入力します。
文字列として
金245万956円
と表示されます。A1セルを印刷しないように、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」 で「ユーザー定義」を選択し「種類」欄に「;;;」と入力し、「ＯＫ」ボタンを押します。
小数点以下を切り捨てた集計を一発でやりたい添付ファイルを参照してください。配列数式を使います。
A 1 19.58 2 3458.39 3 783.64 4 462.37 5 4722 セル範囲A1:A4の値を小数点以下を含まないで加算する場合
セルA5には、
=SUM(ROUNDDOWN(A1:A4,0))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
セル範囲A1:A4の値を小数点以下0桁で切り捨てた値を合計します。19+3458+783+462
=4772
が返ります。それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
SUM
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
「丸め」
ROUNDDOWN
http://kiyopon.sakura.ne.jp/kansuu/round.htm#rounddown
を、参照してください。
セル範囲の8.0を超過する値の合計を返す具体的には、8.5と11.5の場合(8.5-8.0=0.5)+(11.5-8.0=3.5)=4.0となる計算式です。添付ファイルを参照してください。
C D E 1 時間 時間 2 6.0 3 11.5 6.0 4 9.5 5 8.5 6 7 3.0 4.5 8 6.0 9 6.0 3.0 10 6.0 8.5 11 6.0 12 15.0 6.0 13 4.0 14 9.0 15 16 6.0 17 18 14 「配列数式」を使います。
セル範囲C2:E17に数値が入力してあるとします。
セルに
=SUM(IF(C2:E17>8,C2:E17-8))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
セル範囲C2:E17の値が、8超過の場合、対象のセル範囲C2:E17の値から8を引いた値を、合計します。
行挿入時にSUM関 数の合計数式は自動拡張で挿入行を追加しない場合があるSUM関数を使った行のセル範囲の合計は、行を挿入すれば自動的に数式が挿入行に合わせて拡張されます。
A B C D E F G H 1 2 あ い う え お 合計 3 Ａ 1 1 1 1 1 5 4 Ｂ 2 2 2 2 2 10 5 Ｃ 3 3 3 3 3 15 6 Ｄ 4 4 4 4 4 20 7 Ｅ 5 5 5 5 5 25 8 Ｆ 6 6 6 6 6 30 9 Ｇ 7 7 7 7 7 35 10 Ｈ 8 8 8 8 8 40 11 合計 36 36 36 36 36 180
セルC11には、C列の合計値を返す数式
=SUM(C3:C10)
と入力してあります。
11行目を挿入します。 →
セルB11を選択し「挿入」「行全体」
して、合計の数式が12行目に移動
しました。行の挿入で上のセルに数式を入力すれば
A B C D E F G H 1 2 あ い う え お 合計 3 Ａ 1 1 1 1 1 5 4 Ｂ 2 2 2 2 2 10 5 Ｃ 3 3 3 3 3 15 6 Ｄ 4 4 4 4 4 20 7 Ｅ 5 5 5 5 5 25 8 Ｆ 6 6 6 6 6 30 9 Ｇ 7 7 7 7 7 35 10 Ｈ 8 8 8 8 8 40 11 Ｉ 9 12 合計 45 36 36 36 36 189
行の合計数式は自動変更される例えばセルC11に数値を入力すれば、
セルC12の数式は、挿入した行を含んで
=SUM(C3:C11)
に自動的に変更されます。しかし、SUM関数の始めの行(この場合3行目)のセルを選択して、行を挿入した場合は、合計数式は挿入した行を含まない数式に自動変更されます。
A B C D E F G H 1 2 あ い う え お 合計 3 Ａ 1 1 1 1 1 5 4 Ｂ 2 2 2 2 2 10 5 Ｃ 3 3 3 3 3 15 6 Ｄ 4 4 4 4 4 20 7 Ｅ 5 5 5 5 5 25 8 Ｆ 6 6 6 6 6 30 9 Ｇ 7 7 7 7 7 35 10 Ｈ 8 8 8 8 8 40 11 合計 36 36 36 36 36 180
セルC11には、C列の合計値を返す数式
=SUM(C3:C10)
と入力してあります。
3行目を挿入します。 →
セルB3を選択し「挿入」「行全体」
して、合計の数式が12行目に移動
しました。行の挿入で上のセルに数式を入力しても
A B C D E F G H 1 2 あ い う え お 合計 3 4 Ａ 1 1 1 1 1 5 5 Ｂ 2 2 2 2 2 10 6 Ｃ 3 3 3 3 3 15 7 Ｄ 4 4 4 4 4 20 8 Ｅ 5 5 5 5 5 25 9 Ｆ 6 6 6 6 6 30 10 Ｇ 7 7 7 7 7 35 11 Ｈ 8 8 8 8 8 40 12 合計 36 36 36 36 36 180
行の合計数式は挿入した行を含まない
数式に自動変更されますセルC12の数式は、
挿入した行を含まない数式に
=SUM(C4:C11)
に自動的に変更されます。必ず挿入行を反映する合計式としたい場合。
添付ファイルを参照してください。
この場合は、3行目から数式入力セルの1つ上の行までの合計としています。数式を次のように変更します。
INDIRECT関数とROW関数、COLUMN関数を使って、３行目から数式入力セルの一つ上の行までの合計を返します。
=SUM(INDIRECT("R3C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
数式の意味
文字列"R3C"と、数式入力セルの列番号(この場合3)と文字列":R"と、数式入力セルの行番号-1(この場合12-1=11)と、文字列"C"と、 数式入力セルの列番号(この場合3)から、"R1C1"形式のセル参照式とします。
この場合、
=R3C3:R11C3
となります。
このセルの行が、行挿入や行削除などで行が移動しても必ず、3行目からこのセルの一つ前の行までのセルを参照することができます。それぞれの関数の説明は、「関数の使い方説明」
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
を、参照してください。
B列の値が初めて 0.5以上になったときのA列の値とB列の値を出力したいINDIRECT関数と「配列数式」を使います。
添付ファイルを参照してください。
セルD3には、
=INDIRECT("A"&MIN(IF($B$2:$B$121>=0.5,ROW($B$2:$B$121))))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
文字列"A"と、もし、セル範囲$B$2:$B$121の値が、0.5以上の場合、セル範囲$B$2:$B$121の行番号を返し、その最小値(この場合 33)とを文字列結合し、"A1"形式のセル参照式(この場合"A33")とします。同様に、セルD5には、
=INDIRECT("B"&MIN(IF($B$2:$B$121>=0.5,ROW($B$2:$B$121))))
と入力し、{Shift}キーと{Ctrl}キーを押しながら{Enter}を押して「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
文字列"B"と、もし、セル範囲$B$2:$B$121の値が、0.5以上の場合、セル範囲$B$2:$B$121の行番号を返し、その最小値(この場合 33)とを文字列結合し、"A1"形式のセル参照式(この場合"B33")とします。それぞれの関数の説明は、「関数の使い方説明」
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
「統計関数」
MIN
http://kiyopon.sakura.ne.jp/kansuu/avedev.html#MIN
を、参照してください。
シリアル値ってどういうことなんでしょうかシリアル値には、「日付シリアル値」と、「時刻シリアル値」があります。
日付シリアル値は、1990年1月1日を1とした、1日が1の数値です。1990年1月2日は、2です。
今日が2007年1月5日であれば、日付シリアル値は、39087です。
「時刻シリアル値」は、24時間を1とした小数値で、1日が1で、24時間ですから、1時間は1/24です。
1分は、1/24/60です。1秒は、1/24/60/60です。
セルに日付シリアル値として認識できる、日付入力形式yyyy/m/d
例えば、2007/1/5あるいは、H19/1/5 1/5
のように入力すれば、日付シリアル値として、日付が数値として入力されます。
また、時刻も同様に
10:26
のように入力すれば、時刻シリアル値として、小数で入力されます。時刻シリアル値、日付シリアル値は、日付や時刻を計算する場合に便利に使えます。
例えば、2006/1/5の120日後の日付を求めるには、
A1セルに入力した
2006/1/5
に、
=A1+120
とすれば良いのです。
時刻も同様に、A1セルに入力した出勤時刻
8:30
と、B1セルに入力した退社時刻
17:00
を引き算したい場合
=B1-A1
で、勤務時間が計算できます。
セルに第２、第４木曜日を1年分自動表示する添付ファイルを参照してください。
セルA1には年度を決めるため 2007などと西暦年4桁で入力します。
セルA3には、4月の第二週木曜日を返す数式
=DATE(A1,4,1)+4+7-WEEKDAY(DATE(A1,4,1),2)+IF(WEEKDAY(DATE(A1,4,1),2)>4,7,0)
と入力します。
数式の意味
セルA1の年の4月1日の日付(この場合2007/4/1)+4(これは木曜日の意味)+7で、(2007/4/12)から、2007/4/1の月曜日を 1とした曜日の数(この場合日曜日ですから7)から、2007/4/1の月曜日を1とした曜日(この場合日曜日ですから7)が、4(これは木曜日の意味) より大きい場合、7を、それ以外は0を加算した値を減算します。(この場合2007/4/12から7-7=0を減算し、2007/4/12が返ります)セルA4には、その2週間後の日付を返す(第四週木曜日)
=A3+14
と入力します。
数式の意味
セルA3の14日後の日付を返します。セルA5には、次の月の第二週の日付を返す数式
=A3+(4*7)+IF(DAY(A3+(4*7))<=7,7,0)
と入力します。
数式の意味
セルA3+(4*7)の日付(セルA3の日付の４週間後の日付を返します)+もし、その日付が7以下の場合は、7を、それ以外は0を加算します。セル範囲
A4:A5を選択し、「編集」「コピー」して、下のセル範囲A25まで貼り付けます。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
WEEKDAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#weekday
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
を、参照してください
セル に入力した文字列19700805を日付シリアル値として1970/08/05とする二つの方法があります。
数式で、日付表示に変換する方法
A1セルに
19700805
入力してあるとします。
セルB1に、
=TEXT(A1,"####""/""##""/""##")
と入力します。
セルB1には、
"1970/08/05"
と表示されます。（ただし、文字列のため、日付としては人してできません）
セルB1を選択し、「編集」「コピー」して、セルA1を選択し、「編集」「形式を
選択して貼り付け」で、「値」にチェツクを入れ、「ＯＫ」ボタンを押します。
これで、A1セルに日付シリアル値としての
1970/08/05
が返ります。別の方法で、
19700805
を日付シリアル値として認識できる値に変換するには、
「エクセルで使えるソフト」に「日付に変換」アドインソフトがあります。
セルに入力されたyyyymmdd形式（西暦年4桁月2桁日2桁）の日付数値（例えば20060721）を日付シリアル値として認識できる日付文字列(例 えば2006/7/21)に変換します。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/hizuke.htm
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/hizuke.exe注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
セルの文字列の先頭文字を大文字にする関数では、Proper関数を使います。
この場合入力セルとは別のセルに=Proper(A1)などと入力し、(A1セルの)先頭文字を大文字に返すことになります。
具体的には、
A1セルに
excel
と入力してあるとします。
B1セルに、先頭文字を大文字にした文字列を返す関数
=PROPER(A1)
と入力します。
Excel
が返ります。Proper関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
Proper
http://kiyopon.sakura.ne.jp/kansuu/val.html#proper
を、参照してください。
長文の文字列をセル幅で下のセルに分割する方法セル幅に合わせて折り返し、一つのセルに表示するのではなく、セル幅で文字を次の行に分割する方法は、
エクセルには、セル幅を取得する数式は、CELL関数を使います。
=CELL("width",セル)
となります。
セル幅は、小数点以下を切り捨てた整数のセル幅。セル幅の単位は、標準のフォント サイズの 1 文字の幅と等しくなります。添付ファイルを参照してください。
A列のセル幅は、「20」にしてあります。
A1セルに文字列が入力してあるとします。
A2セル以降の行にA1セルの文字列をセル幅で取り出すには、
=MIDB($A$1,1+CELL("width",A1)*(ROW()-2),1+CELL("width",A1))
と入力します。
数式の意味
セル$A$1の文字を、1+A1セルのセル幅数(この場合20)×数式入力セルの行番号-2(この場合20×(2-2)=0)文字目(この場合1+0= 1)、から、半角で、1+セルA1のセル幅数(この場合1+20=21)の文字列を取り出します。このセルをコピーして、下のセル範囲に貼り付けます。
A3セルでは、数式入力セルの行番号が3になりますので、1+セル幅(20)×(3-2=1)文字目(1+20=21)から、半角で、1+20文字の文字 列を取り出します。列幅を変更しても、自動的に文字列がセル幅に合わせて表示されませんので、数式の再計算のため[F9]キーを押してください。
それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
MIDB
http://kiyopon.sakura.ne.jp/kansuu/val.html#midb
「情報関数」
CELL
http://kiyopon.sakura.ne.jp/kansuu/cell.html#cell
「検索+行列」
ROW
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
を、参照してください。
フィルターをかけたときに表示 しているデータのみの連番を付けるオートフィルターの場合は、SUBTOTAL関数を使います。
セルC1には、項目名が入力してあり、データはセル範囲C2をスタートセルとして入力してあるとします。
セルD2には、フィルターをかけた結果の連番を返す数式
=SUBTOTAL(3,$C$2:C2)
と入力します。
数式の意味
セル範囲$C$2:C2のセルで、データベースの集計結果の非表示セルを無視して、空白でないセル数を返します。
このセルをコピーして下のセル範囲に貼り付けます。
SUBTOTAL関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」
SUBTOTAL
http://kiyopon.sakura.ne.jp/kansuu/abs.html#SUBTOTAL
セルの文字が常に 全角11文字になるように全角スペースを文字の後ろに入れる11文字は全角ですペースを補って、入力セルとは別のセルに表示します。
REPT関数を使います。
具体的には、セルA1に、文字列
"田中 陽子"
と入力してあるとします。
セルB12には、A1セルの文字列が11文字となるように全角スペースを追加し、最後
に"様"と付けます。
=REPT(" ",11-LEN(A1))&"様"
と入力します。
数式の意味
セルA1の値(この場合"田中 陽子")と、全角スペース" "を、11-セルA1の文字列
数(この場合5)の値(この場合11-5=6)回数繰り返した文字列を作成して、文字列"
様"を文字列結合します。
" 様"が返ります。
A1セルの文字列を結合すれば、
"田中 陽子 様"
となります。REPT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」REPT
http://kiyopon.sakura.ne.jp/kansuu/val.html#rept
を参照してください。
17 2 1の文字列を日付データにする方法
A 1 17 2 1 2 17 314 3 1710 7 4 171228 5 18 112 数式を使う方法
B1セルに
=SUBSTITUTE("H"&LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)," ",0)
と入力します。
これで、文字列としての
H17/02/01
が返ります。
このセルをコピーして、下のセル範囲B2:B5に貼り付けます。
セル範囲B1:B5を選択し、「編集」「コピー」します。
同じセル位置で、「編集」「形式を選択して貼り付け」を選択し、「貼り付け」グループの「値」のラジオボタンを押し、「ＯＫ」ボタンを押します。
セル範囲を選択し、「書式」「セル」を選択し、セルの書式設定で「表示形式」の「分類」で「日付」で「*2001/3/14」などを選択し、「ＯＫ」ボタ ンを押します。
関係ないセルに、数値の1を入力し「編集」「コピー」します。
日付文字列の表示されているセル範囲を選択し、「編集」「形式を選択して貼り付け」で「演算」グループの「乗算」ラジオボタンを押し、「ＯＫ」ボタンを押 します。
セル範囲を選択し、「書式」「セル」を選択し、セルの書式設定で「表示形式」の「分類」で「日付」で「*2001/3/14」などを選択し、「ＯＫ」ボタ ンを押します。セルの数値の0の部分が半角スペースの文字列ですから、数値の0にします。
セル範囲A1:A5を選択し、「編集」「置換」を選択し、「検索文字列」に半角スペースを入力し、「置換後の文字列」に0を入力し、「すべて置換」ボタン を押します。
これで、数値として認識できる値
A 1 170201 2 170314 3 171007 4 171228 5 180112
となります。
170201の文字列を日付データにする方法
A 1 170201 2 170314 3 171007 4 171228 5 180112 和暦ですから、対象セルに19880000を加算し、西暦にします。
関係のないセルに
19880000
と入力し、「編集」「コピー」します。
次に、セル範囲A1:A5を選択し、「形式を選択して貼り付け」で「演算」グループの「加算」ラジオボタンを押して「ＯＫ」ボタンを押します。
これで、
A 1 20050201 2 20050314 3 20051007 4 20051228 5 20060112 となります。
「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に、私の作成した「日付に変換」アドインソフト
http://kiyopon.sakura.ne.jp/soft/hizuke.htm
があります。
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
セ ル範囲の数の合計を算出する際に、80以上の数値の場合は80として合計したい
「配列数式」を使います。セル範囲A1:A4までの合計で、80以上の数値は、80として計算します。
A 1 70 2 80 3 81 4 85 5 310 セルA5には、
=SUM(IF(A1:A4>=80,80,A1:A4))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。
数式の意味
もし、セル範囲A1:A4の値が、80以上の場合は、80を返し、それ以外はA1:A4の値を返し、合計します。
数値データにおいて、下二桁のみ表示するには入力セルに、下2桁のみを表示することはできません。
別のセルに数式で、下2桁のみを返す数式を紹介します。
セルA1に
105
と入力してあるとします。
セルB1に、
=RIGHT(A1,2)
と入力します。
数式の意味
セルA1の文字列の右から2文字を返します。
05
が、文字列として返ります。
関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「文字列関数」
http://kiyopon.sakura.ne.jp/kansuu/val.html#right
RIGHT
を参照してください。
Sheet1 の県名と名前のリストを検索し、Sheet2に入力した県名から名前を表示させる添付ファイルを参照してください。
MATCH関数と、INDEX関数を使います。シート[Sheet1]
A B 1 山田 北海道 2 田中 秋田 3 佐藤 青森 シート[Sheet2]
A B 1 佐藤 青森 セルB1には、
青森
と入力します。セルA1には、セルB1に入力した"青森"を検索値として、シート[Sheet1]から"佐藤"を返す数式
=INDEX(Sheet1!$A$1:$A$3,MATCH(B1,Sheet1!$B$1:$B$3))
と入力します。
数式の意味
Sheet1のセル範囲$A$1:$A$3から、セルB1を検索値として、Sheet1のセル範囲$B$1:$B$3
を検索し、配列の何番目にあるかを返し、その行番号目のデータを返します。それぞれの関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
MATCH
http://kiyopon.sakura.ne.jp/kansuu/address.html#match
INDEX
http://kiyopon.sakura.ne.jp/kansuu/address.html#index
を参照してください。
100分の1秒の計算式時刻は、文字列ではなく、時刻入力とします。
A B 1 1:05:28.60 2 5:36.39 3 50.14 4 合計時間 1:11:55.13 B3セルの
50.14
は、
分の単位から
0:50.14
と入力します。セルB4には、
=SUM(B1:B3)
と入力します。
時刻を時分秒と百分の1秒まで表示するため、セル範囲B1:B4を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形 式」の「分類」で「ユーザー定義」を選択し「種類」欄に「[>0.0416666666666666]h:mm:ss.00;[> 0.000694444444444444]m:ss.00;s.00」と入力「ＯＫ」ボタンを押します。書式の意味
1時間は
1/24=0.0416666666666666ですから、
[>0.0416666666666666]h:mm:ss.00
で、1時間以上の場合の表示形式1分は
1/24/60=0.000694444444444444ですから、
[>0.000694444444444444]m:ss.00
で、1分以上の場合の表示形式s.00
は、それ以外の場合の表示形式
を示します。この書式設定により、
時:分:秒.00
分:秒.00
秒.00
と３種類の表示形式となります。
2007/2/4に入 社した人の社員証有効期限を2年後の6月で表したい参考になる質問が「計算式・関数」「2002/10/2」
「誕生日の３年１ヶ月後を表示する」にあります。御質問の場合は、次のようになります。
2007/2/4に入社した人の社員証有効期限を 2009/06で表したい
A1セルに
2007/2/4
と入力してあるとします。
B1セルに、2年後の6月(この場合2009年6月)と表示するには
=DATE(year(A1)+2,6,1)
と入力します。
数式の意味
日付シリアル値を、A1セルの年+2,6,1で返します。
(この場合2007+2=2009年6月1日)
2009/06
と表示するため、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分類」で「ユーザー定義」を選択し 「種類」欄に、「yyyy/mm」と入力し、「ＯＫ」ボタンを押します。
時間の計算で１００分の１秒までの計算をさせ たい百分の一の時間は小数点を付けて入力を次のようにします。
また、百分の一秒までの表示を設定するために、セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」の「分 類」で「ユーザー定義」を選択し「種類」欄に「h:mm:ss.00」と入力し、「ＯＫ」ボタンを押します。
A 1 12:45:20.19 2 1:20:30.40 3 4 14:05:50.59 A4セルには、
A1セルとA2セルの合計時間を返す数式
=A1+A2
と入力しています。
あ る数値をＡ、ＢとしてＡ+Ｂ＝ＣとなりＡ×Ｄ＝Ｅ,Ｅ/Ｃ＝ＦとしてＦ＞１となるようにＤを関数で導きたい
添付ファイルを参照してください。
「コールシーク」を使います。
セルA1には、
A B C D E F 1 100 150 250 2.5 250 1
100
セルB1には、
150
セルC1には、数式
=A1+B1
セルD1には、何も入力しません
セルE1には、数式
=A1*D1
セルF1には、数式
=E1/C1
と入力します。セルF1を選択し、
「ツール」「ゴールシーク」を選択し、「ゴールシーク」ダイアログボックスで「数式入力セル」にF1が入力されていることを確認し、「目標値」に「1」、 「変化させるセル」に「D1」を選択入力し「ＯＫ」ボタンを押します。
これで、D1セルに自動的に敷居値の2.5が計算入力されます。
D1セルの値が、2.5以上ならば、条件を満たすことになります。