Ａ列に日付が入力してあり、Ｂ列の数値の最大値、最小値のＡ列の日付をそれぞれ表示させる
二つの方法があります。
1つ目は、「配列数式」を使う
A B C D 1 9月23日 5 9 9月24日 2 9月24日 9 1 9月27日 3 9月25日 7 4 9月26日 3 5 9月27日 1 6
Ａ列に日付が入力してあり、Ｂ列の数値の最大値、最小値のＡ列の日付をそれぞれ表示させる二つの方法があります。
1つ目は、「配列数式」を使う
A B C D 1 9月23日 5 9 9月24日 2 9月24日 9 1 9月27日 3 9月25日 7 4 9月26日 3 5 9月27日 1 6
セルC1には、最大値を返す
=MAX($B$1:$B$5)
セルC2には、最小値を返す
=MIN($B$1:$B$5)
日付を返すセルD1:D2には、「書式」「セル」を選択し、「表示形式」で「日付」を選択し、「種類」欄に適当な日付表示を選択し、「ＯＫ」ボタンを押します。
セルD1には、最大値の日付を返す
=SUM(IF(MAX($B$1:$B$5)=$B$1:$B$5,$A$1:$A$5))
と入力し{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定後数式は、{}でくくられます。
{=SUM(IF(MAX($B$1:$B$5)=$B$1:$B$5,$A$1:$A$5))}
自分で{}を入力してはいけません。セルD2には、最小値の日付を返す
=SUM(IF(MIN($B$1:$B$5)=$B$1:$B$5,$A$1:$A$5))
と入力し{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定後数式は、{}でくくられます。
{=SUM(IF(MIN($B$1:$B$5)=$B$1:$B$5,$A$1:$A$5))}
自分で{}を入力してはいけません。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
2つ目は、VLOOKUP関数を使う
VLOOKUP関数を使いますが、A列の前に１列挿入します。
A B C D E F 1 5 9月23日 5 最大 9 9月24日 2 9 9月24日 9 最小 1 9月27日 3 7 9月25日 7 4 3 9月26日 3 5 1 9月27日 1 6
セルA1には、C列の数値への参照式
=C1と入力します。
この式を下のセルにコピーします。
セルE1に最大値
=MAX(C1:C5)
セルF1にの日付
=VLOOKUP(MAX($C$1:$C$5),$A$1:$B$5,2,FALSE)
セルE2に最小値
=MIN(C1:C5)
セルF2にの日付
=VLOOKUP(MIN($C$1:$C$5),$A$1:$B$5,2,FALSE)
1万人分ほどの名前・住所・電話番号等のデータの中から、100人を無作為に抽出する乱数を発生するために使用した、RANDBETWEEN関数を使うには、「ツール」「アドイン」で「分析ツール」にチェツクを入れて、「分析ツール」アドインを組み込んでおく必要があります。
「分析ツール」がなと場合は、「エクセル」の「インストール」で「分析ツール」アドインをインストールします。添付ファイルを参照してください。
セルA2からセルA111には、指定数値の間の乱数を発生させるため、次の関数が入力されています。
=RANDBETWEEN(1,10000)
では、1から10000までの乱数ですから、ご質問のデータ量が30400の場合は、その数に対する乱数を発生させないといけません。
=RANDBETWEEN(1,30400)
とするか、データ量が一定しないとするとそれを数えるために、セルA1に
=COUNTA(A112:A65536)
と入力します。
この場合は、セル範囲A2:A111には、
=RANDBETWEEN(1,$A$1)
と入力します。B2セルには、乱数の件数累計を返します。
=IF(C1=C2,B1,B1+1)
この式をコピーして、下のセルB111まで貼り付けます。データ抽出を簡単にするために、C1セルに抽出件数の100を入力します。
セルC2には、乱数で返した数値を昇順で表示します。
=SMALL($A$2:$A$111,ROW()-1)
この式をコピーして、下のセル範囲C111まで貼り付けます。セルD1には、データリストの項目名A113と同じ"No"という文字を入力します。
セルD2には、セルC2で指定した件数(この場合100件)までのデータの乱数を返します。
=IF(B2>$C$1,"",C2)
と入力します。
この式をコピーして、下のセルD111まで貼り付けます。データリストは、リスト範囲をほかのデータと明確に区分するため、データリストの開始行の上に1行以上の何も入力しない行を追加します。例では、112行を追加しています。
データリストの左端列には、データ区分のための通し番号（インデックス）をつけます。
データリストの先頭行は項目名としての行とします。セルA113には、"No"と入力します。
データリストの最上行は、項目名が入力されていなければなりません。セルA114以降には、データ番号を付けます。
データリストのA列を先頭行(ご質問の場合はA114)を選択し、同時に{Shift}+{Ctrl}+{↓}を押してデータリストの右端列を選択し、「挿入」「列」で、列を挿入します
。先頭セル(A114)を選択して1を入力します。セルの右下境界にマウスを移動して+のアイコンになったら、{Ctrl}を押しながらマウスを下にドラッグして、データリストの下端データまで連番をつけます。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
無作為データ100件の抽出
データ範囲(セルA113:M30400)のいずれかのセルを選択し、「データ」「フィルタ」「フィルタオプションの設定」を選択し、「抽出先」に、「選択範囲内」を選択し、「リスト範囲」に$A$113:$M$30400が選択されていることを確認し、「検索条件範囲」に$D$1:$D$111を選択入力します。リスト範囲と、検索条件範囲の最上行は、いずれも項目名が入力されているセルでなければなりません。
「ＯＫ」ボタンを押すと、現在のデータリストの中から、ランダムな100件のみが表示されます。行番号が「黒」であったものが「青」に変わって検索データのみが表示されていることを表しています。
その他のデータは行が非表示となって表示されていません。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
すべてのデータを表示する
「データ」「フィルタ」「すべて表示」を選択します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
再度の任意100件の抽出
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
同じ条件や、違う条件(C1の件数を変えることで)でも、
データ範囲(セルA113:M30400)のいずれかのセルを選択し、「データ」「フィルタ」「フィルタオプションの設定」を選択し、「抽出先」に、「選択範囲内」を選択し、「リスト範囲」に$A$113:$M$30400が選択されていることを確認し、「検索条件範囲」に$D$1:$D$111を選択入力します。抽出データの取り出し
この抽出されているデータのみを別のシートにコピーすることもできます。
抽出データの先頭行のA133を選択し、{Shift}+{Ctrl}+{↓}で、最終行までを選択し、さらにその状態で{SHift}+{Ctrl}+{→}で右端までのデータを選択すると、抽出されたすべてのデータが選択できます。
「編集」「コピー」で抽出データをコピーします。
別のシートやブックを開いて、「編集」「貼り付け」しますと、抽出データのみが、貼り付けできます。
計算した結果###になりました###が表示されるのは、エラーとは限りません。
表示桁数が足りない場合に表示されます。
セルの幅を調節して###が本当に###の連続かどうか確認してください。
単に、セルの幅が足りない場合には、他の表示となります。
#VALUE!
#N/A!
#DIV/0!
#NAME?
などのエラー表示となった場合は、そのエラー内容にあった対応となります。
エラー表示の意味は、「質問と回答」の「計算式・関数」「2002/11/2」
「エラー表示の意味」を参照してください。
セルの中の特定の文字を含むセルの値を合計する文字列検索関数(FIND)と、情報関数(ISNUMBER)と「配列数式」を使います。
具体的には、
セル範囲A1:A4に文字列
C1:C4に、数値
が入力してあるとします。
A B C D E 1 イチゴ牛乳 68 牛乳 2 コーヒー牛乳 80 148 3 ミックスジュース 120 4 フルーツジュース 100
セルE1に検索文字列
セルE2に数式
=SUM(IF(ISNUMBER(FIND(E1,A1:A4)),C1:C4))
と入力し{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
{=SUM(IF(ISNUMBER(FIND(E1,A1:A4)),C1:C4))}
自分で{ }を入力してはいけません。
関数の説明は、「関数の使い方説明」
の「文字列関数」のFIND
http://www.katch.ne.jp/~kiyopon/kansuu/val.html#find
「情報関数」のISNUMBER
http://www.katch.ne.jp/~kiyopon/kansuu/cell.html#isnumber
「論理関数」のIF
http://www.katch.ne.jp/~kiyopon/kansuu/if.html#if
「数学+三角」のSUM
http://www.katch.ne.jp/~kiyopon/kansuu/abs.html#sum
を参照してください。
1000以下の数字を切り捨てたい12,345,678→12,3451000のけたで切り捨てして、1000のけたまで表示するのですね。
切り捨てには、ROUNDDOWN関数を使います。
また、1000のけたまで表示するには、1000で割ればいいですね。具体的には、セルA1に数値
12,345,678
が入力してありとします。
セルB1に数式
=ROUNDDOWN(A1,-3)/1000
と入力します。
12,345
が返ります。
ファイルを超えた串刺し集計ファイルを超えた串刺し集計も、問題なくできます。
すべてのファイルを開いておきます。
Book1.xlsのシートSheet1のセルA1
Book2.xlsのシートSheet1のセルA1
Book3.xlsのシートSheet1のセルA1
を合計するとします。
合計を返すセルを選択し、
=SUM(
と入力します。
「ウィンドウ」で、「Book1.xls」を開き、シートSheet1のセルA1を選択し、,を入力します。
続いて、「ウィンドウ」で、「Book2.xls」を開き、シートSheet1のセルA1を選択し、,を入力します。
「ウィンドウ」で、「Book3.xls」を開き、シートSheet1のセルA1を選択し、)Enterを入力します。
セルには、
=[Book1.xls]Sheet1!A1+[Book2.xls]Sheet1!A1+[Book3.xls]Sheet1!A1
と入力されます。
この式をコピーして、対象のセル範囲に貼り付けます。
角度の計算360°-217°30′55″参考になる質問が「質問と回答」の「計算式・関数」「2002/4/6」「角度の入力方法・表示方法・計算方法」にあります。ご質問の場合は、セルA1:A3に角度を表示するものとします。
セル範囲A1:A3を選択し、セルの書式設定で「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「[h]°m′s″」と入力し「ＯＫ」ボタンを押します。
A 1 360° 2 217°30′55″ 3 =a1-A2
360°を
セルA1に
360:00:00
と入力し、
217°30′55″を
セルA2に
217:30:55
と入力します。
セルの書式設定で「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「[h]°m′s″」と入力し「ＯＫ」ボタンを押します。
セルA3に
=A1-A2
と入力します。
計算結果を偶数（きりあげ）にするISEVEN関数と、IF関数を使います。
ISEVEN(数値) 数値が偶数のときに TRUE を返します
計算式の結果が奇数の場合には、計算式+1とします。
=計算式+IF(ISEVEN(計算式),0,1)
とします。
ISEVEN関数の説明は「関数の使い方説明」
http://www.katch.ne.jp/~kiyopon/kansuu/index.htm
の「情報関数」ISEVEN
http://www.katch.ne.jp/~kiyopon/kansuu/cell.html#iseven
を参照してください。
同じような関数で、ISODD関数あります。
数値が奇数のときに TRUE を返します
条件に合致するセルの行番号を返す「シート」「Sheet1」の会社名と備考のリストから、シート「目次」の会社名に合致する行番号を返すものとします。
シート「Sheet1」
A B C 1 社名 備考 2 3 Ａ社 ○○○○・・・ 4 △△△△・・・ 5 ■■■■・・・ 6 □□□□・・・ 7 ▲▲▲▲・・・ 8 Ｂ社 ○○○○・・・ 9 △△△△・・・ 10 ■■■■・・・ 11 Ｃ社 ○○○○・・・ 12 Ｄ社 ○○○○・・・ 13 △△△△・・・ 14 □□□□・・・ 15 ▲▲▲▲・・・ 16 Ｅ社 ○○○○・・・ 17 △△△△・・・ 18 ▲▲▲▲・・・ 19 Ｆ社 ○○○○・・・ 20 △△△△・・・ 21 ■■■■・・・ 22 □□□□・・・ 23 ▲▲▲▲・・・ 24 Ｇ社 ○○○○・・・ シート「目次」
A B 1 目次 2 3 Ａ社 3 4 Ｂ社 8 5 Ｃ社 11 6 Ｄ社 12 7 Ｅ社 16 8 Ｆ社 19 9 Ｇ社 24
「配列数式」を使います。
セルB3には、
=MAX(IF(A3=Sheet1!$B$3:$B$35,ROW(Sheet1!$B$3:$B$35)))
と入力し、{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
確定後、数式は{ }でくくられます。
{=MAX(IF(A3=Sheet1!$B$3:$B$35,ROW(Sheet1!$B$3:$B$35)))}
自分で{ }を入力してはいけません。
このセルをコピーして、下のセル範囲B4:B12まで貼り付けます。
数式の意味
セル「A3」と、シート「Sheet1」のセル範囲「$B$3:$B$35」が同じ場合に、その行番号「ROW(Sheet1!$B$3:$B$35))」を返します。
電話番号のハイフンを取る方法SUBSTITUTE関数で文字列を置き換える方法
A1セルに電話番号が
023-456-7890
と、入力されているとします。
=SUBSTITUTE(A1,"-","")
で、文字列として
0234567890
がかえります。
電話番号のハイフンごとにハイフンを取った状態でセルに分けたいまた、A1セルの電話番号を
A1セルに023
B1セルに456
C1セルに7890
とする場合は、
「データ」「区切り位置」を使います。
A1セルを選択し、「データ」「区切り位置」を選択し、「区切り位置定ウィザード1/3」の「元のデータ形式」の「カンマやタブなどの区切り文字によってフィールド毎に区切られたデータ」を選択し、「次へ」ボタンを押し、「」「区切り文字」に「その他」を選択し、「-」を入力し「完了」ボタンを押します。
文字列の右端から2文字削除するLEN関数で文字数を数えて-2とし、LEFT関数と組み合わせて文字を左から取り出します。
A1セルにデータが
0000000k001701 計
と入力されているとします。=LEFT(A1,LEN(A1)-2)
で、右端から2文字分が削除されたデータが返ります。
0000000k001701LEN(A1)で、A1セルの文字数を返します。
16文字です。
16-2で14文字です。
LEFT関数で、左から14文字取り出します。
各セルに1名づつ入力した名前を無作為に並べ替える方法参考になる質問が「質問と回答」
の「計算式・関数」「2004/8/2」「重複しない乱数を複数セルに返す」にあります。見本のファイルを参照してください。
見本では１００人としています。
シート{Sheet2}には、入力した「名前」の前に1列追加し、「連番」を付けておきます。
シート{並べ替え計算}で、セルA2:A101に、1から100までの重複しない乱数を発生させています。
シート{Sheet2}のC列にはその乱数を表示し、D列に名前を返します。
再計算が、手動になっていますので、[F9]ボタンを押すと再計算されて、並べ替えられます。
12,300,500円のように右側に"円"が付いているセルを集計したい実際のデータが示してありませんので的確な回答ができません。
12,300,500円は、入力されているデータが文字列の"円"を含んでいるので、そのままでは計算できません。
「配列数式」を使い、IF関数と組み合わせで、文字列関数のRIGHTで右端が"円"の場合、LEFTで文字数-1で、数値のみ取り出し、VALUE関数で数値とに変換し、それをSUM関数で合計します。具体的には、セル範囲A1:A10に文字列"円"を含むデータが入力されているとします。
セルに
=SUM(IF(RIGHT(A1:A10,1)="円",VALUE(LEFT(A1:A10,LEN(A1:A10)-1))))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後数式は、{ }でくくられます。
{=SUM(IF(RIGHT(A1:A10,1)="円",VALUE(LEFT(A1:A10,LEN(A1:A10)-1))))}
自分で{ }を入力してはいけません。
添付ファイルを参照してください。
40811と入力したものを日付の2004/8/11にするご質問の場合は、先頭の「200」がない値の40811ですから次のようになります。
A1セルに40811と入力されているとします。
B1セルに
=DATEVALUE("200"&LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2))
と入力します。
日付シリアル値
38210
が返ります。
計算式を日付シリアル値に直すには、セルを選択して、「編集」「コピー」し、「編集」「形式を選択して貼り付け」で、「値」として「ＯＫ」ボタンを押します。
日付の表示形式にするには、「書式」「セル」で「セルの書式設定」ダイアログボックスで「表示形式」の「分類」を「日付」にします。
データの個数の関数COUNTIFで複数の条件を指定する場合セル範囲A1:A6の値が、○または、●の場合のセル数を返すには、COUNTIF関数を次のように使います。=COUNTIF(A1:A6,"○")+COUNTIF(A1:A6,"●")
=A5,=A7,=A9と参照式を行順に二行毎にオートフィルしたいINDIRECT関数を使います。
A B C 1 A1への参照式 2 A3への参照式 3 A5への参照式 A1への参照式 4 A7への参照式 A5への参照式 5 A9への参照式 A9への参照式 6 A11への参照式 7 A13への参照式 8 A15への参照式 9 A17への参照式 10 A19への参照式
B1セルにA1セルへの参照、B2セルにA3セルへの参照式入力をするには、
=INDIRECT("A"&ROW()*2-1,TRUE)
この式を下のセル範囲にフィルします。
この式の意味
ROW()*2-1
B1セルで、式の入力してある行は、1行目ですから
=1*2-1
=2-1
=1B2セルで、式の入力してある行は、2行目ですから
=2*2-1
=4-1
=3B3セルで、式の入力してある行は、3行目ですから
=3*2-1
=6-1
=5となります。4行おきにオートフィルする場合
C3セルにA1セルへの参照、C4セルにA5セルへの参照式入力をするには、
=INDIRECT("A"&(ROW()-2)*4-3)
この式を下のセル範囲にフィルします。
この式の意味
(ROW()-2)*4-3
C3セルで、式の入力してある行は、3行目ですから
=(3-2)*4-3
=1*4-3
=4-3
=1C4セルで、式の入力してある行は、4行目ですから
=(4-2)*4-3
=2*4-3
=8-3
=5C5セルで、式の入力してある行は、5行目ですから
=(5-2)*4-3
=3*4-3
=12-3
=9となります。INDIRECT関数は、指定した文字列により参照されるセルの値を返します。
INDIRECT関数の説明は、「関数の使い方説明」
の「検索+行列」の「INDIRECT」を参照してください。
ROW関数は、行番号を返します。
INT関数は、数値を最も近い整数に切り捨てます。
エラーや文字列のセルを含まずに、数値のセルを合計するISNUMBER関数とIF関数を「配列数式」で記述して求めます。セル範囲A1:A21の数値のみのセルを合計する場合は、
=SUM(IF(ISNUMBER(A1:A21),A1:A21))
と入力し、{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
{=SUM(IF(ISNUMBER(A1:A21),A1:A21))}
自分で{ }を入力してはいけません。ISNUMBER関数の説明は、「関数の使い方説明」の「情報関数」ISNUMBERを参照してください。
二つの日付間の月数(年数、日数)を返すDATEDI関数を使います。
DATEDIF関数は、「関数の挿入」ダイアログボックスには表示されませんが、関数を挿入した後で、「関数の挿入」ボタン{Fx}をクリックすると「関数の引数」ダイアログボックスで、関数の引数を編集することができます。
書式
=DATEDIF(開始日,終了日,単位)
開始日
終了日
単位
"y" 期間内の満年数
"m" 期間内の満月数
"d" 期間内の満日数
"md" 1ヵ月未満の日数
"ym" 1年未満の月数
"yd" 1年未満の日数A1セルに開始日1-Nov-03
B1セルに終了日30-Nov-04
を入力してある場合、13
を返すには、終了日の1ヵ月に満たない月数を含めていますので、次のようになります。
=DATEDIF(A1,B1,"m")+1
試験結果の偏差値を求める見本ファイルを参照してください。
まず、各科目の平均点を求めます。
セルD14には、
=AVERAGE(D4:D13)
と入力します。
セルD14の数式をセル範囲E14:H14にコピーします。
次に、標本標準偏差を求めます。
セルD15には、
=STDEVP(D4:D13)
と入力します。
セルD15の数式をセル範囲E15:H15にコピーします。
次に、標本標準偏差をもとに、偏差値を求めます。
セルJ4には、
=50+10*(D4-D$14)/D$15
と入力します。
セルJ4の数式をセル範囲J5:J13,K4:L13にコピーします。
同様にセル範囲M6:M9,M13,N4:N5,N10:N13の貼り付けます。
重複しない乱数を複数セルに返す見本のファイルではA列に1-50までの重複しない乱数を返します。
{F9}ボタンを押すと再計算によって、新しい乱数が返ります。
RAND関数と、RANK関数と、INDIRECT関数を使います。セルB2には、乱数の開始値を入力します。(この場合3)
セルC2には、乱数の終了値を入力します。(この場合50)
セルD1には、乱数を発生させる関数=RAND()を入力し、このセルをコピーして下のセル範囲D50まで貼り付けます。
この場合、50種類ですのでD50まで貼り付けましたが、100種類の場合は、D100までコピーします。セルA2には、開始値をセルB2、終了値をセルC2とした重複しない乱数が返ります。
=RANK(D1,INDIRECT("&D$1:$D$"&$C$2-$B$2+1))+$B$2-1
と入力します。
数式の意味
文字列"$D$1:$D$"と、セル$C$2の値-セル$B$2の値+1(この場合50-3+1=48)を文字列結合関数&で結合し、A1形式のセル参照式とします。
$D$1:$D$48となり、数式=RANK(D1,$D$1:$D$48)+$B$2-1が返ります。
数式の意味
セルD1の値が、セル範囲$D$1:$D$48の何番目に大きいかを返し、セル$B$2の値を加算し、1を減算します。このセルをコピーして下のセル範囲A51まで貼り付けます。
この場合、50種類ですのでD50まで貼り付けましたが、100種類の場合は、D100までコピーします。
A列とＢ列で同じSUM関数で計算をしているのに答えが違う一部のセルの数値が、文字列として入力されているために合計値が合いません。
文字列は、SUM関数では数値として認識しませんので、計算されません。
「編集」「ジャンプ」で、「セル選択」ボタンを選択し、「選択」グループの「数式」ラジオボタンを押し、「文字」のみにチェツクを入、「文字列」として認識している数値を選択します。
選択されたセルが文字列として入力されています。
それらのセルを選択した状態で、{f2}キーを押して{Enter}を押し、それを繰り返します。
2004/7/8から155日後の年月日（曜日）は日付の計算については、「質問こと回答」の「計算式・関数」「2001/12/19」「日付の計算をしたい」に説明があります。日付はそのまま、四則計算ができます。
エクセルでは、1日が1で、日付シリアル値として認識しています。
従って、
2004/7/8
の155日後は、
=DATVALUE("2004/7/8")+155
となります。
セルA1に
2004/7/8
と入力されています。
セルA2に
155
と入力されています。
=A1+A2
で、2004/12/10
が、返ります。
日付表示にするためには、「書式」「セル」で、「表示形式」の「分類」で、「日付」を選択し、「種類」欄で表示形式を「2001/3/14」など選択し、「ＯＫ」ボタンを押します。
曜日も表示したいばあいは、「書式」「セル」で、「表示形式」の「分類」で、「ユーザー定義」を選択し、「種類」欄で表示形式を「yyyy/m/d aaa」のようにaaaをつけた書式にし、「ＯＫ」ボタンを押します。
○と入力されたセルの数を返すCOUNTIF関数を使います。
具体的には、
セル範囲C2:C17の中で"○"のセルの合計は
=COUNTIF(C2:C17,"○")
となります。
COUNTIF関数の説明は「関数の使い方説明」
の「統計関数」の「COUNTIF」を、参照してください。
指定日付までのデータの累計を計算するSUMIF関数を使います。
A B C D E 1 日付 実績 3月25日 <=38071 67 2 3月21日 13 3 3月22日 14 4 3月23日 13 5 3月24日 11 6 3月25日 16 7 3月26日 15
セルC1には、条件となる日付を入力します。セルD1には、セルC1の日付以下のという条件式を返します。
="<="&C1セルE1には、セル範囲A2:A7が、セル(D1)の条件式に、該当するセル範囲B2:B7の合計を返します。
=SUMIF(A2:A7,D1,B2:B7)
開始日と終了日を含む日付の日数を返す日付を入力すると、実際のデータは、その日の0:00となっています。
開始日を、2004/7/1と、入力すると
2004/7/10:00終了日を、2004/7/31と、入力すると
2004/7/310:00です。
セルの書式設定の「分類」「日付」で「1997/3/1413:30」を選択して見てください。終了日の7/31は、7/30の24:00と同じですから、引き算するとその日を含まないことになります。
最終日が、7/31と認識されるためには、+1する必要があります。
具体的には、
セルA1に2004/7/1
セルA2に2004/7/31
と入力してある場合、
=A2-A1+1
とします。
セルの書式設定の「表示形式」「分類」で、「標準」を選択し「ＯＫ」ボタンを押します。
31が返ります。
2003/12/20から2004/3/29の月数を端数の月は加算しないで2と返す開始日が月の途中の場合は、EOMONTH関数で次の月の１日をかえし、初日の場合はその月の1日を返します。
終了日が月の途中の場合は、EOMONTH関数でその月の1日を返し、最終日の場合は次の月の1日を返します。
DATEDIF関数で、それぞれの月の期間の満月数を計算します。
計算式は、
A1セルに開始日2003/12/20
B1セルに終了日2004/3/29
と入力されている場合
=DATEDIF(EOMONTH(A1,IF(A1=EOMONTH(A1,-1)+1,-1,0))+1,EOMONTH(B1,IF(B1=EOMONTH(B1,0),0,-1))+1,"m")
となります。
80と入力し、1:20と表示させたい80と入力した同じセルで、1:20と表示させることはできません。
別のセルに数式を入力し、セルの書式設定で、m:sと指定します。
具体的には、セルA1に80と入力してあるとします。
セルB1に1:20
と表示するには、
セルB1を選択し、「書式」「セル」で、「セルの書式設定」ダイアログボックスの「表示形式」「の分類」で、「ユーザー定義」を選択し、「種類」欄に「m:s」と入力し、「ＯＫ」ボタンを押します。
一の位が5刻みで切り捨てる関数(74.5→70 78→75)FLOOR関数を使います。
(基準値の最も近い倍数に丸める(ＦＬＯＯＲは切り捨て、ＣＥＩＬＩＮＧは切り上げ))
書式:=FLOOR(数値,基準値)具体的には、
セルA1に数値74.5が入力してある場合、5で切り捨てるには、
=FLOOR(A1,5)
となります。
参考に切り上げの場合は、CEILING関数を使います。
数式およびリンクをかけたときIFおよびISBLANK関数を使わずに0を表示しない方法方法は、2つあります。
1つ目「書式」で設定「書式」「セル」で「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し「種類」欄に「G/標準;G/標準;」と入力し「ＯＫ」ボタンを押します。
表示書式では、「正の値の表示書式;負の値の表示書式;ゼロの値の表示書式;文字列の表示書式」の4つの書式が設定できますので、ゼロの値の表示書式をナシにします。
2つ目「ツール」「オプション」で設定「ツール」「オプション」の「表示」タグを開き「ウィンドゥオプション」で「ゼロ値」のチェックを外し「ＯＫ」ボタンを押します。
数値の下三桁だけを並べ替えの対象にしたい新しく列を挿入して、その列に該当の数値の下三桁だけをRIGHT関数で取り出します。
具体的には、A列2行目からデータが元の数値として入力されているとします。
D列にその下三桁を取り出す関数。
=VALUE(RIGHT(A2,3))
と入力します。
実際には、数値として認識するためにVALUE関数を組み合わせています。
「データ」「並べ替え」D列を「最優先されるキー」としてを行います。
時間で、秒以下の単位を四捨五入や切り上げ、切捨てにする方法同じ入力セルで、表示することはできないようです。
別のセルに計算式で、表示してみます。
ROUND関数を使って四捨五入
ROUNDUP関数を使って切り上げ
ROUNDDOWN関数を使って切り捨て
します。
さらに、時刻シリアル値として認識するために、HOUR関数、MINUTE関数、SECOND
関数、TIMEVALUE関数を使っています。
四捨五入
=TIMEVALUE(HOUR(A1)&":"&MINUTE(A1)&":"&ROUND(VALUE(TEXT(A1,"s.00")),0))切り上げ
=TIMEVALUE(HOUR(A1)&":"&MINUTE(A1)&":"&ROUNDUP(VALUE(TEXT(A1,"s.00")),0))切り捨て
=TIMEVALUE(HOUR(A1)&":"&MINUTE(A1)&":"&ROUNDDOWN(VALUE(TEXT(A1,"s.00")),0))
となります。
行を挿入してもSUM関数の数式を自動的に変更するには行の合計は、行を挿入すれば自動的に数式が挿入行の合わせて変更されるはずです。
この数式が変更されない場合は、合計行を選択して行挿入した場合です。INDIRECT関数を使います。
具体的には、A列のセルのいずれかのセルで、その上の行までのセル範囲を返す式は、
=INDIRECT("A1:A"&ROW()-1,TRUE)
となります。
A10セルで数式を入力した場合は、"A1:A9"が返りますが、数式としては認識できないので、#VALUEが返ります。
これをSUM関数と組み合わせて、=SUM(A1:A9)のようにするには、
=SUM(INDIRECT("A1:A"&ROW()-1,TRUE))
となります。10行目のセルに数式を入力し、1行目のセルからその上の行までの合計を返す式は
列を固定しないで、指定するためにCOLUMN関数を組み合わせます。
=SUM(INDIRECT("R1C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
と入力します。
数式の意味
文字列"RC1"と、数式入力セルの列番号と、文字列"R"と数式入力セルの行番号-1と、文字列"C"と、数式入力セルの列番号を文字列結合し、R1C1形式のセル参照式とし、SUM関数で合計します。
A10セルに入力した場合、R1C1:R9C1へのセル参照式となります。
重複データを取り出す参考になる質問が「質問と回答」の「計算式・関数」「2002/9/14」「重複データに印を付ける重複データの２つ目以降に印を付ける」にあります。
COUNTIF関数を使います。具体的には、
A列の会員番号が重複しているデータを抽出します。
A B C D 1 会員番号 氏名 種目 重複 2 2000 ああああああ サッカー 2 3 2000 ああああああ 水泳 2 4 2001 いいいいいい サッカー 1 5 2002 うううううううう サッカー 2 6 2002 うううううううう 野球 2
セルD2には、会員番号の列(A列)のデータが同じ場合にその数を返します。
=COUNTIF($A$2:$A$6,A2)
2以上の場合は、重複データがあることになります。
重複したデータのみを抽出するには、
データ範囲のいずれかのセルを選択し、「データ」「フィルタ」「オートフィルタ」で、D列の▼で、「オプション」で、「1」「と等しくない」を選択します。
このセル範囲を選択して、「編集」「コピー」して、別のセルに「貼り付け」すれば、会員番号の重複しているデータが抽出できます。
すべてのデータを表示するには、「データ」「フィルタ」「オートフィルタ」で、、「すべて」を選択します。
「オートフィルタ」を解除して、▼を消す場合は、「データ」「フィルタ」「オートフィルタ」を選択して、チェツクを外します。
勤務開始時間と終了時間から簡易グラフの作成見本のファイルを参照してください。
横軸の時刻は、8:00,8:30のように30分ごとの入力とします。ただし、:30の時刻となるセルは表示しないように「書式」「セル」で「フォントの色」を「白」にして背景と同じにします。
また、:00の時刻のセルは、「書式」「セル」で、「表示形式」「分類」で「ユーザー定義」を選択し、「種類」欄に「h」と入力し、時のみを表示するようにします。
「条件付き書式の設定」で、時刻に対する書式を設定します。
具体的には、
色塗りをするセル（E3）を選択し、「書式」「条件付き書式」で「条件1」に「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
「=AND($C3<=E$2,$D3>E$2)」と入力し「書式」で「パターン」で「色」をしていして「ＯＫ」ボタンを押します。
A列の最終行のデータをB列に返すINDIRECT関数を「配列数式」で使います。
セルB1に
=INDIRECT("A"&MAX(IF(A1:A65535<>"",ROW(A1:A65535))),TRUE)
と、入力し、{Ctrl}+{Shift}+{Enter}で「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で{ }を入力してはいけません。INDIRECT関数についての説明は「関数の使い方説明」
の「検索+文字列」「INDIRECT」
を参照してください。また、私の作成した「下端データの検索」
という「ユーザー定義関数」を使えば、選択範囲のデータが入力されているもののうち、列の一番下端のデータを返します。
そのソフトを使う場合は、「アドインソフトを使う場合の注意点」を必ずお読みください。
税込み金額から税抜き金額を返す 税抜き金額から税込み金額を返す税込み金額から税抜き金額を返す
セルA1に税込み金額が10000と入力されているとします。
5%の消費税の場合は
セルB1には、
=ROUNDDOWN(A1/1.05,0)
と入力します。
9523が返ります。
小数点以下を切り捨てしています。税抜き金額から税込み金額を返す
セルA1に税抜き金額が9524と入力されているとします。
5%の消費税の場合は
セルB1には、
=ROUNDDOWN(A1*1.05,0)
と入力します。
10000が返ります。
小数点以下を切り捨てしています。
整数に丸めた誤差を無くして、個々の値に反映したい計算結果の合計値を、整数に丸めた値と合わせたいのですね。
A B C D E F G H I 1 2 真値 整数値 真値との差 補正値 補正結果 3 売上 構成比 5,000 5,001 0.46821 -1 5000 4 あ店 10,175,312 5.99424% 299.7119 300 0.288069 0 300 5 い店 16,848,327 9.92529% 496.2644 496 0.264352 0 496 6 う店 15,954,670 9.39884% 469.9419 470 0.05815 0 470 7 え店 15,070,606 8.87804% 443.9019 444 0.098091 0 444 8 お店 7,234,415 4.26177% 213.0884 213 0.088354 0 213 9 か店 9,499,680 5.59623% 279.8113 280 0.188685 0 280 10 き店 1,965,100 1.15763% 57.88166 58 0.118343 0 58 11 く店 8,000,324 4.71296% 235.6481 236 0.351938 0 236 12 け店 18,342,917 10.80574% 540.2872 540 0.287224 0 540 13 こ店 10,041,291 5.91529% 295.7644 296 0.235633 0 296 14 さ店 9,837,559 5.79527% 289.7635 290 0.236521 0 290 15 し店 17,537,032 10.33100% 516.55 517 0.44997 0 517 16 す店 20,367,375 11.99835% 599.9173 600 0.082741 0 600 17 せ店 8,876,926 5.22936% 261.4682 261 0.468212 -1 260 18 169,751,534
=ROUND(E4,0)
この式を下のセル範囲にコピーします。セルG3には、真値と絶対値の差の一番大きな値を返します。
=MAX(G4:G17)セルG4には、真値と四捨五入した値との差を絶対値（プラスマイナスをなくして）返します。
=ABS(E4-F4)
このセルをコピーして下のセルに貼り付けます。セルH4には、セルG3に表示されている、真値と絶対値の差の一番大きな値と比としてセルに、セルF3とE3との差（整数値の合計と真値の合計との差）を返します。
=IF($G$3=G4,$F$3-$E$3,0)
このセルをコピーして下のセルに貼り付けます。セルI4には、整数値との補正結果を返します。
=F4+H4
このセルをコピーして下のセルに貼り付けます。
二つの列のデータが共に重複しているデータ行を消したい2つの列のデータも文字列結合関数で結合して1つのデータとします。
そのデータをCOUNTIF関数で重複しているかどうかを返します。
A B C D E 1 項目1 項目2 項目3 項目結合 重複 2 1014040 C310 2004/4/30 1014040C310 1 3 1014040 C410 2004/5/1 1014040C410 2 4 1014040 C410 2004/5/1 1014040C410 2 5 1014040 C510 2004/5/3 1014040C510 2 6 1014040 C510 2004/5/1 1014040C510 2 7 1014041 C310 2004/5/8 1014041C310 1 8 1014041 C410 2004/5/9 1014041C410 2 9 1014041 C410 2004/5/9 1014041C410 2 10 1014041 C510 2004/5/9 1014041C510 2 11 1014041 C510 2004/5/9 1014041C510 2 12 1014042 C310 2004/5/11 1014042C310 1 13 1014042 C410 2004/5/11 1014042C410 2 14 1014042 C410 2004/5/11 1014042C410 2 15 1014042 C510 2004/5/12 1014042C510 2 16 1014042 C510 2004/5/11 1014042C510 2 セルD2には、
A列とB列のデータをを結合します。
=A2&B2
この式をコピーしてしたの行に貼り付けします。セルE2には、重複したデータの場合2以上の値を返します。
=COUNTIF($D$2:D16,D2)
この式をコピーして下のセルに貼り付けます。
重複データは2,以上が返ります。
データ範囲を選択して、「データ」「フィルタ」「オートフィルタ」で、E列のデータの「オプション」「2以上」として検索し、その検索データ範囲を削除します。
124502と入力して12分45秒02、または12’45″02と（緯度経度の）表示同じセルでの入力と表示はできません。
別のセルに数式を入力します。
A B C 1 034502 03′45″.02 laptime 2 074852 07′48″.52 04′03″.50 3 110023 11′00″.23 03′11″.71 4 144502 14′45″.02 03′44″.79
通常は時刻の入力には、時:分:秒.00としますが、124502とした場合には数値の124502として認識されます。
入力セルと同じセルで、時刻として認識させることはできませんが、別のセルで計算式で時刻として認識できるようになります。
LEFT関数で、右の２文字から分を取り出し
MID関数で、3文字目からの2文字を取り出します。
RIGHT関数で、左の２文字から分を取り出し
文字列結合関数&で時刻入力形式で結合し
timevalue関数で、時間として認識させることができます。
具体的には、A1セルに時刻が124502と入力されている場合他のセルに
=TIMEVALUE("00:"&LEFT(A1,2)&":"&MID(A1,3,2)&" "&RIGHT(A1,2))
と言う計算式を使えば
0.008854398
という時刻シリアル値となります。
これを時刻表示にするために「書式」「セル」で「セルの書式設定」ダイアログの「表示書式」の「分類」で「ユーザー定義」を選び「種類」欄に「mm分ss秒.00」と入力し、「ＯＫ」ボタンを押します。
12分45秒.02
と表示されます。
秒の後に小数点がついて1/100票の表示となってしまいます。
12′45″02
と表示するには、
書式」「セル」で「セルの書式設定」ダイアログの「表示書式」の「分類」で「ユーザー定義」を選び「種類」欄に「mm′ss″.00」と入力し、「ＯＫ」ボタン」を押します。laptimeを計算するには、時刻シリアル値として計算されたデータを四則演算で計算します。
具体的には、
A1セルからA4セルに500,100,1500,2000のタイムをそれぞれ入力します。
セルの書式設定は、「文字列」としておきます。
A1セルに034502
A2セルに074852
A3セルに110023
A4セルに144502
と入力されている場合。
B1セルに
=TIMEVALUE("00:"&LEFT(A1,2)&":"&MID(A1,3,2)&" "&RIGHT(A1,2))
と入力し、下のセル範囲B4までコピーします。
C2セルには、
=B2-B1
と入力します。
このセルをコピーして下のセル範囲C4まで貼り付けます。
指数表示の文字列の「4.98706E+12」を数値にしたいセルA1に、「4.98706E+12」と指数表示で入力されているとします。
別のセルに、
1
と入力し、コピーします。
対象の指数表示の文字列数値のセルを選択し、「編集」「形式を選択して貼り付け」で「演算」グループの「乗算」にチェックを入れ「ＯＫ」ボタンを押します。
シートAとシートBに重複するデータをシートCに取り出す参考になる質問が「質問と回答」の「計算式・関数」「2002/9/14」「重複データに印を付ける重複データの２つ目以降に印を付ける」にあります。
これを利用して、シート[A]
A B C 1 項目1 項目2 項目3 2 aaa 222 535 3 bbb 111 545 4 ccc 333 555
シート[B]
A B C D 1 項目1 項目2 項目3 重複=0 2 999 222 575 0 3 000 333 223 0 4 123 444 945 1
セルD2には、
シートAのセル範囲B2:B4と、このシートのセルB2が重複している場合は、0を返し、それ以外は1を返します。
=IF(COUNTIF(A!$B$2:$B$2,B!B2)>=1,0,1)
この式をコピーして、下のセル範囲に貼り付けます。
シート[B]のD列には、重複データに0が表示されますので、データ範囲のいずれかのセルを選択し、「データ」「フィルタ」「オートフィルタ」で、「0」を表示します。
そのデータ範囲をコピーしてシート[C]に貼り付けます。
すべてのデータを表示するには、「データ」「フィルタ」「オートフィルタ」のチェックを外します。シート[C]
A B C 1 項目1 項目2 項目3 2 999 222 575 3 000 333 223
COUNTIF関数の説明は「関数の使い方説明」の「統計関数」COUNTIFを参照してください。
リストから検索条件と男、女別に該当するデータを返す
A B C D 1 以上 男 女 男 2 1 5 3 33 3 21 6 9 6 4 41 7 10
セルA2:A4に境界となる数値、B2:B4に男の値、C2:C4に女の値を入力下リストを作成しておきます。
セルD1に男、か女を入力します。(この場合は男以外は女として認識します)
D2に検索値の数値を入力します。(この場合は33と入力)セルD3には、
=VLOOKUP(D2,A2:C4,IF(D1="男",2,3),TRUE)
と入力します。
式の意味
検索値(D2)の値が、セル範囲A2:C4の左端列(この場合A列)に合致する行の、セルDの値が"男"の場合、2列目それ以外は3列目の値を返します。
データ範囲から検索値に該当するデータを返すVLOOKUP関数を使います。
A B C D 1 あ 10 い 9 2 い 9 3 う 8 4 え 7 5 お 6 6 か 5 7 き 4 8 く 3 9 け 2 10 こ 1
セルD1には、
=VLOOKUP(C1,A1:B10,2,FALSE)
と入力します。
式の意味
検索値C1が、セル範囲,A1:B10の左端列(この場合A列)に合致する行の,2列目(この場合B列)のセルの値を返します。
VLOOKUP関数の説明は、「関数の使い方説明」の「検索+行列」「VLOOKUP」を参照してください。
離れた位置の複数セル範囲の場合のCOUNTIF関数の使い方A1,A5:A10,A12というように複数範囲のセルではそれぞれにCOUNTIF関数を使って結果を出して、その合計とします。
具体的には、次のように検索条件が"○"のセル数を返す場合。
=COUNTIF(A1,"=○")+COUNTIF(A5:A10,"=○")+COUNTIF(A12,"=○")
となります
勤務時間の計算で、控除時間を含む場合添付ファイルを参照してください。
セルC2:D6に休憩開始時間と休憩終了時間を入力しておきます。
IF関数で、勤務時間からそれぞれの休憩時間を差し引きます。
A B C D 1 休憩開始 休憩終了 2 12:30 13:30 3 19:15 19:45 4 23:45 24:45 5 26:45 27:15 6 29:15 29:45 7 8 開始時間 終了時間 勤務時間 9 8:30 19:30 9:45 10 8:30 20:30 10:30 11 20:00 30:30 8:30 12 20:00 32:30 10:30
セルD9には、
=(C9-B9)-IF($B9<$C$2,IF($C9<$C$2,0,IF($C9>$D$2,$D$2-$C$2,$C9-$C$2)),IF($B9<$D$2,IF($C9>$D$2,$D$2-$B9,0),0))-IF($B9<$C$3,IF($C9<$C$3,0,IF($C9>$D$3,$D$3-$C$3,$C9-$C$3)),IF($B9<$D$3,IF($C9>$D$3,$D$3-$B9,0),0))-IF($B9<$C$4,IF($C9<$C$4,0,IF($C9>$D$4,$D$4-$C$4,$C9-$C$4)),IF($B9<$D$4,IF($C9>$D$4,$D$4-$B9,0),0))-IF($B9<$C$5,IF($C9<$C$5,0,IF($C9>$D$5,$D$5-$C$5,$C9-$C$5)),IF($B9<$D$5,IF($C9>$D$5,$D$5-$B9,0),0))-IF($B9<$C$6,IF($C9<$C$6,0,IF($C9>$D$6,$D$6-$C$6,$C9-$C$6)),IF($B9<$D$6,IF($C9>$D$6,$D$6-$B9,0),0))
と入力し、下のセル範囲に貼り付けます。
日付に対する曜日を文字列で返す添付ファィルを参照してください。
セルD7には、曜日を文字で返します。
=CHOOSE(WEEKDAY(A7,2),"月","火","水","木","金","土,""日")
WEEKDAY関数で、セルA7の日付に対する曜日の数値を返し、その数値に対する曜日
をCHOOSE関数で数値順に表示します。
それぞれの関数の説明は、「関数の使い方説明」の
CHOOSE関数の説明は、「検索+行列」のCHOOSE
WEEKDAY関数の説明は、「日付+時刻」のWEEKDAY
を参照してください。
3月31日の半年後は9月30日とするEDATE関数を使います。
この関数は、通常では組み込まれませんので、「ツール」「アドイン」から「分析ツール」をチェツクして登録してください。
書式
EDATE(開始日,月)
具体的には、2004/3/31の6ヶ月後は
EDATE("2004/3/31",6)
とします。
セルA1に、2004/3/31と入力されているとします。
EDATE(A1,6)
セルの書式設定は、「日付」とします。
EDATE関数の使い方は、「関数の使い方説明」
の「日付」の「EDATE」
を参照してください。
13行毎のデータを返す添付ファイルを参照してください。シート[Sheet2]
セルA2には、シート[Sheet1]のB列の13行目+1のデータを返す式
=INDIRECT("Sheet1!B"&(ROW()-1)*13+1,TRUE)
を入力します。
数式の意味
INDIRECT関数で、入力した文字列に対応する参照式を作ります。
Sheet1のB列のデータを返します。
行は、ROW関数で式の入力行を返し、その値に13を掛け+1として14行目となります。
この式をコピーして下のセルに貼り付けます。セルB2には、シート[Sheet1]のE列の13行目+1のデータを返す式
=INDIRECT("Sheet1!E"&(ROW()-1)*13+1,TRUE)
を入力します。
数式の意味
INDIRECT関数で、入力した文字列に対応する参照式を作ります。
Sheet1のE列のデータを返します。
行は、ROW関数で式の入力行を返し、その値に13を掛け+1として14行目となります。
この式をコピーして下のセルに貼り付けます。
セルの数値(小数点以下）の桁数を返すにはLEN関数でセルの数値の全体の桁数を返し、その数値からFIND関数で小数点"."の位置を返した数値を減じます。
具体的には、セルA1に数値
1.5682
が入力してある場合
セルB1に小数点以下の桁数を返すには
=LEN(A1)-FIND(".",A1,1)
と入力します。
LEN(A1)
で、A1セルの数値の文字数6が返ります。
FIND(".",A1,1)
で、A1セルの文字列の中の小数点"."の位置2が返ります。
残業時間のある(条件に合致する)データ行のみを空白無く抽出するINDIRECT関数
ROW関数
COUNTA関数
IF関数
COLUMN関数
を使います。添付ファイルを参照してください。
セルAB2には、
=IF(ROW()-1>COUNTA($D$2:$D$32),"",INDIRECT("R"&SMALL(IF($D$2:$D$32<>0,ROW($D$2:$D$32)),ROW()-1)&"C"&COLUMN()-27,FALSE))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として入力します。
確定後数式は、{}でくくられます。
自分で{}を入力してはいけません。
このセルを「編集」「コピー」して、下のセル範囲AE32まで選択し、「編集」「形式を選択して貼り付け」で、「値」を選択し、「「ＯＫ」ボタンを押します。
COUNTA関数は、対象セル範囲の空白でないセル数を返します。
ROW関数は対象セルの行番号を返します。
COLUMN関数は、対象セルの列番号を返します。
0に近い値を返すIF関数と、
MIN関数を使います。
A B C 1 0.892 -0.231 -0.231 2 0.892 0.231 0.231 3 -0.234 -0.235 -0.234 セルA1に0.892
セルB1に-0.231
が入力されているとします。
セルC1には、
=IF(ABS(A1)<ABS(B1),A1,IF(ABS(A1)>ABS(B1),B1,""))
と入力します。
MIN関数の説明については、
「関数の使い方説明」の「統計関数」「MIN」を参照してください。「0以外で0に近い値」ユーザー定義関数を作成しました。選択範囲のセルで、0以外で0に近い値を返します。
負の数字だけ合計したいSUMIF関数を使います。
SUMIF関数の説明は「関数の使い方説明」
の「数学+三角」の「SUMIF」にあります。具体的には
セル範囲A1:A10に数値が入力されているとします。
負の数だけの合計は、
=SUMIF(A1:A10,"<0",A1:A10)
となります。
ゴルフスコアの５人以上のチームで上位4位までの合計点で順位を返す添付ファイルを参照してください。セルH4には、
=IF(ISERR(SMALL(IF($G$4:$G$9<>0,$G$4:$G$9),ROW()-3)),0,SMALL(IF($G$4:$G$9<>0,$G$4:$G$9),ROW()-3))
と入力し、{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
式の意味
セル範囲$G$4:$G$9で、ゼロ以外のセルがあればその値を返します。エラーの場合は0を返します。その配列範囲のデータで、小さい順の行番号から3を引いた(この場合行番号4ですから3を引いた1)番目の値を返します。
この式をコピーして、下のセル範囲H7まで連続貼り付けします。セルH10には、
=IF(ISERR(SMALL(IF($G$10:$G$15<>0,$G$10:$G$15),ROW()-9)),0,SMALL(IF($G$10:$G$15<>0,$G$10:$G$15),ROW()-9))
と入力し、{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
式の意味
セル範囲$G$10:$G$15で、ゼロ以外のセルがあればその値を返します。エラーの場合は0を返します。その配列範囲のデータで、小さい順の行番号から9を引いた(この場合行番号10ですから9を引いた1)番目の値を返します。
この式をコピーして、下のセル範囲H13まで連続貼り付けします。セルH16には、
=IF(ISERR(SMALL(IF($G$16:$G$21<>0,$G$16:$G$21),ROW()-15)),0,SMALL(IF($G$16:$G$21<>0,$G$16:$G$21),ROW()-15))
と入力し、{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
式の意味
セル範囲$G$16:$G$21で、ゼロ以外のセルがあればその値を返します。エラーの場合は0を返します。その配列範囲のデータで、小さい順の行番号から15を引いた(この場合行番号16ですから15を引いた1)番目の値を返します。
この式をコピーして、下のセル範囲H19まで連続貼り付けします。セルI4には、
=SUM(H4:H9)
と入力します。
このセルをピーして、下のセルI10とI16に貼り付けます。セルJ4には、順位を返す式を
=RANK(I4,$I$4:$I$21,1)
と入力します。
このセルをピーして、下のセルJ10とJ16に貼り付けます。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
それぞれの関数の説明は「関数の使い方説明」の
IF
ISERR
ROW
SMALL
を参照してください。
日付を文字列に置換えたいセルA1に
2004/04/17
と入力され、書式設定：表示形式：ユーザー定義で2004年04月ているとします。
別のセル(たとえばB1)に
=TEXT(A1,"yyyy年mm月")と入力します。
セルA1の書式設定を「標準」にします。
セルB1を選択し、「編集」「コピー」し、セルA1に「編集」「形式を選択して貼り付け」で「値」とします。
セルB1のデータは必要ありませんので削除します。
ローン計算のボーナス併用払いの計算添付ファイルを参照してください。
借入金の1,000,000のうち、ボーナスで500,000支払いますので、毎月の支払額は1,000,000-500,000で、500,000となります。
毎月の支払額と、ボーナスの支払額をそれぞれ別々に計算しています。
月ごと支払いは支払い回数60回
ボーナスは支払い回数が60回×1/6となります。
A B C 1 定期返済のボーナス併用払い 2 借入条件 3 借入金額 1,000,000 4 利率（年） 2% 5 返済回数 60 6 ボーナス支払額 500,000 7 支払月額 -8,764 500,000 8 ボーナス支払額 -52,791 500,000 セルB7には、
=PMT(B4/12,B5,B3-B6)
と入力します。
利率が年利ですから月にするために12で割っています。
また、借入金額からボーナス支払額を差し引いています。セルB8には、
=PMT(B4/2,B5/6,B6)
利率を年から6ヶ月にするために2で割っています。
また、支払い回数を６ヶ月に１回とするために、6で割っています。
数式5=2のA乗-AのAの値を求める「コールシーク」あるいは、「ソルバー」を使います。「ゴールシーク」の場合
A1セルにはAの値が返ります。
セルB1に数式
=2^A1-A1
と入力します。
数式の入力されているセルB1を選択し、「ツール」「ゴールシーク」を選択すると「ゴールシーク」ダイアログボックスが表示されます。
「数式入力セル」ボックスには先ほど選択しておいた数式の入力セルB1の参照が表示されています。
「目標値」ボックスには数式の結果としたい数値「5」を入力します。
「変化させるセル」ボックスで、ゴールシークの対象となるセルA1セルの参照「A1」を入力します。
あるいはマウスでセルを直接A1セルをクリックして指定します。
「ＯＫ」ボタンをクリックするかEnterキーを押すと「ゴールシーク」ダイアログボックスには、ゴールシークの結果が表示されます。
この値をA1セルに保存するには「ＯＫ」ボタンを押します。
Excelは、反復計算によってゴールシークを実行します。つまり「変化させるセル」ボックスで指定された変数セルに値を代入し、その結果と目標値とを比較します。
そして、数式の結果が目標値と一致するまで代入を続けるのです。ここで使用したべき乗の結果では正しい結果が得られないと思います。しかし、「精度」と「反復回数」の指定を変更すれば、ただしい結果が得られます。
デフォルトでは「ゴールシーク」は、反復計算の回数が100回を越えるか、計算結果の変動が0.001未満になるまで、計算を繰り返します。より高い精度が必要な場合は、このデフォルト値を変更してください。
これにはます、「ツール」「オプション」をクリックし、「計算方法」タブを表示します。次に、「最大反復回数」ボックスの値を100より大きくするか、「変化の最大値」を0.001よりも小さくしてください。ご質問の場合は「変化の最大値」を0.00001としてみてください。
また、ゴールシークで求められる解は1つだけです。「ソルバー」を利用する場合
より複雑な数式の解を求めるにはソルバーを利用します。
ソルバーは複数の変数を含む数式を対象として、目的のセルの値を最大あるいは最小にする組み合わせを求めます。また、ソルバーでは、計算で使用される値に一定の制限（制約条件式）を指定できます。
「ソルバー」はアドインとして提供されます。通常は利用できません。Excelのセットアップ時に降るインストールを指定すると、「ツール」メニューに「ソルバー」コマンドが表示されます。
ソルバーコマンドが表示されない場合は、「ツール」「アドイン」コマンドを選択し、「アドイン」ボックスの一覧から「ソルバーアドイン」チェックボックスをオンにしてください。
ソルバーを利用するには、「ツール」「ソルバー」をクリックします。「ソルバー:パラメータ設定」ダイアログボックスでは、「目的セル」「変化させるセル」、「制約条件」ボックスに3種類の情報を指定する必要があります。
ご質問の例では「目的セル」に、数式の入力されているB1セルを、「変化させるセル」ボックスには解となるA1セルを、制約条件には何も指定しません。
「ソルバー:パラメータ設定」ダイアログボックスのでの指定が終了したら「実行」ボタンをクリックしてください。目的のセルの値を最適化することに成功すると、「ソルバー:探索結果」が表示され、ワークシートには最適化された値が表示されます。この最適化された値を残すには「解を記入する」オプションボタンをクリックして「ＯＫ」ボタンを押します。
1から20までの重複しない乱数を20個のセルに返す見本のファイルを参照してください。一つのセルで正しく1から20までの重複しない乱数を返すのは無理です。
私の場合は、別のセルで1から20までの乱数を複数(380個)発生させて、その乱数が重複しているかどうかを別のセルでCOUNTIF関数で確認します。
さらに、重複していない場合に、重複していないセルの数を累計し(1から20までが返ります)ます。
E列で累計した数値(1から20まで)に対してVLOOKUP関数で、元のF列の乱数を返します。RANDBETWEEN関数を使うには、「ツール」「アドイン」から「分析ツール」を組み込んでください。
詳細はこちらを参照してください。セルF1には、1から20までの乱数を発生させます。
最小値はセルC1で、最大値はセルC2で指定しています。
=RANDBETWEEN($C$1,$C$2)
この式を行380までコピー貼り付けします。
（380行までとしているのは、乱数の発生で重複しているセルが多くなるのを見込んでいます。20*19=380）セルE1には、F列の値が重複していない場合そのセルの数を累計します。
実際にはD列のセルの値が1のセル数を、その数式の入力してある行のセル範囲まで加算します。=COUNTIF($D$1:D1,1)
と入力します。
この式を行380までコピー貼り付けします。
セルD1には、F列の値が重複しない場合は1を返します。
=COUNTIF($F$1:F1,F1)
と入力します。
重複しているデータがあれば2以上の数値が返ります。
この式を行380までコピー貼り付けします。セルB6には、1から20までの重複しない乱数を返します。
検索順序を行番号で決めています。
=VLOOKUP(ROW()-5,$E$1:$F$380,2,FALSE)
と入力します。
式の意味
セル範囲E1:E380の値が、行番号-5(この場合6行目ですから6-5で1)に等しい場合のF1:f380の該当行の値を返します。
この式を行25までコピー貼り付けします。
対象の値が無い場合は、エラー値#N/Aが返ります。
エラー値を返さないようにするには、数式を次のようにします。
=IF(ISERROR(VLOOKUP(ROW()-5,$E$1:$F$380,2,FALSE)),"",VLOOKUP(ROW()-5,$E$1:$F$380,2,FALSE))それぞれの関数の説明は
RANDBETWEEN
COUNTIF
ROW
VLOOKUP
ISERROR
を参照してください。
100.11という数字を小数点以下だけ抜き取って11だけ表示するFIND関数と、RIGHT、LEN関数を使います。
A1セルに100.11
と入力されているとします。
セルB1に11を表示したい場合は、
=RIGHT(A1,LEN(A1)-FIND(".",A1))
と入力します。
得られた数値は、文字列として返されます。
文字列ではなく、数値として計算できる用にするには、
=VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1)))
とします。
それぞれの関数の説明は
FIND文字列の検索
RIGHT右端からの指定数の文字の切り出し
LEN文字列の長さを返す
「関数の使い方説明」
の、「文字列関数」
http://www.katch.ne.jp/~kiyopon/kansuu/index.htm
の
http://www.katch.ne.jp/~kiyopon/kansuu/val.html#find
http://www.katch.ne.jp/~kiyopon/kansuu/val.html#right
http://www.katch.ne.jp/~kiyopon/kansuu/val.html#len
を参照してください。
計算により、入力に時間を要する「ツール」「オプション」「計算」タグで「計算方法」を「手動」にしてみてください。
再計算するには、{F9}ボタンを押します。
A1セルに入力した数値の累計をB1セルに返すマクロで作成しました。
対象のシートのセルA1に数値を入力すると、セルB1にその累計を返します。A1セルの値が100でB1セルの値が1000の場合は、B1セルに=1000+100の式が返され、1100が表示されます。
A1セルが数値以外の場合は、何も処理しません。下のマクロコードをコピーし、該当のシートタグをマウスの右ボタンで選択して「コードの表示」を選択します。
表示された「Microsoft VisualBasic」のシート右側の空白部分に貼り付けます。
「Microsoft VisualBasic」を終了して「エクセル」に戻るには「ファイル」「終了してMicrosoft Excelに戻る」を選択します。Private Sub Worksheet_Change(ByVal Target As Excel.Range)
r = Target.Row
c = Target.Column
'rは、行番号
'Cは、列番号
'この場合はA1セルですので、行1列1です。
'セルC5の場合は行番号5、列番号3ですから、53となります。
If r & c = 11 Then
G = Range("A1")
Range("B1") = "=" & Range("B1") & "+" & G
End if
End Sub
RANK関数で同じ順位がある場合、1233567とせずに、1234567にしたいCOUNTIF関数と組み合わせて、同じ順序の場合は、RANK関数に重複数-1を加算するようにします。
セルC1:C7にデータが入力されている場合。
A B C 1 =COUNTIF($B$1:B1,B1)-1+B1 =RANK(C1,$C$1:$C$7,1) 10 2 2 2 20 3 3 3 30 4 4 3 30 5 5 5 50 6 6 6 60 7 7 7 70
セルB1には、通常の順位を返します。
=RANK(C1,$C$1:$C$7,1)
と入力します。
このセルをコピーして、下のB7セルまで貼り付けます。リストの左端の列(この場合はA列)のセルA1には、重複数を加味した順位(この場合下の行が次の順位)を返します。
=COUNTIF($B$1:B1,B1)-1+B1
と入力します。
このセルをコピーして下のA7のセルに貼り付けます。
HLOOKUPの検索範囲が２つある場合検索値に対する検索範囲を抽出する為にもう一つ別のリストを作成します。
その表によって、検索値に対してどの参照範囲となるかをVLOOKUP関数で求め、そのセルのデータからINDIRECT関数でHLOOKUP関数の参照範囲にします。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
添付ファイルを参照してください。
hlookup2.xls
21kB
シート[表]の
セル範囲A20:B30には、
検索値に対する参照範囲が変化できるように、検索値に対する参照範囲のリストを作成します。
シート[Sheet1]の
セルC5には、検索値に対する参照範囲をかえします。
=VLOOKUP(B5,表!A20:B39,2,FALSE)
セルB6には、参照範囲が別のセル(C5)に入力されているので、INDIRECT関数で次のように数式とします。
=IF($B$5="","",HLOOKUP($B$5,INDIRECT("表!"&C5,TRUE),2,FALSE))
セルB7には、参照範囲が別のセル(C5)に入力されているので、INDIRECT関数で次のように数式とします。
=IF($B$5="","",HLOOKUP($B$5,INDIRECT("表!"&C5,TRUE),3,FALSE))
小数点以下の計算は誤差が出るこれは、浮動小数点に関する規格に則り、数値を2進数で格納していることから生じています。
この問題を解決する為には、小数点以下で計算させない事です。
0.13+0.16-0.24-0.06
=-0.009999999999999950000
の計算を、(0.13*100+0.16*100-0.24*100-0.06*100)/100
としてみてください。
=-0.010000000000000000000
となります。
マイクロソフトの解説もあります。
http://support.microsoft.com/default.aspx?scid=kb;ja;813530
複数行に入力されている同じ項目の複数のデータを１行に並べ替るINDIRECT関数と「配列数式」を使う方法
A B C 1 番号 年月日 2 A11111 H17.3.30 1 3 A11111 H17.12.31 2 4 A12111 H16.3.30 1 5 A12111 H17.3.30 2 6 A12111 H18.3.30 3 7 A13111 H16.12.31 1 8 A13111 H17.3.30 2 9 A13111 H17.12.30 3 10 A13111 H18.3.30 4
D E F G H I J 13 履歴１ 履歴２ 履歴３ 履歴４ 履歴５ 履歴６ 14 A11111 H17.3.30 H17.12.31 15 A12111 H16.3.30 H17.3.30 H18.3.30 16 A13111 H16.12.31 H17.3.30 H17.12.30 H18.3.30 17 18 19 20
セルC2には、A列の番号が同じものの数を上の行から累計する数式
=COUNTIF($A$2:A2,A2)
と入力します。
この式をコピーして下のセル範囲C3:C10まで貼り付けます。セルD14には、項目名の「番号」の重複しないデータを返す数式
=IF(ROW()-13>SUM(IF($C$2:$C$10=1,1)),"",INDIRECT("A"&SMALL(IF($C$2:$C$10=1,ROW($C$2:$C$10)),ROW()-13)))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
数式入力セルの行番号-13(この場合14-13=1)が、セル範囲$C$2:$C$10の値が1の場合、1を返しその合計数(重複しない「番号」の数です)よりも大きい場合は、何も返しません。
文字列"A"とセル範囲$C$2:$C$10の値が1の場合、そのセル範囲の行番号を返し、数式入力セルの行番号-13(この場合14-13=1)番目に小さい値を返し(この場合2)、文字列結合関数&で結合し、"A1"形式のセル参照式とします。
この場合セル"A2"への参照となります。
この式をコピーして下のセル範囲D25まで貼り付けます。E14には、セルD14の「番号」に該当するデータの日番目を返す数式
=IF(SUM(IF($A$2:$A$10=$D14,1))<COLUMN()-4,"",INDIRECT("B"&MIN(IF($A$2:$A$10=$D14,IF($C$2:$C$10=COLUMN()-4,ROW($C$2:$C$10))))))
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で{ }を入力してはいけません。数式の意味
セル範囲$A$2:$A$10の値が、セル$D14と同じ場合、1を返しその合計数(同じ番号のセル数です)が、数式入力背の列番号-4(この場合5-4=1)よりも小さい場合は、何も返しません。
文字列"B"とセル範囲$A$2:$A$10の値が、セル$D14と同じで、かつセル範囲$C$2:$C$10の値が、数式入力セルの列番号-4(この場合5-4=1)と同じ場合、セル範囲$C$2:$C$10の行番号を返し(この場合2)、文字列結合関数&で結合し、"A1"形式のセル参照式とします。
この場合セル"B2"への参照となります。
この式をコピーして、右のセル範囲F14:J14、下のセル範囲E15:J20まで貼り付けます。それぞれの関数の説明は「関数の使い方説明」
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
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
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
OR
http://kiyopon.sakura.ne.jp/kansuu/if.html#or
を参照してください。
別の方法
「エクセルで使えるソフト」に私の作成した「拡張VLookUp」ユーザー定義関数 を使います。
n番目に検索された行の指定列のデータを返す拡張VLookUp関数です。
エクセルに組み込みのVLOOKUP関数は、指定した範囲の左端列で検索し、発見されたセル同じ行の指定した列に入力されてている値を返す関数で、指定した列で最初に発見したデータしか返しませんが、この「拡張VLoopUp」関数は、n番目に見つかった行のデータを返します。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/vlookupx.htm
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
具体的には、添付ファイルを参照してください。
このファイルには拡張VLOOKUP関数を貼り付けてあります。
セルE14には、
=IF(COUNTIF($A$3:$A$11,$D14)<COLUMN()-COLUMN($D$14),"",VLookUpX($D14,$A$3:$B$11,COLUMN()-COLUMN($D$14),2))
と入力します。
数式の意味
セル範囲$A$3:$A$11の値がセル$D14と同じセル数合計(この場合2)が、数式入力セル(この場合5)の列番号-セル$D$14の列番号(この場合4ですから5-4=1)より小さい場合は、何も返しません。
セル$D14を検索値として、セル範囲$A$3:$B$11の左端列(この場合A列)を検索して、数式入力セルの列番号(この場合5)-セル$D$14の列番号(この場合4ですから5-4=1)番目の検索された行の、2列目(この場合B列)の値を返します。このセルをコピーして右のセル範囲E14:J16まで貼り付けます。
A1セルに7:00,B1セルに180(分)で合計をC1セルに10:00と返したい時刻シリアル値を説明します。
1日は1で、24時間です。
1時間は1/24です。
1分は1/24/60です。
したがって、分をシリアル値に直すには1/24/60をかければいいですね。
C1セルには
=A1+B1/24/60
と入力します。
C1セルには、A1セルの時刻シリアル値にB1セルの分を時刻シリアル値に直した値を加算して返します。
C1セルの書式設定は「時刻」「13:30」にします。
データテーブルの使用方法について
A B C D 1 借入金返済の試算 支払額 2 頭金 なし \-22,222 3 利率 9.00% \-64,370 4 期間（月） 360 9.25% \-65,814 5 借入金 8,000,000 9.50% \-67,268
セルD2には、
=PMT(C1/12,B4,B5)
と入力します。C1/12としているのは利率を月利にするためで、B4は返済回数（月数）の入力セル、B5は借入金額が入力されているセル参照です。
この数式はC1セルを参照していますが、C1セルは現在空白となっています（Ｅｘｃｅｌでは、空白セルの参照は0として扱われます）。そのため、数式は現在利率0%
として、毎月の返済額を計算しています。
このC1には、Excelによって代入値が順次自動的に代入されます。ただし、代入といっても実際似せるに数値が入力されるわけではないので、テーブル範囲以外の
セルならC1でなくてもかまいません。
データテーブル範囲を選択します。データテーブル範囲とは、代入値と数式を含んだ必要最小限の四角形の範囲です。ここでは、C2:D5がデータテーブル範囲とな
ります。
「データ」「テーブル」をクリックします。「テーブル」ダイアログボックスが表示されたら、「行の代入」ボックスまたは、「列の代入」ボックスに代入セル
を指定します。代入セルとは、数式が参照しているセルのことで、ここではC1が代入セルです。この例では、代入値は列方向のセル範囲に入力されているので、
代入セルは「列の代入セル」ボックスに指定します。そのためには、テキストボックスをクリックして、代入セルの絶対参照$C$1入力するか、あるいはセルC1を
直接クリックします。
「ＯＫ」ボタンをクリックすると、各代入値の代入結果がデータテーブル範囲に表示されます。
この例では、セル範囲D3:D5に３つの結果が表示されます。
データテーブルの作成を実行すると、データテーブルのD3:D5（この範囲を「結果範囲」と呼びます）には、数式{=TABLE(,C1)}が入力されます。中かっこで囲まれ
ていることから分かるように、これは配列数式です。このTABLE関数が、C列の利率を使ってPMT関数の結果を求めています。
テーブルの作成後に、数式や代入値を変更することも可能です。
TABLE関数は、内部関数です。したがって「関数の挿入」ダイアログボックスまたは、入力できないことを意味しています。
二捨三入、７捨八入A1セルに数値が入力してある場合、次のような式となります。
長いですが、１つの式です。
=IF(OR(AND(VALUE(RIGHT(A1,1))>=3,VALUE(RIGHT(A1,1))<=4),AND(VALUE(RIGHT(A1,1))>=8,VALUE(RIGHT(A1,1))<=9)),CEILING(A1,5),FLOOR(A1,5))また、「エクセルで使えるソフト」http://kiyopon.sakura.ne.jp/soft/index.htmに
「二捨三入、七捨八入」ユーザー定義関数があります。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/round2378.htm注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
をお読み必ずください。
ファイルをコピーしてできた別のブックへのリンクを自分のブックへのリンクに戻すおそらく新たに作成したブックへのリンクが設定されたのでしょう。
別のブックへのリンクを解除すればよいとと思います。
どのような参照をされているのかわかりませんが、次のように別のブックへのリンクを検索してみて下さい。
「編集」「検索」で、「検索する文字列」に[と入力し「次を検索」ボタンを押します。
[ブック名.xls]が検索されたら、その[ブック名.xls]を「検索する文字列」に入力します。
「置き換え」ボタンを押し「全て置き換え」ボタンを押します。
これで、別のブックへのリンクが無くなり、自分のブックへのリンクに切り替わります。
ＳＩＮα＝０．２３４の場合はαの度数をどうやって求めますか三角関数ですね。ASIN（アークサイン）関数を使います。
角度がラジアンで求められますので、度にする場合は、計算結果に180/PI()を掛けます。この場合は、=ASIN(0.234)*180/PI()
で、13.53268354度
となります。
並べ替えによるグループの平均値のずれを防止するには方法が２つあります。
A B C D E F 1 グループ 金額 A B C 2 400 700 300 3 B 800 4 B 600 5 A 500 6 C 400 7 A 400 8 A 300 9 C 200
セルD1,E1,F1にはそれぞれの条件ここではグループ名を入力します。1つ目
SUMIF関数とCOUNTIF関数を使う方法。
セルD2には、グループごとの平均値が返る式
=SUMIF($A$3:$A$9,D$1,$B$3:$B$9)/COUNTIF($A$3:$A$9,D$1)
と入力します。
このセルをコピーして、右のセルE2,F2に貼り付けます。2つ目
「配列数式」を使う方法。
セルD2に、
=AVERAGE(IF($A$3:$A$9=D$1,$B$3:$B$9))
と入力し、{Shift}+{Ctrl}{+ENter}で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
{=AVERAGE(IF($A$3:$A$9=D$1,$B$3:$B$9))}
自分で{ }を入力してはいけません。
このセルをコピーし、右のセルE2,F2に貼り付けます。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
sumif関数とcountif関数の説明は
「関数の使い方説明」
http://www.katch.ne.jp/~kiyopon/kansu/index.htm
の「数学+三角」
http://www.katch.ne.jp/~kiyopon/kansuu/abs.html#sumif
http://www.katch.ne.jp/~kiyopon/kansuu/abs.html#countifにあります。
「配列数式」の説明は、「質問と回答」
http://www.katch.ne.jp/~kiyopon/situmon/index.htm
「計算式・関数」「2002/3/23」
「複数の検索条件に合致するセルをカウントする(配列数式)」
にあります。
数値を漢数字で表示したい２つの表示方法があります。
入力したセルにそのまま漢数字として表示する「書式」「セル」で、「セルの書式設定」ダイアログボックスから「表示形式」の「分類」で「ユーザー定義」を選択し、
一二三四五六七八九〇
と、表示する場合は、[DBNum1]#
壱弐参四伍六七八九〇
と、表示する場合は、[DBNum2]#
と入力します。
この場合、数値はそのまま計算に使えます。
入力セルとは別のセルに、漢数字として表示する。A1セルに数値が1234567890と入力されているとします。
B1セルに
=NUMBERSTRING(A1,1)
で、十二億三千四百五十六万七千八百九十
が返ります。
=NUMBERSTRING(A1,2)
で、壱拾弐億参阡四百伍拾六萬七阡八百九拾
が返ります。
=NUMBERSTRING(A1,3)
で、一二三四五六七八九〇
が返ります。
また、ユーザー定義関数「漢数字に変換」
http://www.katch.ne.jp/~kiyopon/soft/kansuji.htm
で、アラビア数字の0,1,2,3,4,5,6,7,8,9を漢数字に変換し、〇,壱,弐,参,四,五,六,七,八,九を返します。
数値以外の文字はそのまま返します。
注意
ここに掲載されているエクセルのブックは「マクロ」が記述されています。
このファイルを開く場合は、「マクロを有効にする」をチェツクして実行してください。
http://www.katch.ne.jp/~kiyopon/soft/addin.html
数字123を0000123などに変換する
入力数値を、先頭桁から0で補って、7桁にする方法です。２つの方法があります。
１つ目は、
入力数値はそのまま123で、表示のみ0000123とします。
A1セルに
123
と入力されているとします。
A1セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで、「表示形式」タグの「分類」で「ユーザー定義」を選択し、「種類」欄に「0000000」と0を7個入力し、「ＯＫ」ボタンを押します。
セルA1は数値として認識されます。2つ目は、
A1セルとは別のセルに文字列として、0000123と表示します。
B1セルに
=TEXT(A1,"0000000")
と入力します。
B1セルには、0000123と表示されます。
セルB1は文字列として認識されます。
ただし、計算にB1セルを使用すると、そのデータを数値として認識し、123として計算できます。
セル参照を列が変わっても、前の列の最終行の続きで参照式の行を増加したい手作業になりますが、次のようになります。
簡単にする方法は、参照式を該当する列に作制すのではなく、同じ列に必要な行分のすべての式を連続コピーで作成します。次に、作成した式を表示したいセル範囲に移動させます。
A B C D E F 1 12/27 =A1 =A5 2 12/28 =A2 =A6 3 12/29 =A3 =A7 4 12/30 =A4 =A8 5 12/31 =A5 6 1/1 =A6 7 1/2 =A7 8 1/3 =A8 9 1/4 =A9 10 1/5 =A10 11 1/6 =A11 12 1/7 =A12 13 1/8 =A13 14 1/9 =A14 15 1/10 =A15 16 1/11 =A16
具体的には、セル範囲A1:A16への参照式をC1:F4
セルC1に=A1と入力します。
この式を下のセルA16まで、オートフィル機能で連続コピーします。
セル参照式としてC列に作成されます。
参照式の列を移動したいセル範囲A5:A8を選択し、セル範囲の境界にマウスポインタを持って、ポインタが矢印に変わったら移動先のセルD1にドラッグします。
同様にセル範囲A9:A12をセルE1にドラッグ
セル範囲A13:A16をセルF1にドラッグします。
数式を修正せずに、正しい参照のままでセルの参照が移動されます。
「成績一覧表から個票を作成する」のをクラス全員とか任意の数名にしたい１つのデータでなく複数のデータを検索して表示したい場合は、「データ」「フィルタオプションの設定」を使います。
「フィルタオプション」の使い方説明は、「エクセルの操作説明」の、「データベース」を参照してください。
ただし、通常は別のシートにデータの検索結果を表示できませんので、検索結果を別のシートに表示するために、別のシートから検索結果のセルへの参照式を入力しておく必要があります。
見本のファイルを添付しますので、参考にしてください。
kensaku1.xls 30kB
姓と名前の間に入れてしまったスペースを一括して消す「置換」を使います。
手順
１ 氏名の入力されているセル範囲を選択します。
２ 「編集」「置換」を選択し、「検索文字列」に" "(スペース)を入力します。
３ 「置き換え後の文字列」に何も入力しないで、「全て置き換え」ボタンを押します。スペースが半角の場合や全角の場合がありますので、「検索文字列」に半角スペース" "または、全角スペース" "を入れて２度実行してください。
PHONETIC関数で返った姓と名のスペースを削除したいPHONETIC関数で返った、ふりがなの間にあるスペースを削除したいのですね。
SUBSTITUTE関数を使います。
具体的には、セルA1に"岡安 清隆"
と、姓と名の間に全角スペースの入った氏名があるとします。=PHONETIC(A1)
では、
"おかやす きよたか"が返りますが、
SUBSTITUTE関数を使って
=SUBSTITUTE(PHONETIC(A1)," ",,1)
"おかやすきよたか"
が返ります。もし、半角のスペースも含まれているのであれば、次のような関数にします。
=SUBSTITUTE(SUBSTITUTE(PHONETIC(A1)," ",,1)," ",,1)
全角スペースと、半角スペースのどちらも削除されます。
メーターの数値が一周して0に戻った時の使用量の計算式
A B 1 メーター 使用量 2 952 3 980 28 4 6 26
この場合次桁が1000ですから、セルB3には
=A3+IF(A3-A2<0,1000,0)-A2
と入力します。
差し引きの値が、マイナスになった場合は、該当月のデータに1000を加算します。
この式をツ下のセルにコピー、貼り付けします。
時間の差し引きの結果で同じ7分36秒でも計算結果が違うこれは、計算結果の数値が割り切れないために生ずる誤差までを比較しているためです。B2セルとA2セルに時間が入力されているとします。
33分20秒-40分56秒=7分36秒
計算結果の時刻シリアル値
0.0052777777777777734分10秒-41分46秒=7分36秒
計算結果の時刻シリアル値
0.00527777777777778秒の単位では、時刻シリアル値の小数点以下6桁まであれば正しい計算になりま
すので、差し引き計算を次のようにします。
=ROUND(B2-A2,6)
フリガナの頭文字１字だけを条件として生年月日順に並べ替えたいこの場合は、ふりがなの1文字目を抽出した列を追加する必要があります。
具体的には、
A B C 1 氏名 生年月日 氏名１文字 2 伊藤博文 1925/3/5 イ 3 岩倉具視 1913/5/8 イ 4 徳川慶喜 1812/7/1 ト 5 福沢諭吉 1845/6/10 フ
A列に氏名
B列に生年月日
が入力されているとします。
C列を追加し、C2セルに
=LEFT(PHONETIC(A2),1)
と入力し、氏名の1文字目を取り出します。
データのいずれかのセルを選択し、
「データ」「並べ替え」を選択し、「最優先されるキー」に「氏名１文字」のC列、「2番目に優先されキー」に「生年月日」のB列を選択し、「ＯＫ」ボタンを押します。
時間帯によって時給の額が違う場合の計算法時間帯毎の時給を別のセル表で作成しておきます。
そのセル表から時間帯に対応する時間を算出します。
見本のファイルを参照してください。kinmu1.xls
14.5kB
最大値・最小値の行番号または列番号が知りたい「配列数式」と、行番号の場合はROW関数、列番号の場合はCOLUMN関数を使います。具体的には、セル範囲A1:A100にデータが入力されているとしますと、
最大値の入力されているセルの行番号は、
=MIN(IF(MAX($A$1:$A$100)=$A$1:$A$100,ROW($A$1:$A$100)))
と入力し、{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で { }を入力してはいけません。
同様に、最小値の入力されている行番号は、
=MIN(IF(MIN($A$1:$A$100)=$A$1:$A$100,ROW($A$1:$A$100)))
と入力し、{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
確定後、数式は、{ }でくくられます。
自分で { }を入力してはいけません。
5-195で-以降の数値のけた数を1000の位にそろえるIF関数
LEN関数
FIND関数
文字列結合関数(&)を使います
A B C D 1 A 5-195 5-1950 2 B 4-1500 3 C 3-1800 4 D 4-2100 5 E 3-1350 6 F
セルC2に5-195
と入力されている場合
セルD2に
=IF(C2="","",IF(LEN(C2)-FIND("-",C2)<4,C2&0,C2))
と入力します。
数式の意味
セルC2が空白の場合は何も表示しません。
セルC2の文字数から"-"の文字位置を引いた値(この場合3)が4より小さい場合はセルC2の文字にに0を結合します。
それ以外はセルC2のデータをそのまま返します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
それぞれの関数の説明は「関数の使い方説明」の「論理関数」の
IF
「文字列関数」の
LEN
FIND
&
を参照してください。
10から20までの間の数字をランダムに表示したい（0.5単位ずつ）RANDBETWEEN関数とFLOOR関数を使います。
=FLOOR(RANDBETWEEN(100,200),5)/10RANDBETWEEN(100,200)
で100以上200以下の整数の乱数を発生させます。FLOOR(数値,5)
関数で、数値を5刻みの倍数で切り捨てます。
128場合、125となります
その値を10で割れば、0.5刻みの乱数になります。
12.5となります。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
RANDBETWEEN関数
（指定された範囲の乱数を発生させます）
RANDBETWEEN関数を使うには、分析ツールをインストールしておく必要があります。
分析ツールは「ツール」「アドイン」で［分析ツール］をチェツクし［ＯＫ］とします。
書式 =RANDBETWEEN(最小値、最大値）
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
FLOOR関数
(数値を挟む基準値の倍数のうち、0 に近い方の値を返します)
書式 =FLOOR(数値, 基準値)
数値 丸める数値を指定します。
基準値 倍数の基準となる数値を指定します。
生年月日より喜寿・米寿年を求めるにはA1セルに生年月日が
1960/5/6
のように入力されているとします。喜寿は、77歳ですから
=IF(A1="","",YEAR(A1)+77)
2037
が返ります。
米寿は、88ですから
=IF(A1="","",YEAR(A1)+88)
2048
が返ります。
10の位が50以上の場合は50、50以下の場合は0に丸めたいFLOOR関数を使います。
この場合はA1*B1*C1の計算結果の数値を50で丸めます。
具体的には、
A B C D E 1 15000 26 0.004 1560 1550 2 15000 14 0.004 840 800
セルE1に
=FLOOR(A1*B1*C1,50)
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
FLOOR関数の説明は「関数の使い方説明」の「丸め」「FLOORとCEILING」を参照してください。
同じ日付のデータを同じ行に並べる簡単にできそうな気がしますが、非常に難しいです。
手作業で関数などを使えばできますが、面倒です。
A B C D 1 日付 データ 日付 データ 2 10月1日 あああ 10月1日 かかか 3 10月2日 いいい 10月2日 ききき 4 10月5日 ううう 10月4日 くくく 5 10月6日 えええ 10月5日 けけけ
まず、セル範囲C2:D5を選択して、「編集」「切り取り」、セルA6を選択して、「編集」「貼り付け」します。
A B 1 日付 データ 2 10月1日 あああ 3 10月2日 いいい 4 10月5日 ううう 5 10月6日 えええ 6 10月1日 かかか 7 10月2日 ききき 8 10月4日 くくく 9 10月5日 けけけ
セル範囲A2:B9のいずれかのセルを選択して、「編集」「並べ替え」を選択し、「最優先されるキー」に「日付」列を選択し、「昇順」を選択し、「ＯＫ」ボタンを押します。
A B 1 日付 データ 2 10月1日 あああ 3 10月1日 かかか 4 10月2日 いいい 5 10月2日 ききき 6 10月4日 くくく 7 10月5日 ううう 8 10月5日 けけけ 9 10月6日 えええ
列Bを選択し、「挿入」「列」とし、セルB2に
=A2+(COUNTIF($A$2:A2,A2)-1)/24
と入力し、この式を「編集」「コピー」して、下のセルに「編集」「貼り付けします。
この式で、同じ日付の24個までの日付けに小数点以下の順番を付けます。
A B C 1 日付 データ 2 10月1日 10月1日 あああ 3 10月1日 10月1日 かかか 4 10月2日 10月2日 いいい 5 10月2日 10月2日 ききき 6 10月4日 10月4日 くくく 7 10月5日 10月5日 ううう 8 10月5日 10月5日 けけけ 9 10月6日 10月6日 えええ
セルD2に
=IF($A1=$A2,"",IF(ISNA(VLOOKUP($A2+(COLUMN()-3)/24,$B$2:$C$9,2,FALSE)),"",VLOOKUP($A2+(COLUMN()-3)/24,$B$2:$C$9,2,FALSE)))
と入力し、「編集」「コピー」し、このセルを下のセルと右のセルに「編集」「貼り付け」します。
A B C D 1 日付 データ 2 10月1日 10月1日 あああ かかか 3 10月1日 10月1日 かかか 4 10月2日 10月2日 いいい ききき 5 10月2日 10月2日 ききき 6 10月4日 10月4日 くくく 7 10月5日 10月5日 ううう けけけ 8 10月5日 10月5日 けけけ 9 10月6日 10月6日 えええ
この式の意味
セルA1(前の行の日付)とA2(この行の日付)が同じなら何も表示しません。
セルA2+(列番号-3)/24のデータをセル範囲B2:C9から検索し、同じ場合は、そのセル範囲(B2:C9)の2番目の列(C列)のデータを返します。
抽出結果を値として貼り付けるために、セル範囲C:AEを選択し、「編集」「コピー」し「編集」「形式を選択して貼り付け」を選択し、「値」を選択して「ＯＫ」ボタンを押します。
重複データの行を抽出するために
セルB2に
=COUNTIF($A$2:A2,A2)-1
と入力します。
この式を「編集」「コピー」し下のセルに「編集」「貼り付け」します。
重複行は1月1日以上の日付（または、1以上の数値）になっていますので、「データ」「フィルタ」「オートファイルタ」を選択し、「1月1日以上(または、1以上の数値)」のデータを抽出します。
抽出された1月1日(または、1以上の数値)の行番号を選択し、「編集」「削除」を選択します。
「データ」「フィルタ」「オートフィルタ」のチェックを外し、フィルタ機能を解除します。
列Bを選択し、「編集」「削除」します。
空白、文字列、0（数値）を除いたセル数を返す空白、文字列、0（数値）を除いたセル数を返す場合は、
=COUNT(IF(A1:A8<>0,A1:A8))
と入力し、配列数式として、{Shift}+{Ctrl}+{Enter}で確定します。
配列数式は、確定後数式が{ }でくくられます。
{=COUNT(IF(A1:A8<>0,A1:A8))}
{ }を自分で入力してはいけません。
数字を入力して該当するランクや文字を返す具体的な表が分かりませんが、例として次のようなものを参考にしてください。VLOOKUP関数を使います。
セルA1:B11に区分の数字を入力します。
実際には、B列の数値は必要ありませんが、見た目にわかりやすいように、入力しています。
C列に該当のランクの文字を入力します。
（この場合は数字です）
セルB13には、得点を入力します。
A B C 1 以上 未満 ランク 2 0 10 1 3 10 20 2 4 20 30 3 5 30 40 4 6 40 50 5 7 50 60 6 8 60 70 7 9 70 80 8 10 80 90 9 11 90 100 10 12 13 得点 76 8
セルC13には
=VLOOKUP(B13,$A$2:$C$11,3,TRUE)
と入力します。この式の意味
セルB13の値が、セル範囲A2:C11の左端列(この場合はA列)の値を検索して、その値以上次の値未満の該当するセル範囲の3列目（この場合はC列）の値を返します。
数式が10行毎のデータ(1,11,21,31行目)を返すようにコピーしたいINDIRECT関数と、ROW関数を使います。
D1の数式を下にコピーしてC列の1,11,21,31のデータを返す場合
C D 1 1 1 2 2 11 3 3 21 4 4 31
セルD1には、
=INDIRECT("$C"&ROW()*10+1-10,TRUE)
と入力し、下のセルにコピ貼り付けします。INDIRECT関数についての説明は、
「関数の使い方説明」
の「検索+行列」「INDIRECT」にあります。
ROW関数の説明については、
「検索+行列」「ROW」にあります。
参考にしてください。
月の１５日までは前月を返すEOMONTH関数を使いますが、この関数を使うには、「ツール」「アドイン」で、「分析ツール」をチェックして、「ＯＫ」ボタンを押して、「分析ツール」を組み込む必要があります。具体的な関数は、次のようになります。
A1セルに日付が日付シリアル値として入力されています。
A1セルの書式は、「書式」「セルを選択し、「表示形式」タグの「分類」で「日付」「1997/3/4」としています。
B1セルの書式は、「書式」「セルを選択し、「表示形式」タグの「分類」で「標準」としています。A B
1 2003/10/16 10=MONTH(EOMONTH(A1,IF(DAY(A1)<=15,-1,0)))
関数の=の前にあるこの { は何配列数式の記号です。「{Ctrl}+{Shift}+{Enter}で「配列数式」として確定します。」
配列数式は{ } でくくられます。
見本となる質問が「質問と回答」
の「計算式・関数」「002/3/23」「複数の検索条件に合致するセルをカウントする(配列数式)」にあります。
Aに漢字にて名前を入力し、セルのBに自動的にふりがな表示するPHONETIC関数で、文字列から「ふりがな」を抽出します。
具体的には、A1セルに入力した漢字のふりがなをB1セルに表示したい場合は、
B1セルに
=PHONETIC(A1)
と入力します。
Xの12乗が56.8となる場合のXの値を求める数式同じような質問が「質問と回答」の「計算式・関数」「2001/6/15」「n乗根、立方根を返すには」にあります。
その内容
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
べき乗の関数をうまく使います。どちらでも同じ意味です。
A1セルの立方根を返す
=A1^(1/3)
=POWER(A1,1/3)n乗根を返す
=A1^(1/n)
=POWER(A1,1/n)
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
ご質問の場合は
=56.8^(1/12)
となります。
数値を関数で簡単にグラフ化する方法文字列を指定回数だけ繰り返して表示する関数
REPT関数を使います。
書式
REPT(文字列,繰り返し回数)具体的には、
セルA1に10と入力した時にB1に*を10個表示させるには、
B1セルに
=REPT("*",A1)
と入力します。
A B 1 10 **********
簡易グラフを作成するには、B1セルの幅を広げておく必要があります。
休憩時間を含む場合と24:00以降の勤務時間の計算方法時間の計算は、通常の四則演算が可能です。
ただし、24時以降の時刻を正しく計算させるためには、1:00を25:00のように入力します。具体的には、
A B C D 1 休憩開始 休憩終了 2 12:30 13:30 3 19:15 19:45 4 23:45 24:45 5 開始時間 終了時間 勤務時間 6 8:30 19:30 9:45
まず、24時以上の時刻も正しく表示できるように時刻を表示するセル範囲A2:E2を選択し、「書式」「セル」を選択し、セルの書 式設定ダイアログボックスの「表示形式」の「分類」で、「ユーザー定義」を選択し、「種類」欄に[h]:mmと入力し「ＯＫ」ボタンを押します。
セルD4の時刻の入力は、24時を越えていますので、0:45ではなく、24:45と入力します。
セルD6に勤務時間を返します。
=(C6-B6)-IF($B6<$C$2,IF($C6<$C$2,0,IF($C6>$D$2,$D$2-$C$2,$C6-$C$2)),IF($B6<$D$2,IF($C6>$D$2,$D$2-$B6,0),0))-IF($B6<$C$3,IF($C6<$C$3,0,IF($C6>$D$3,$D$3-$C$3,$C6-$C$3)),IF($B6<$D$3,IF($C6>$D$3,$D$3-$B6,0),0))-IF($B6<$C$4,IF($C6<$C$4,0,IF($C6>$D$4,$D$4-$C$4,$C6-$C$4)),IF($B6<$D$4,IF($C6>$D$4,$D$4-$B6,0),0))
また、1ヵ月分の合計は、計としてセルE5に
=SUM(D6:D39)と入力します。
このセルの書式設定も、「ユーザー定義」を選択し、「種類」欄に[h]:mmと入力し「ＯＫ」ボタンを押します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
また、時間を15分単位などで切り上げ（あるいは切り捨て）する場合「質問と回答」の「計算式・関数」「2003/7/4」
「時間を１５分単位で丸めて切り上げ、切り捨て」を参照してください。
「○月」までと指定するとその月までの集計結果が表示されるようにしたい
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−〔配列数式〕を使います。
セルB1:H1までの4月などは、数値として認識されるように4,5,6,7．．．のみ入力します。
セル範囲B1:H1を選択し、〔書式〕〔セル〕を選択し、〔セルの書式設定〕ダイアログボックスの「表示形式」の〔分類〕で〔ユーザー定義〕を選択し、〔種類〕欄に#"月"と入力します。
同様にセルB12を選択して、「書式」を「ユーザー定義」の#"月"と入力します。
セルB12には、検索条件となる月の7を入力します。
A B C D E F G H 1 項目/月 4月 5月 6月 7月 8月 9月 10月 2 A 123 123 128 127 129 150 3 B 456 456 789 4 C 5 D 6 E 7 F 8 G 9 10 11 12 項目/月 7月 13 A 501 14 B 1701 15 C 0 16 D 0 17 E 0 18 F 0 19 G 0 20 H 0
セルB13に
=SUM(IF($B$1:H1<=B$12,B2:H2))
と入力し、{Ctrl}+{Shift}+{Enter}で、配列数式として確定します。
配列数式は確定後{ }でくくられます。
{=SUM(IF($B$1:H1<=B$12,B2:H2))}
自分で{ }を入力してはいけません。
このセルの式をコピーして、下のセルA20まで貼り付けます。
条件に該当するセルの項目名を返す(配列数式)「配列数式」と「COLUMN」関数「INDIRECT」関数を使います。
A B C D E F G H 1 Jan-99 Feb-99 Mar-99 Apr-99 May-99 最高金額 時期 2 店１ 1,488 1,355 2,665 1,849 1,557 2,665 Mar-99 3 店２ 4,574 3,691 4,106 4,811 5,374 5,374 May-99 4 店３ 1,473 1,302 2,329 1,713 1,700 2,329 Mar-99
セルH2には、
=INDIRECT("R1C"&SUM(IF(B2:F2=G2,COLUMN(B2:F2))),FALSE)
と入力し、{Shift}を押しながら{Ctrl}を押しながら{Enter}キーを押して「配列数式」として確定します。
確定後数式は、{ }でくくられます。
{=INDIRECT("R1C"&SUM(IF(B2:F2=G2,COLUMN(B2:F2))),FALSE)}
自分で{ } を入力してはいけません。
この式をしたのセルにコピー貼り付けします。
「配列数式」でセル範囲B2:F2の値が、セルG2と等しいセルの列番号(D列ですから4)をCOLUMN関数で返し、INDIRECT関数で、"R1C"と文字列結合関数(&)で連結して参照式R1C4となります。
セルには、参照先のセルD1(R1C4)の値が返ります。
COLUMN関数については、こちらを参照してください。
INDIRECT関数については、こちらを参照してください。
郵便番号のハイフンを取る方法「置換」を使う方法
郵便番号の入力されているセル範囲を選択し、「編集」「置換」を選択し、「検索する文字列」に"-"と入力し、「置換後の文字列」に何も入力せずに「すべて置
き換え」ボタンを押します。
変換後に、セル範囲を選択し、「セルの書式設定」で「ユーザー定義」0000000と0を7つ入力し、「ＯＫ」ボタンを押します。
７桁に満たない部分の数値は、先頭に０が補われます。SUBSTITUTE関数で文字列"-"を""に置き換える方法
A1セルに郵便番号が
078-1653
と、入力されているとします。
=SUBSTITUTE(A1,"-","")
で、文字列として
0781653
がかえります。
重複データを含まない正味の入場者数の集計参考になる質問が「質問と回答」の
「計算式・関数」「2002/9/14」「重複データに印を付ける重複データの２つ目以降に印を付ける」にあります。
A B C D E F 1 2 来場者一覧表 平成１５年９月 １３・１４ 3 4 Ｎｏ． 氏 名 住 所 5 1 青木 繁 1 6 2 井上 明 1 7 3 宇野 宗佑 1 8 4 江川 卓 1 9 5 大川 栄作 1 10 6 加藤 善男 1 11 7 木村 庄之助 1 12 8 久野 優 1 13 9 華厳 巌 1 14 10 小山 昭信 1 15 11 佐藤 延雄 1 16 12 塩田 丸雄 1 17 13 鈴木 浩 1 18 14 瀬川 英子 1 19 15 園田 広雄 1 20 16 青木 繁 0 21 17 井上 明 0 22 18 宇野 宗佑 0 23 19 0 24 20 0
セルF5に
重複データの２件目以降は０を返し、１件目は１を返す式
=IF(C5=0,0,IF(COUNTIF($C$5:C5,C5)>1,0,1))
を入力し、{Ctrl}+{Shift}+{Enter}で、配列数式として確定します。
確定後、数式は、{ }でくくられます。この式をセルF24までコピーします。
この数値1が、１回目の来場者ですから、
=SUM(F5:F24)
で、正味の来場者数が返ります。
入力値の間違は、赤色で"ＮＧ"、正解ならば、青色で"ＯＫ"を表示A列に回答を入力します。
B列に判定を正しければ、青でＯＫ、違っていれば、赤でＮＧを返します。
C列に正解の答えを入力します。
A B C 1 回答 判定 正解 2 東京 ＯＫ 東京 3 広島 ＮＧ 名古屋
セルB2に判定の式
=IF(A2="","",IF(A2=C2,"ＯＫ","ＮＧ"))
と入力します。
この式をセルB3以降の行にコピー貼り付けします。
セルB2以降のセル範囲を選択し、「書式」「条件付き書式の設定」を選択します。
「条件１」に「数式が」を選択し、
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
=$A2=$C2
と入力し、「書式」ボタンを押して「フォント」の「色」を青にし「ＯＫ」ボタンを押します。
「追加」ボタンを押して、「条件2」に「数式が」を選択し、
=$A2<>$C2
と入力し、「書式」ボタンを押して「フォント」の「色」を赤にし「ＯＫ」ボタンを押します。
「条件付き書式の設定」ダイアログボックスの「ＯＫ」ボタンを押します。
４桁の数値は３桁以降を切り捨て表示する有効桁数を指定桁数にするには、次のような関数になります。
=RoundDown(数値, -(Len(Abs(Int(数値))) - 桁数))A1セルの数値を上位3桁にするには
=RoundDown(A1, -(Len(Abs(Int(A1))) - 3))
となります。さらに、1000円未満は十円単位、十円未満の場合は一円単位とするには、
=RoundDown(A1, -(Len(Abs(Int(A1))) - 3+IF(A1<10,2,if(A1<1000,1,0))))
となります。参考に、有効桁数を任意の桁数に指定できる「指定桁数」ユーザー定義関数があります。
123456を最下位セルを指定して、セル毎に1,2,3,4,5,6と表示する
A B C D E F G H 1 123456 1 2 3 4 5 6 2 78901 7 8 9 0 1
セルG1を一位として表示する場合は、
セルB1に次のように入力します。
=IF(LEN($A1)-COLUMN($G1)+COLUMN()<=0,"",MID($A1,LEN($A1)-COLUMN($G1)+COLUMN(),1))
この式をセルB1tからG1までコピーします。
リストから0を基点に+0に近い小さな数字と、-0に近い小さな数字を抽出配列数式を使います。
セルA1:A10に数値が入力されているとします。
A B C 1 10.6 -2.2 1.4 2 -34.9 3 66.3 4 28.1 5 -2.2 6 45.4 7 35.5 8 -12.7 9 1.4 10 103.2
セルB1には、0より小さいマイナスの数値を返す式
=MAX(IF(A1:A10<0,A1:A10))
と入力し、{Ctrl}+{Shift}+{Enter}で配列数式として確定します。
配列数式は、確定後{ } でくくられます。
自分で{ }を入力してはいけません。セルB1には、0より大きいマイナスの数値を返す式
=MIN(IF(A1:A10>0,A1:A10))
と入力し、{Ctrl}+{Shift}+{Enter}で配列数式として確定します。
配列数式は、確定後{ } でくくられます。
自分で{ }を入力してはいけません。
リストの中から、検索条件に合致する別の列の値を返すセル範囲C12:C15の中で、セルC17に合致するデータのA列の値を返すものとします。A B C
12 2 12
13 5 46
14 7 35
15 9 32
16
17 MAX 46 答え 5=SUM(IF(C17=C12:C15,A12:A15))
と入力し、{Ctrl}+{Shift}+{Enter}で、「配列数式」として確定します。
確定後、数式は{ } でくくられます。
{ } を自分で入力してはいけません。
datedif関数で、開始日より終了日が大きい場合、マイナス日数を表示したいこの場合は、DATEDIF関数を使わずに単なる加減乗除の式を使います。
日付は、日付シリアル値で１日が1ですから、単に引き算ができます。
IF関数を使い、開始日が終了日より大きい場合計算結果をマイナスとします。
A B C
1 開始日 2003/8/7 -7
2 終了日 2003/7/31=IF(B1>B2,B2-B1,-B1-B2)
セルA1に-12345と入力し、セルB1に▲,C1に1,D1に2,･･3,4,5と表示するセルA1に-12345と入力されているとします。
第１位のセルH1から右寄せで順に左に1桁ずつ表示する場合。
セルH1には
ご質問の場合の第１位がセルH1の場合、計算式は次のようになります。=IF(LEN($A1)-COLUMN($H1)+COLUMN()<1,"",IF(MID($A1,LEN($A1)-COLUMN($H1)+COLUMN(),1)="-","▲",MID($A1,LEN($A1)-COLUMN($H1)+COLUMN(),1)))
と入力します。
この式をコピーし、左の列B1まで貼り付けます。
品目毎のデータ数を返す特定の文字列の入っているセルの数を返すにはCOUNTIF関数を使います。
具体的には、セルA1:A10までのデータに"りんご"と言う文字が入力されているセルの数を返すには
=COUNTIF(A1:A4,"りんご")
と入力します。COUNTIF関数の説明は、「関数の使い方説明」
「数学+三角」の「COUNTIF」
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif
を参照してください。
A1:A10に対象の文字列が入力されている行のB1:B10に入力されている数値を合計するには
=SUMIF(A1:A4,"りんご",B1:B4)
となります。SUMIF関数の説明は、「関数の使い方説明」
「数学+三角」の「SUMIF」
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sumif
を参照してください。
何人かの年齢計算で「何歳何ヶ月」と表示、平均を「何年何ヶ月」と表示する人数分の合計・平均を表示するには、
セルA1:A10に複数人の誕生日が入力されている場合、
=DATEDIF($A1,TODAY(),"y")&"年"&DATEDIF($A1,TODAY(),"ym")&"ヶ月"
をセルB1にと入力します。
このセルを選択し、「編集」「コピー」し、以下のセルB2:B10に「貼り付け」ます。
平均年齢を返すには、
セルC1に
=DATEDIF(AVERAGE(A1:A10),TODAY(),"y")&"年"&DATEDIF(AVERAGE(A1:A10),TODAY(),"ym")&"ヶ月"
と入力します。
この結果は、文字列となりますので、計算には使えません。平均年齢を「何.何歳」とするには、
=DATEDIF(AVERAGE(A1:A10),TODAY(),"y")+DATEDIF(AVERAGE(A1:A10),TODAY(),"ym")/12
とし、「書式」「セル」を選択し、「セルの書式設定」で、「表示形式」を「数値」とし、小数点以下の桁数を「1」とします。
この結果は、数値となりますので、計算にも使えます。
２０日締めの翌々月１５日支払日で、土日の場合は翌月曜日を返すセルA1に日付が入力されているとします。20日締めで翌々月の15日支払いで、土日の場合は翌月曜日を返す式は次のようになります。
式が長いですが、連続の式です。
=DATEVALUE(YEAR(EDATE(A1,2+IF(DAY(A1)>20,1,0)))&"/"&MONTH(EDATE(A1,2+IF(DAY(A1)>20,1,0)))&"/15")+IF(WEEKDAY(DATEVALUE(YEAR(EDATE(A1,2+IF(DAY(A1)>20,1,0)))&"/"&MONTH(EDATE(A1,2+IF(DAY(A1)>20,1,0)))&"/15"),2)>=6,8-WEEKDAY(DATEVALUE(YEAR(EDATE(A1,2+IF(DAY(A1)>20,1,0)))&"/"&MONTH(EDATE(A1,2+IF(DAY(A1)>20,1,0)))&"/15"),2),0)
となります。
セル内の日付に２８０日を経過した日付を別のセル内に返すエクセルでは、日付を日付シリアル値として、１日は1となっています。
また、日付シリアル値は数値としての四則演算ができますから、280日後はセル値+280となります。
具体的には、セルA1に日付が2003/8/3と入力してあるとします。
280日後は、
=A1+280となります。
2004/5/9
が返ります。
複数セルに分かれた市外局番、局番、番号を結合する文字列結合関数CONCATENATEまたは&を使います。
具体的には、
セルA1,B1,C1にそれぞれ市外局番、局番、番号が入力されているとします。
=CONCATENATE(A1,B1,C1)
または、
=A1&B1&C1
とします。
また、市外局番に( ) を局番の後ろに-を入れる場合は、
=CONCATENATE("(",A1,")",B1,"-",C1)
あるいは、
="("&A1&")"&B1&"-"&C1
文字列結合関数については、「関数の使い方」の「文字列関数」のCONCATENATEまたは&を参照してください。
縦に並んでいるデータを横に集計する方法INDIRECT関数を使います。
A B C D E 1 実績 目標 2 A 20 30 3 B 23 32 4 C 18 28 5 D 24 27 6 E 22 29 7 8 9 10 達成率 11 A B C D E 12 66.7% 71.9% 64.3% 88.9% 75.9%
セルA12:E12は、セルの書式設定で「表示形式」を「パーセンテージ」にし、「小数点以下の桁数」を1とします。セルA12に
=INDIRECT("Ｂ"&COLUMN()+1)/INDIRECT("C"&COLUMN()+1)
と入力し、セルB12以降の列に貼り付けます。
関数の意味
COLUMN関数で、数式を入力したセルの列番号(この場合1)を返し、それに+1します。(2となります)
INDIRECT関数で、文字列"B"とCOLUMN()+1の結果 2 を文字列結合関数 & で結合して式とします。(B2となります）
同様に、INDIRECT("C"&COLUMN()+1)で、C2を返し、二つの参照結果を / で割ります。
−−−−−−−−−−−−−−−−−−−−−−−−−−
COLUMN セル参照の列番号を返します
INDIRECT 指定した文字列により参照されるセルの値を返します
−−−−−−−−−−−−−−−−−−−−−−−−−−
関数の使い方については、「関数の使い方」の「検索+行列」
INDIRECT
COLUMN
を参照してください。
入社日と退社日を入力し、在職の期間は何年何ヶ月参考になる質問が「質問と回答」の「2002/9/2」
「生年月日を入力した時、自動的に現在、「何歳何ヶ月」か表示する」
にあります。を入社日と退社日に変えます。
具体的には、
セルA1に入社日(例えば1970/4/1)
セルB1に退社日(例えば2003/3/31)
と入力してある場合。
セルC1に
=DATEDIF(EOMONTH(A1,-1),EOMONTH(A2,0),"y")&"年"&DATEDIF(EOMONTH(A1,-1),EOMONTH(A2,0),"ym")&"ヵ月"
と入力します。
33年0ヵ月が返ります。
関数の説明は「関数の使い方説明」
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
を参照してください。
1月を1として(1-1）の場合、答えを「12」としたい日付シリアル値でなく、数値の場合で12進数にする時は、以下のようにします。
セルA1に月としての数値1
セルA2に引く月数-2
と入力してある場合は
=A1+A2+IF(A1+A2<=0,12,IF(A1+A2>12,-12,0))
となります。
計算結果が0以下の場合は12を加算し、12より大きい場合は、12を減じます。
数値2,14,12374を12374,14,2 と桁数に関係なく左からの数値でソートする
A列 に数値が入力してあるとします。数値を列毎に取り出します。
セルA1に入力したデータをB1からF1に振り分ける方法を示します。
MID関数(文字を左からの指定位置から指定数を返します)
COLUMN関数(列番号を返します)
を使います。
A B C D E F 1 12374 1 2 3 7 4 2 14 1 4 3 2 2
セルB1には次の式を入力します。
=MID($A1,COLUMN()-1,1)
この式を下の行にコピーします。次に並べ替えをしますが、４項目以上の並べ替えが必要な場合は、右の列の最後の３項目をまず並べ替えて、最終にはA列以外の左のB,C,D列の並べ替えを 実施します。
すべてのセル範囲A列からF列のデータを選択し、「データ」「並べ替え」を選択し、「並べ替え」ダイアログボックスで「最優先されるキー」に「D列」、２ 番目に優先されるキーに「E列」、３番目に優先されるキーに「F列」を選択し、それぞれ「昇順」にチェツクを入れ、「ＯＫ」ボタンを押します。
再度、「データ」「並べ替え」を選択し、「並べ替え」ダイアログボックスで「最優先されるキー」に「B列」、２番目に優先されるキーに「C列」、３番目に 優先されるキーに「D列」を選択し、それぞれ「昇順」にチェツクを入れ、「ＯＫ」ボタンを押します。必要ない列B,C,D,E,F列を削除します。
セルに入力した時にデータの頭に001,002と順番に付くようにしたい入力セルをA1,A2,A3とした場合、セルB1に
=TEXT(ROW(),"000")&A1
と入力します。
式の意味
ROW()関数で取得した行番号を、TEXT関数で頭に0の付いた３桁で表示し、文字列結合関数&でセルA1のデータと結合します。
この式をコピーして、下のセルB2:B3に貼り付けます。
A B 1 NTT東日本 001NTT東日本 2 NTT西日本 002NTT西日本 3 NTTドコモ 003NTTドコモ
もし、この変換後のデータを元のA列に貼り付けたいのならば、B列のデータをコピーし、「編集」「形式を選択して貼り付け」で「値」としてA列に貼り付け ます。
複数の数値の合計が指定数になる組み合わせを返すソルバーを利用する方法があります。
ソルバーを利用するには「ツール」「アドイン」で「ソルバーアドイン」にチェツクを入れます。
ソルバーがリストボックスに表示されない場合は、セットアッププログラムをもういちど起動し、「追加／削除」ボタンをクリックしてアドインを追加セット アップしてください。
但し，複数の答えがあっても１つしか求められません。
また、組み合わせの数を指定することもできません。
A B C 1 1 =SUMPRODUCT(A1:A8,B1:B8) 2 3 3 8 4 11 5 15 6 19 7 22 8 26 C1:=SUMPRODUCT(A1:A8,B1:B8)と入れておきます。
この式はA列の値とB列の値をかけ算した値の合計を返します。手順
ツール → ソルバー のパラメータ設定で
目的セル:C1
目標値：値にチェック，その右に希望の数値を34と入れます。
変化させるセル:B1:B8制約条件の追加ボタンを押して，セル参照にB1:B8を選択して，中央のボタンを押して，一番下のデータというのを選びます。
制約条件に「バイナリ」と表示されます。
バイナリとは二進数のことで０または1となります。追加ボタンを押して，キャンセルを押して元の画面に戻ります。
これで，「実行」ボタンを押すと，足し算に用いらる数値はB列が1になります。
実行結果で、うまくいけば、「最適解がみつかりました。」と表示されて、B列に 1か 0 が表示されているはずですから、そのまま OK ボタンを押すだけです。
上の場合はB1,B4,B7セルに1が返ります。
稼動時間をセルに色を付けて表示するにはセルに色をつけるには「条件付書式」を使います。
具体的には、
A B 1 出社時間 8:30 2 退社時間 17:30 3 0:00 =AND(A3>=$B$1,A3<=$B$2) 4 1:00 5 2:00 6 3:00 7 4:00 8 5:00 9 6:00 10 7:00 11 8:00 12 9:00 13 10:00 14 11:00 15 12:00 16 13:00 17 14:00 18 15:00 19 16:00 20 17:00 21 18:00 22 19:00 23 20:00 24 21:00 25 22:00 26 23:00 27 24:00
セルB2に退社時間（例えば17:30）を入力します。
セルA3に時刻0:00
セルA4に時刻1:00
と、１時間ごとにセルA27まで、時刻を入力します。
セル範囲のB3:B27を選択し、「書式」「条件付書式」を選択し、「条件１」を「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
=AND(A3>=$B$1,A3<=$B$2)
と入力し「書式」ボタンを押し、「パターン」タグで、セルの色を指定し、「ＯＫ」ボタンを押し、「条件付書式の設定」ダイアログボックスの「ＯＫ」ボタン を押します。
