EXCEL Kansu-Sample.xls Sheet1


日付、曜日を設定するには
  A B 数式
1 2000/1/1 =MID("日月火水木金土",WEEKDAY(A1),1)
2 2000/1/1 H12.1.1 (土) =TEXT(A2,"ge.m.d (aaa)")
3 平成12年1月1日 土曜日 =TEXT(A3,"aaaa")
4 2000/1/1 1 Jan 2000 Sat =TEXT(A4,"d_mmmm_yyyy ddd")
5 2000-01-01 土 書式/セル/表示形式/ユーザー定義で設定の場合 yyyy-mm-dd aaa
6 平成12年1月1日 土曜日 書式/セル/表示形式/ユーザー定義で設定の場合 ggge"年"m"月"d"日" aaaa
<参考>日付や時刻の書式記号について
▼年、月、および日を表示するには、次の書式記号を使います。
h または "hh" の直後、あるいは "ss" の直前に "m" を指定した場合は、月ではなく分が表示されます。
書式記号 表示
M 1 〜 12 (月)
Mm 01 〜 12 (月)
Mmm Jan 〜 Dec (月)
Mmmm January 〜 December (月)
Mmmmm J 〜 D (月名の最初の文字)
D 1 〜 31 (日)
Dd 01 〜 31 (日)
Ddd Sun 〜 Sat (日)
Dddd Sunday 〜 Saturday (日)
Yy 00 〜 99 (年)
Yyyy 1900 〜 9999 (年)
▼時、分、および秒を表示するには、次の書式記号を使います。
書式記号 表示
H 0 〜 23 (時)
Hh 00 〜 23 (時)
M 0 〜 59 (分)
Mm 00 〜 59 (分)
S 0 〜 59 (秒)
Ss 00 〜 59 (秒)
h AM/PM 4:00 AM
h:mm AM/PM 4:36 PM
h:mm:ss A/P 4:36:03 PM
[h]:mm 25.02 など経過時間を時で表示
[mm]:ss 63:46 など経過時間を分で表示
[ss] 経過時間を秒で表示
h:mm:ss.00 秒の小数部分を表示
<参考>
AM と PM 表示形式に AM または PM の文字が含まれる場合、時刻は 12 時間表示で表示されます。
"AM" および "A" は午前 0 時から正午までの時間を表し、
"PM" および "P" は正午から午前 0 時までの時間を表します。
これらの文字が含まれていない場合は、24 時間表示を基準にします。
また、"m" または "mm" は、"h" または "hh" の直後、あるいは "ss" の直前に指定します。
これ以外の位置に指定すると、分ではなく月が表示されます。


カレンダーを設定するには(基準日を A1セル(年)、B1セル(月)、C1セル(日)とした例)
   A    B   C
1   2007 1 1  ←カレンダーを表示する基準年月日
2      
3  曜日 ▼月の数式 ▼日の数式 ▼曜日の数式
4  1 1 =MONTH(B4) =DATE(A1,B1,C1) =B4
5   2 =IF(DAY(B5)=1,MONTH(B5),"") =B4+1 =B5
6   3 =IF(DAY(B6)=1,MONTH(B6),"") =B5+1 =B6
7   4 =IF(DAY(B7)=1,MONTH(B7),"") =B6+1 =B7
〜 途中の行を省略しています 〜
30   27 =IF(DAY(B30)=1,MONTH(B30),"") =B29+1 =B30
31   28 =IF(DAY(B31)=1,MONTH(B31),"") =B30+1 =B31
32   29 =IF(DAY(B32)=1,MONTH(B32),"") =IF(DAY(DATE(A1,B1,29))<4,"",B31+1) =B32
33   30 =IF(DAY(B33)=1,MONTH(B33),"") =IF(DAY(DATE(A1,B1,30))<4,"",B32+1) =B33
34      31    =IF(B34="","",IF(DAY(B34)=1,MONTH(B34),"")) =IF(DAY(DATE(A1,B1,31))<4,"",B33+1) =B34
<注>日のセルB4〜B34の/書式/表示形式/を d   曜日のセルC4〜C34の表示形式を aaa と設定します。
<参考>基準日(表示する最初の年月日)は、セルA1,B1,C1に入力し任意変更が可能です。
<参考>上記例はカレンダーを一ヶ月単位(当月最初の日〜翌月の前日)としています。
<参考>月の表示は、基準日設定で当月と翌月をまたぐ場合に該当月をセルに表示します。

<参考>条件付き書式で色を付ける例
B4〜C34のセルを選択して、メニューの[書式(O)]-[条件付き書式(D)]
条件1 数式が =WEEKDAY(B4:C4)=7  書式/フォント/色を青色  と設定し、[追加(A) >>]
条件2 数式が =WEEKDAY(B4:C4)=1  書式/フォント/色を赤色  と設定し、[ OK ] ボタンを押します。

<参考>曜日の数式で、C4セルに
=IF(B4<>"",MID("日月火水木金土",WEEKDAY(B4),1),"") とする事も出来ます。
この場合、条件付き書式は、条件1 セルの値が/次の値に等しい ="土" のように文字を指定します。
<参考>祝日や振替休日等は、世界標準ではないため自動設定されません。

<参考>成人の日は1月の第2月曜日、体育の日は10月の第2月曜日です。
今年が西暦2007年なら、下記の数式で求められます。
成人の日 =DATE(2007,1,14-WEEKDAY(DATE(2007,1,0),3))
体育の日 =DATE(2007,10,14-WEEKDAY(DATE(2007,10,0),3))
▼<参考>カレンダーを下記例のように、列方向に配置(コピー)するには
  A    B   C D E F G H I J  この列以降を省略〜  
 1     2007   1            
 2 1   1 2 3 4 5 6 7
 3 1   曜日
 4                    
<手順1>元のカレンダー範囲A1〜C34のセルを選択します
<手順2>メニューの[編集(E)]-[コピー(C)] をクリック
<手順3>貼り付けたいセルや、別シートのセルを1つ指定してクリック
<手順4>[編集(E)]-[形式を選択して貼り付ける(S)]
[貼り付け(A):すべて/演算(O):しない/行列を入れ替える(E)]-[ OK ] ボタンをクリック  
<参考>上記の手順で、数式や条件付き書式も含めて自動的に新しく配置されます。
<注>貼り付ける範囲には、3行34列の連続した空白セルが必要です。


一般的な関数の使用例
     A    B C 関数
1 A 100 ▼セル範囲(A1〜A5)の指定データ A の個数
2 B 200 1 =COUNTIF(A1:A5,"A")
3 C 300 ▼セル範囲(A1〜B5)の指定データ B の値合計
4 D 400 200 =SUMIF(A1:A5,"B",B1:B5)
5 E 500
6 300 =AVERAGE(B1:B5) セル範囲の数値の平均値
7 1600 =SUM(B1:B5,100) セル範囲の数値の合計(に値100を加算した合計)
8 1000 =MAX(B1:B5,1000) セル範囲の最大数値(と値1000を比較した最大値)
9 10 =MIN(B1:B5,10) セル範囲の最小数値(と値10を比較した最小値)
10 5 =COUNT(A1:B5) セル範囲の数値の個数(文字や空白以外)
11 10 =COUNTA(A1:B5) セル範囲の空白以外の個数
12 100 =IF(A1="",0,B1) 指定の条件が(空白)なら(ゼロ)、それ以外は(セルの値表示)
13 山田太郎 ヤマダタロウ =PHONETIC(B13) 指定セルのふりがな抽出
<参考>関数貼り付けショートカットキー

【SHIFT】+【F3】
 なにも入力されていないセルで [Shift]+[F3] だと、[関数貼り付け]ダイアログボックスが表示されます。
 既に関数が入力されているセルで[Shift]+[F3]では、数式パレットが表示されます。
 
【CTRL】+【A】
 数式パレットを使って引数を入力するには、たとえばセルに =COUNTIF まで入力してから、[Ctrl]+[A] とします。
 
【CTRL】+【SHIFT】+【A】
 関数の引数を表示するには、例えばセルに =COUNTIF まで入力してから、[Ctrl]+[Shift]+[A] とします。
 すると、=COUNTIF(範囲,検索条件) というように、どんな引数が入るかが表示されます。 

<注>ショートカットキーの +記号は、たとえばSHIFTキーを押しながらF3キーを押すというキー操作を示しています。

<参考>長い数式のトラブルシューティングを行う
ワークシートに作成した長い数式が期待どおりの結果を返さない場合、
ポインタをドラッグして数式バー内の数式の一部を選択し、F9 キーを押します。
この操作を行うと、選択した部分のみが評価されます。

重要 : 【Enter】 キーを押すと数式の選択された部分が消去されます。
消去されないようにするには、【Enter】 キーではなく 【Esc】 キーを押してください。
ただし、誤って 【Enter】 キーを押した場合、【Ctrl】 キーを押しながら Z キーを押すことで変更を元に戻すことができます。


セルの書式設定 > ユーザー定義(カスタム書式)
<手順>セルを右クリック→[セルの書式設定(F)]→[表示形式](分類)→[ユーザー定義](種類)のテキストボックスで指定します。
または、メニューの[書式(O)]-[セル(E)]-[表示形式] でも同様です。
<例>▼値が100までは赤色、それより大きい場合は青色で表示
[赤][<=100];[青][>100]

<例>▼50未満は赤色、50は緑色、50以上は青色、文字なら白色(画面上は非表示)で表示
[赤][<50];[青][>50];[緑]0;[白]
カスタム書式が優先されるため、フォント色を指定しても無視されます。
<例>▼ゼロの場合は空白
[=0]"" と設定します。[=0]"";G/標準 となり、ゼロは表示されません。
<例>▼マイナスの場合は△記号を付けて、ゼロの場合は表示しない
#,##0;"△ "0,0;;@
<例>▼0 以下の数値は絶対値にし、それ以外の数値はそのままで表示
[<0]0;G/標準
<例>▼千単位(四捨五入された値)で表示
#,##0,"千";-#,##0,"千"
<例>▼数字を全角文字(大文字)にする
[DBNum3][$-411]0
<例>▼数値(数字と数字)の間隔をあける
#_;#_;#_;#_;#_;#_;#_;#_;#
<例>▼数字入力のみで、数字の前に @(アットマーク) をつける
"@"#,##0
<例>▼数字入力のみで 1:23 のように時刻形式で表示
0!:00 又は 0":"00
<例>▼数値や文字を ""(ダブルクォーテーション) で囲んで表示  !"@!"
数字を [ ] (角かっこ)で囲んで表示  ![G/標準!]
<例>▼7桁郵便番号にハイフン「−」をまとめて挿入する
000-0000

<参考>表示形式を作成する
書式記号を使用してユーザー定義の表示形式を作成することができます。

小数点以下の桁と有効桁数 分数または小数点が付いた数値の書式を設定するには、次の書式記号を指定します。
小数部に指定した書式記号の数よりも小数部の桁数が多い場合は、
書式記号の数に合わせて小数部の桁数が四捨五入されます。
また、整数部に指定した書式記号の数よりも整数部の桁数が多い場合は、多い分の桁も表示されます。
整数部に数値記号 (#) だけが含まれている場合は、1 以下の数字が小数点の右に表示されます。

# を指定すると、有効桁数だけが表示されて余分なゼロは表示されません。

0 (ゼロ) を指定すると、数値の桁数が指定したゼロの数よりも少ない場合に余分なゼロを表示します。

? を指定すると、Courier New などの固定幅フォントで数値の小数点を揃えるために、
整数部と小数部の余分なゼロがスペースで表示されます。? は桁数の異なる複数の小数を揃えて表示する場合に使います。

▼小数点以下の桁と有効桁数
書式記号 表示
####.# 1234.59 を 1234.6 とする。
#.000 8.9 を 8.900 とする。
0.# .631 を 0.6 とする。
#.0# 12 を 12.0、1234.568 を 1234.57 とする。
???.??? 44.398、102.65、および 2.8 の小数点の位置を揃える。
# ???/??? 5.25 を 5 1/4、5.3 を 5 3/10 と、除算記号を揃える。
▼桁区切り記号
書式記号 表示
#,### 12000 を 12,000 とする。
#, 12000 を 12 とする。
0.0,, 12200000 を 12.2 とする。
▼色
表示形式で色を設定するには、色の名前 (以下の 8 色) のいずれかを角かっこ ([ ]) で囲んで入力します。
これらの色コードは書式部分の先頭に指定する必要があります。
[黒] [青] [水] [緑] [紫] [赤] [白] [黄]
▼条件
数値が指定した条件を満たす場合にだけ表示形式を設定するには、条件を角かっこで囲みます。
条件は、比較演算子と値で指定します。
たとえば、次の書式では、100 以下の数値は赤で、100 より大きい数値は青で表示されます。
[赤][<=100];[青][>100]

セルの値に応じて色の明暗などのセルの書式をワークシートに設定するなど、条件付きで書式を指定するには、
[書式] メニューの [条件付き書式] コマンドを使用します。

組み込みの表示形式を編集しても、その表示形式が削除されることはありません。

<参考>文字列やスペースの書式記号について

▼文字を追加する
文字列と数値の両方をセルに表示するには、文字列をダブル クォーテーション (" ") で囲むか、
表示形式で円記号 (\) に続けて 1 文字を指定します。
たとえば、表示形式に $0.00" Surplus";$-0.00" Shortage" を指定すると、
負の値のときに "$-125.74 Shortage" が表示されます。
スペース、$-+/():!^&`(アクセント記号)、' (クォーテーション) 、~{}=<>、を入力する場合、
ダブル クォーテーションを付ける必要はありません。

▼入力する文字列を含める
文字列の書式部分は、常に表示形式の末尾に指定します。
書式部分のうち、入力した文字列を表示する部分に @ 記号を指定します。
文字列の書式部分に @ 記号がない場合、入力した文字列は表示されません。
入力した文字列と共に特定の文字列を表示する場合は、
「"総合評価" @」 のように表示する文字列をダブル クォーテーションで囲みます。

表示形式に文字列の書式部分がない場合、その表示形式はセルに入力する文字列には影響しません。

▼スペースを追加する
文字幅分の間隔を空けるには、表示形式で下線 (_) に続けて文字を指定します。
たとえば、表示形式の末尾に _) のように下線と右かっこを指定すると、
かっこの幅の分だけ文字間隔が空くため、正の数を負の数と揃えて表示できます。

▼文字を繰り返す
表示形式で指定した文字をセル幅に達するまで繰り返し表示するには、
表示形式の書式部分にアスタリスク (*) を指定します。
たとえば、数字に続けて - を繰り返し表示するには、「0*-」 と指定します。


 関数による漢数字の例
  A B 関数
1  1234567890 十二億三千四百五十六万七千八百九十 =TEXT(A1,"[DBNum1]")
2  1234567890 壱拾弐億参阡四百伍拾六萬七阡八百九拾 =TEXT(A2,"[DBNum2]")
3  1234567890 十2億3千4百5十6万7千8百9十 =TEXT(A3,"[DBNum3]")
4 123 金 壱百弐拾参 萬圓 ="金 "&(TEXT(A4,"[DBNum2]"))&" 萬圓"
5 1234567890 十二億三千四百五十六万七千八百九十 =NUMBERSTRING(A5,1)
6 1234567890 壱拾弐億参阡四百伍拾六萬七阡八百九拾 =NUMBERSTRING(A6,2)
7 1234567890 一二三四五六七八九〇 =NUMBERSTRING(A7,3)
<参考>セルを選択して/書式設定/表示形式/ユーザー定義/でも設定出来ます。
(セルの値が 1234567890 ならば、同様の結果が得られます。)
[DBNum1] 十二億三千四百五十六万七千八百九十
[DBNum2] 壱拾弐億参阡四百伍拾六萬七阡八百九拾
[DBNum3] 十2億3千4百5十6万7千8百9十

<参考>数字の1234567890を、壱弐参四五六七八九〇に変換するには
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(A1,3),"一","壱"),"二","弐"),"三","参")


年齢の計算(厳密に言えば、複雑な問題点があります)
<例1>A1セルに生年月日、B1セルに今日の日付とした場合(単位は年で、1年未満はゼロになります)
C1セルに =DATEDIF(A1,B1,"Y")
または
C1セルに =YEAR(B1)-YEAR(A1)-(DATE(YEAR(B1),MONTH(A1),DAY(A1))>B1)
<例2>A1セルを生年月日とした場合(単位は年月日)
B1セルに =DATEDIF(A1,TODAY(),"Y")&"歳 "&DATEDIF(A1,TODAY(),"YM")&"ヶ月"&DATEDIF(A1,TODAY(),"MD")&"日"
<参考>DATEDIF(開始日,終了日,単位)
満月数を求める場合 =DATEDIF(A1,TODAY(),"M")
満日数を求める場合 =DATEDIF(A1,TODAY(),"D")
<参考> 閏(うるう)年を判定する方法
4 年に一度は閏年 (1 年を 366 日とする年) ですが、実際の恒星年との誤差を解消するため、100 で割り切れる年に対して、
その年がさらに 400 でも割り切れる場合は閏年、割り切れない場合は平年 (1 年を 365 日とする年) となります。
以下の年は、100 で割り切れますが 400 では割り切れないため閏年ではありません。
1700、1800、1900、2100、2200、2300、2500、2600
以下の年は 100 でも 400 でも割り切れるため、閏年になります。
1600、2000、2400
閏年かどうかを判定するには、以上の計算を行う数式を作ります。
セル A1 に入力された年に対して判定する数式は以下のようになります。
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100),1)),"閏年","平年")
<参考>生年月日から定年齢(60歳)に到達する月の末日を求めるには
A1セルに生年月日、B1セルに定年末日の日付とする場合
▼単純に60歳の誕生月の末日を求めるには <セル書式は日付>
B1セルに =DATE(YEAR(A1)+60,MONTH(A1)+1,0)

▼法律に従った年齢の数え方で「定年齢に到達する月の末日」を求めるには
    (1日生まれの人の定年月が,誕生月の前月とする)
B1セルに =DATE(YEAR(DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)-1)),MONTH(DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)-1))+1,0)

▼満60歳までの日数を求めるには <セル書式は標準>
B1セルに =DATE(YEAR(A1)+60,MONTH(A1),DAY(A1))-TODAY()

<注>各数式例は、定年齢を60歳としています。必要に応じて数式中の 60 を変更してください。
<参考>生年月日から学年を求める
A1セルに生年月日が入力されている場合
=MID("___________小1小2小3小4小5小6中1中2中3高1高2高3大1大2大3大4社会",(DATEDIF(A1,DATE(YEAR(TODAY())-(MONTH(TODAY())<=3)*1,4,1),"Y"))*2,2)
または
=CHOOSE(DATEDIF(A1,DATE(YEAR(TODAY())-(MONTH(TODAY())<=3)*1,4,1),"Y")-5,"小1","小2","小3","小4","小5","小6","中1","中2","中3","高1","高2","高3","大1","大2","大3","大4","社会人")
<参考>生年月日から今日現在の学年を表示するには
  A B C D E
1 年齢 学年 生年月日 新年度開始日 現在の学年
2 0 未就学    2000/5/5 2007/4/1 小学1年
3 6 小学1年 ▼新年度開始日(D2セル)の数式
4 7 小学2年 =DATE(YEAR(TODAY())-(MONTH(TODAY())<=3)*1,4,1)
5 8 小学3年 ▼現在の学年(E2セル)の数式
6 9 小学4年 =VLOOKUP(DATEDIF(C2,$D$2,"Y"),LIST,2,TRUE)
7 10 小学5年
8 11 小学6年
9 12 中学1年
10 13 中学2年
11 14 中学3年
12 15 高校1年
13 16 高校2年
14 17 高校3年
15 18 大学1年
16 19 大学2年
17 20 大学3年
18 21 大学4年
19 22 社会人
<注>セル範囲(A2〜B19)にLISTという名前を付けています。
<注>セルC2に生年月日を入力します。

<参考>名前を付けない場合のE2セル数式は
=VLOOKUP(DATEDIF(C2,$D$2,"Y"),$A$2:$B$19,2,TRUE) となります。
<参考>セルC2以下に生年月日を複数人数入力した場合は、
D2〜E2セル選択して、人数分数式を下方向へドラッグ(コピー)します。


月末の日付を取得するには
  A B ▼数式
 1 2004/2/3   2004/2/29 =DATE(YEAR(A1),MONTH(A1)+1,0)
 2    2005/5/5 2005/5/31 =EOMONTH(A2,0)
 3   2006/4/10 13:24 06-4-30 PM 13:24 =TEXT(EOMONTH(A3,0),"yy-m-d ")&TEXT(A3,"AM/PM h:mm")
 4 2007/4/1 期限: 平成 19年 4月 30日 =TEXT(EOMONTH(A4,0),"期限: ggg e年 m月 d日")
<参考>数式中の 0 の部分は、 -1 や 1 などに変更して、月単位で前後した月末を取得できます。
<参考>1カ月後の同日日付を取得するには、=EDATE(A1,1) などの関数を用います。

<参考>表示したいセルに下記数式を入力して日付が取得できます。
今日の1カ月前  =EDATE(TODAY(),-1)
今日の1カ月後  =EDATE(TODAY(),1)

<参考>表示したいセルに下記数式を入力して日付が取得できます。
先月末 =DATE(YEAR(TODAY()),MONTH(TODAY()),0)     または、 =EOMONTH(TODAY(),-1)
今月末 =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)   または、=EOMONTH(TODAY(),0)
来月末 =DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)   または、=EOMONTH(TODAY(),1)

<参考>表示したいセルに下記数式を入力して日数のみ取得できます。
先月  =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())-0,0))  または、 =DAY(EOMONTH(TODAY(),-1))
今月  =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))  または、 =DAY(EOMONTH(TODAY(),0))
来月  =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,0))  または、 =DAY(EOMONTH(TODAY(),1))

<参考>関数EOMONTH を用いる場合
[ツール]-[アドイン]-[分析ツール]にチェックが入っていない場合は、エラー値 #NAME? が表示されます。


住所を、都道府県とそれ以降に分けるには
▼都道府県名を取り除くには
  A B 数式
1 東京都中央区日本町1−2−3 中央区日本町1−2−3 =RIGHT(A1,LEN(A1)-4+SUM((MID(A1,3,1)={"都","道","府","県"})*1))
<注>元住所に、都道府県名が含まれない場合、うまく取り出せません。
<参考>都道府県名のみ取り出す場合: =LEFT(A1,SUM((MID(A1,3,1)<>{"都","道","府","県"})*1))
▼都道府県・市区町村・番地・に分離するには
  A B C D E
1 東京都中央区日本町1−2−3 東京都 中央区日本町 1−2−3 中央区日本町1-2-3
都道府県(セルB1) =LEFT(A1,LEN(A1)-LEN(C1))
市区町村(セルC1) =LEFT(E1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(E1)&1234567890))-1)
数字で始まる番地(セルD1) =RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890)-1))
*都道府県以外(セルE1) =RIGHT(A1,LEN(A1)-4+SUM((MID(A1,3,1)={"都","道","府","県"})*1))
<注>元住所に、都道府県名が含まれない場合、うまく取り出せません。
<注>列E は、各データを取り出す際に必要です。列を削除すると、エラー #VALUE! となります。
<注>都道府県以外の市区町村・丁目・番地等は固有の表記が多く、適切に分離できない場合があります。

<参考>丁目や番地等でスペースや”ー”、"−”、”-”など混在している場合は、
範囲を選択して、メニューの[編集(E)]-[置換(E)]で元の住所データを統一しましょう。
<参考>関数 =JIS(A1)は2バイトに、=ASC(A1)は1バイトに変換します。
<参考>数字とハイフンを2バイト文字で統一 =SUBSTITUTE(JIS(A1),"ー","−")
<参考>数字とハイフンを1バイト文字で統一 =ASC(SUBSTITUTE(JIS(A1),"ー","−"))
▼その他の方法
メニューの[編集]-[置換]を利用します
住所を選択して、検索する文字列: 県*  置換後の文字列: で県名のみとなります。
住所を選択して、検索する文字列: *県  置換後の文字列: 空白 で県名を除いた住所となります。

<注>置換を実行すると、元住所は直接書き換えられてしまいます。
あらかじめコピーした住所データに対して実行しましょう。


スペース(空白)で区切られたデータを取り出すには
A1セルに 山田 太郎 という文字列があると仮定して
文字列のスペースの位置 3 =FIND(" ",A1)
空白までの文字を分離 山田 =LEFT(A1,FIND(" ",A1)-1)
空白以降の文字を分離 太郎 =MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
<注>最初の空白が対象となります。空白が複数ある場合は後の空白は無視されます。

<参考>全角と半角スペース(空白)が混在している場合の姓のみ
=LEFT(A1,FIND(" ",SUBSTITUTE(A1," "," "))-1)

<参考>全角と半角スペース(空白)が混在している場合の名のみ
=RIGHT(A1,LEN(A1)-FIND(" ",SUBSTITUTE(A1," "," ")))

<参考>文字の間に入っているスペースを削除するには
数式 =SUBSTITUTE(SUBSTITUTE((A1)," ","")," ","")

<参考>メニューの[編集]-[置換]でスペースを削除する方法
範囲を選択して、[検索する文字列] にスペースを入力、[置換後の文字列] で何も入力しないで
[すべて置換]をクリックします。


<参考>区切り位置(スペース)で前後のデータを取り出す方法(B列とC列)

<手順1>範囲を選択
<手順2>メニューの[データ(D)]-[区切り位置(E)]
<手順3>[スペースによって右または左に揃えられた固定長フィールドのデータ]にチェックが入っていることを確認
[次へ]ボタンをクリック
<手順4>[データのプレビュー]のところで先頭のスペースと姓の間でクリック
<手順5>[次へ]をクリック
<手順6>[データのプレビュー]のところで1列目が選択されている状態で
<手順7>[完了]をクリックします。

<参考>名前の間のスペースは残して、姓名の前後についているスペースだけを削除するには
数式 =TRIM(A1)


文字列の かっこ( ) で囲まれた文字や値を取り出すには
123(456)7890 456 =MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
123 =MID(A1,1,FIND("(",A1,1)-1)
7890 =MID(A1,FIND(")",A1)+1,10)
123(456) =MID(A1,FIND("",A1),FIND(")",A1))
(456)7890 =MID(A1,FIND("(",A1),FIND(")",A1)+1)
123456 =MID(A1,1,FIND("(",A1,1)-1)&MID(A1,FIND("(",A1)+1,FIND("(",A1)-1)
4567890 =MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)&MID(A1,FIND(")",A1)+1,10)
1237890 =MID(A1,1,FIND("(",A1,1)-1)&MID(A1,FIND(")",A1)+1,10)
1234567890 =MID(A1,1,FIND("(",A1,1)-1) &MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)&MID(A1,FIND(")",A1)+1,10)
又は
=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")
<注>( )はどの位置でも計算結果を返しますが、大文字と小文字を区別します。
数式例では小文字のかっこです。必要に応じて変更してください。

<参考>元の値に( )が含まれていない場合、エラー値 #VALUE! と表示されます。
エラー値の代わりに空白にしたいときは、
456 を取得の場合、数式を =MID(A1,FIND("(",A1&"()")+1,FIND(")",A1&"()")-FIND("(",A1&"()")-1) とします。

<参考>数式中の 10 は取り出す桁数(最大文字数)を指定しています。
<参考>( )のみを除く場合は、対象セルを選択して、メニューの [編集]-[置換] が便利です。
<参考>マクロで同様のコードを紹介しています。


特定の位の数値を取り出すには
       123456.789         2 =INT(MOD(SIGN(A1)*A1/10000,10))              10,000の位
3 =INT(MOD(SIGN(A1)*A1/1000,10)) 1,000の位
4 =INT(MOD(SIGN(A1)*A1/100,10)) 100の位
5 =INT(MOD(SIGN(A1)*A1/10,10)) 10の位
6 =INT(MOD(SIGN(A1)*A1/1,10)) 1の位
7 =INT(MOD(SIGN(A1)*A1/0.1,10)) 小数点第 1位
8 =INT(MOD(SIGN(A1)*A1/0.01,10)) 小数点第 2位
9 =INT(MOD(SIGN(A1)*A1/0.001,10)) 小数点第 3位      
<注>元の値が文字列の場合は、エラー値 #VALUE! となります。

<参考>数式を下記のように、べき乗( ^ ) を用いて表すことも出来ます。
100の位  =INT(MOD(SIGN(A1)*A1/10^2,10))
10の位  =INT(MOD(SIGN(A1)*A1/10^1,10))
小数点第 2位  =INT(MOD(SIGN(A1)*A1/10^-2,10))


文字列から、個別の文字列を取り出すには(区切り文字が複数:ハイフン2カ所)
  A B C D
1 型式コード コード1 コード2 コード3
2 AB123-45(CD)-E678FG AB123 45(CD) E678FG
B2セル数式
=MID(A2,1,FIND("-",A2,1)-1)
C2セル数式
=MID(A2,FIND("-",TRIM(LOWER(ASC(A2))))+1,FIND("-",TRIM(LOWER(ASC(A2))),FIND("-",TRIM(LOWER(ASC(A2))))+1)-FIND("-",TRIM(LOWER(ASC(A2))))-1)
D2セル数式
=MID(A2,FIND("-",TRIM(LOWER(ASC(A2))),FIND("-",TRIM(LOWER(ASC(A2))))+1)+1,10)
<注>上記例で、ハイフン ” - ” が含まれないか1カ所のみの場合、#VALUE! となります。
<参考>ハイフンが1カ所のみの場合、C2セルの数式は =MID(A2,FIND("-",A2)+1,LEN(A2)-FIND("-",A2)) とします。
<参考>上記型式コード中のハイフンは、どの位置でもデータを取り出します。

<参考>文字列が 1024*768*256 なら
B2セル =(LEFT(A2,FIND("*",TRIM(LOWER(ASC(A2))))-1))
C2セル =MID(A2,FIND("*",TRIM(LOWER(ASC(A2))))+1,FIND("*",TRIM(LOWER(ASC(A2))),FIND("*",TRIM(LOWER(ASC(A2))))+1)-FIND("*",TRIM(LOWER(ASC(A2))))-1)
D2セル =MID(A2,FIND("*",TRIM(LOWER(ASC(A2))),FIND("*",TRIM(LOWER(ASC(A2))))+1)+1,9)

<参考>区切り文字は必要に応じて変更してください。
<参考>個別の文字列を取り出すには(区切り文字がシャープ(#)1カ所の場合)
  A B C
1 型式コード コード1 コード2
2 abc#12345xyz abc 12345xyz
B2セル数式  =LEFT(A2,FIND("#",A2&"#")-1)
C2セル数式  =RIGHT(A2,LEN(A2)-LEN(B2)-1)
<注>上記例で、シャープ ”#” が含まれない場合、C2セルは #VALUE! となります。

<参考>B2セル数式中の -1 を省略すると、abc# が取り出せます。
<参考>C2セル数式中の -1 を +1にすると、#12345xyz が取り出せます。

<参考>区切り文字は必要に応じて変更してください。


2つのセルを、ひとつのセルに表示するには
  A B C 数式
1 合計    2010 合計2010 =A1&B1 ← &演算子で結合
2 合計 2,010 =A1&" "&(TEXT(B1,"###,0")) ← 値に桁区切り書式を指定
3 残高合計 \2,010 - ="残高"&A1&" "&(TEXT(B1,"\###,0"))&" -" ← 文字と円書式を指定
4 合計
@ 2010
=A1&CHAR(10)&"@ "&B1 ←<改行指定>セルの[書式設定]-[配置]-[文字の制御]
「折り返して全体を表示する」にチェックを入れます。
<注>数式を適用したセルの書式は、文字列となります。

<参考>関数 CONCATENATE (文字列1, 文字列2, ...)
文字列1, 文字列2,... 1 つにまとめる文字列を指定します。引数は 1 〜 30 個まで指定できます。
また、文字列だけでなく、数値やセル参照も指定できます。

<数式例>
=CONCATENATE(A1,B1) は =A1&B1 と同じ 合計2010 を返します。
<数式例>
=CONCATENATE(A1," から ",B1," 引きます") は 合計 から 2010 引きます という結果を返します。


文字列の後ろから4番目に特定の文字ハイフン ”-” を挿入するには
=REPLACE(A1,LEN(A1)-2,0,"-")
又は
=CONCATENATE(LEFT(A1,LEN(A1)-3),"-",RIGHT(A1,3))
<注>上記数式例では、セルA1の文字数が2文字以下の場合、#VALUE! となります。

<参考>セルA1の文字が 12345 の場合
12-345 →数式は =REPLACE(A1,LEN(A1)-2,0,"-")
123-45 →数式は =REPLACE(A1,LEN(A1)-1,0,"-")
1234-5 →数式は =REPLACE(A1,LEN(A1)*1,0,"-")
12345- →数式は =REPLACE(A1,LEN(A1)*2,0,"-")

<参考>特定の文字は数式中の "-" の部分で指定します。自由に変更してください。


文字列の一部を、置き換え・取り出し・取り除くには
元の文字(A1) 変換された文字 数式
123-456-7890 (123)456-7890 ="("&SUBSTITUTE(A1,"-",")",1) ←はじめのハイフンまでを、かっこ( ) で囲む
123-456-7890 123456-7890 =SUBSTITUTE(A1,"-","",1)  ←はじめのハイフンのみ取り除く
123(456)7890 123-456-7890 =SUBSTITUTE(SUBSTITUTE(A1,"(","-",1),")","-",1) ←かっこ( ) をハイフンに置き換える
code-A001(S) 型式:A001(S) =SUBSTITUTE(SUBSTITUTE(A1,"code","型式",1),"-",":",1) ←codeを型式に、ハイフンを :に置き換える
ID-1234567890 ID-********** =REPLACE(LEFT(A1,3),4,,REPT("*",LEN(A1)-3)) ←有効3桁で以降を文字数分アスタリスク * に置き換える
ID-1234567890 1234567890 =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},)))) ←文字列+数字+文字列から数字のみ取り出す
1234567890 123456789-0 =REPLACE(A1,LEN(A1),0,"-") ←最後の一文字の前にハイフン挿入する
1234567890 12345***** =REPLACE(LEFT(A1,5),6,,REPT("*",LEN(A1)-5)) ←有効5桁で以降をアスタリスクに置き換える
1234567890 *67890 =REPLACE(RIGHT(A1,5),1,0,"*") ←はじめの5桁をアスタリスクで省略する
1234567890 12345* =REPLACE(LEFT(A1,5),6,0,"*") ←6文字目以降をアスタリスクで省略する
1024 Pixel 1024 =LEFT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},)))) ←数字+文字列から数字のみ取り出す
(株)日本 日本 =SUBSTITUTE(SUBSTITUTE(A1,"(株)",""),"(有)","") ←文字列中の(株)や(有)を除く
100-0001 1000001 =SUBSTITUTE(A1,"-","") ←郵便番号のハイフンを除く
<注>数式はすべて、元の文字をA1セルに置いた場合のものです。
<参考>置き換える記号や文字、桁数などは必要に応じて変更してください。
<参考>対象セルを選択して、メニューの [編集]-[置換] でも可能な場合があります。


文字の検索/置換で、?や * を置き換えるにはチルダ(~)を使用します
置換前の文字 置換後
??Sample?? **Sample** 検索置換前の文字 ~? と指定し、置換文字を * とした場合
**Sample** ??Sample?? 検索置換前の文字 ~* と指定し、置換文字を ? とした場合
<手順>範囲を選択して、メニューの[編集]-[置換]-[検索する文字列]-[置換後の文字列] で行います。
<参考>標準では、置換前の文字として、* や ? はエクセルに認識されません。 

<参考>ワイルドカード文字
部分的に等しい文字列を検索するには、ワイルドカード文字を使います。
1 つのワイルドカード文字は、1 つまたは任意の数の文字を表します。

ワイルドカード文字 / 検索対象
? (疑問符) 疑問符と同じ位置にある任意の 1 文字
たとえば、「sm?th」と入力すると "smith" や "smyth" が検索されます。
* (アスタリスク) アスタリスクと同じ位置にある任意の数の文字
たとえば、「*east」と入力すると "Northeast" や "Southeast" が検索されます。
~ (チルダ) (?、*、または ~ の前に入力) 疑問符、アスタリスク、またはチルダ
たとえば、「excel~?」と入力すると "excel?" が検索されます。
<参考>置き換えでアポストロフィ「'」を取り除く
セルに表示されないクォーテーション (')、アポストロフィ (') は文字認識されず検索できません。

'123 や 'abc などの数値や文字列の場合
空白のセルをコピーし、該当セルを選択して右クリック、
メニューの「形式を選択して貼り付け」→演算を「加算」として[OK]ボタンをクリックします。

または
メニューの [データ] → [区切り位置] → [完了]ボタンをクリックします。

'=A1
などの数式の場合で列単位の場合は該当セルを選択して
メニューの [データ] → [区切り位置] → [完了]ボタンをクリックします。

<注>数式で形式を選択して貼り付けの場合は
=A1 と表示されますがそれを一度編集状態にして確定しないと数式の計算の結果が表示されません。
<参考>セル内改行を解除後に残る中点「・」を取り除く
セル内改行 [Alt]+[Enter] された文字列を元に戻す場合、通常の検索/置換は文字として認識されません。
[セルの書式設定]-[配置] で 「折り返して全体を表示する」 のチェックを解除してもセルに中点 「・」 が残ります。

<対策> 「・」 を取り除くには、メニューの[編集]-[置換] でダイアログボックスを表示させ
[Ctrl]+「J」 とキー操作して、[置換] ボタンをクリックします。

<注>[検索する文字列] と[置換後の文字列] には何も入力しません。
<注>列幅と行の高さは自動調整されないので、適切に変更してください。

<参考>置換ダイアログ表示のショートカットキーは [Ctrl]+「H」 です。
<参考>VBA(2)ページに  セル内改行文字列を標準表示に戻す を掲載しています。


セルの値に対応する文字列を表示するには
  A B
1 コード 対応する値 数式
2 1 社員 =IF(A2="","",CHOOSE(A1,"社員","パート","アルバイト"))
3 3 アルバイト =IF(A3="","",CHOOSE(A1,"社員","パート","アルバイト"))
<注>セルに数式で設定した項目数以外を入力した場合は、エラー値 #VALUE! となります。

<参考>A列セルの値 1,2,3... に対応して、数式で指定された文字列をB列に表示します。
数式例では、1=社員、2=パート、3=アルバイト、となります。
<参考>数式内の ”〜” の文字の順番に対応し、コンマで区切って追加や変更が可能です。
<参考>最大・最小に対応する文字列
  A B C
1 コード 結果 数式
2 A-001 5 ▼値が最大のA列コードを取得
3 B-001 50 C-001 =INDEX(A:A,MATCH(MAX(B:B),B:B,0))
4 C-001 100 ▼値が最小のA列コードを取得
5 D-001 50 D-002 =INDEX(A:A,MATCH(MIN(B:B),B:B,0))
6 D-002 -5
<注>A列にデータがない場合はゼロ、B列に値データがない場合は、エラー値 #N/A となります。

<参考>値が同じ場合は、最初のセルの値が返されます。
<参考>数式例では、範囲を列全体 A列(A:A)、B列(B:B) としています。必要に応じて範囲を設定してください。
<参考>最下行のデータ/最右列のデータ の項も参照してください。


値が1000未満はKg(キログラム)、1,000以上はt(トン)と表示するには
=IF(A1>=1000,A1/1000&" t",A1&" kg")
<参考>数式は計算対象(A1セル)に対して適用の例です。

グラム(g) >キログラム(Kg)   =CONVERT(A1,"g","kg")
キログラム(Kg) >グラム(g)  =CONVERT(A1,"kg","g")
メートル(m) >キロメートル(km)  =CONVERT(A1,"m","km")
キロメートル(km) >メートル(m)  =CONVERT(A1,"km","m")
メートル(m) >フィート  =CONVERT(A1,"m","ft")
キロメートル(Km) >マイル  =CONVERT(A1,"km","mi")
センチメートル(cm) >インチ  =CONVERT(A1,"cm","in")
メートル(m) >ヤード  =CONVERT(A1,"m","yd")
摂氏 >華氏  =CONVERT(A1,"C","F")
摂氏 >絶対温度  =CONVERT(A1,"C","K")
リットル >ガロン  =CONVERT(A1,"l","gal")
リットル >デシリットル  =CONVERT(A1,"l","dl")
リットル >ミリリットル  =CONVERT(A1,"l","ml")

年>時間  =CONVERT(A1,"yr","hr")
日>時間  =CONVERT(A1,"day","hr")
日>分  =CONVERT(A1,"day","mn")
時間>日 =CONVERT(A1,"hr","day")
時間>分 =CONVERT(A1,"hr","mn")
時間>秒 =CONVERT(A1,"hr","sec")
分>時間 =CONVERT(A1,"mn","hr")

<参考>CONVERT(数値, 変換前単位, 変換後単位)
<注>メニューの[ツール]-[アドイン]-[分析ツール]にチェックを入れる必要があります。
<注>引数のデータ型が適切でない場合、エラー値 #VALUE! が返されます。
また、存在しない単位を指定すると、エラー値 #N/A が返されます。


度分秒の角度を入力すれば三角関数が計算できる数式例
A1 に 30:00:00 と入力する場合、
=SIN(A1*PI()/7.5) 
<注>度分秒を入力するセルの書式を [h]"°"mm"' "ss!" と設定します。
A1に(度),B1に(分),C1に(秒)なら
=SIN((A1+B1/60+C1/3600)*PI()/180)


A列(途中に空白あり)の中で、データのある最下セルの行番号は
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))
<参考>上記例は、A列すべての行が対象となります。セル範囲は必要に応じて変更してください。
<参考>最下行のデータ
▼A列(数値のみ・途中空白なし)の中で、最下行の数値は
数式 =IF(COUNT(A:A),INDEX(A:A,COUNT(A:A)),"")

▼A列(途中に数値・空白・文字が混在)の中で、最下行の数値は
数式  =INDEX(A:A,MATCH(MAX(A:A)+1,A:A,1))

▼A列(途中空白なし)の中で、最下行の文字列は
数式 =IF(COUNTA(A:A),INDEX(A:A,COUNTA(A:A)),"")

▼A列(途中に数値・空白・文字が混在)の中で、最下行の文字列は
数式 =INDEX(A:A,MATCH("",A:A,-1))

▼A列(途中に数値・空白・文字が混在)の中で、最下行の値は
数式 =IF(COUNTA(A:A),INDEX(A:A,MAX(IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1)),IF(COUNT(A:A),MATCH(MAX(A:A)+1,A:A,1)))),"")

<参考>範囲指定の配列数式例 (列全体を範囲とすることができません) [CTRL]+[SHIFT]+[Enter]キーで確定
配列数式 {=INDEX(A1:A100,MAX(ROW(A1:A100)*(A1:A100<>"")))}  ←範囲(A1〜A100)の例
<注>データが全く存在しない場合は循環参照エラー、または#N/A となります。
<参考>数式中の A:A がA列全体を指定しています。
行1(途中に空白あり)の中で、データのある最右セルまでの列数は
=LOOKUP(2,1/(1-ISBLANK(1:1)),COLUMN(1:1))
<参考>上記例は、1行目すべての列(A〜IV)が対象となります。
行5を指定する場合は 5:5 とします。
<参考>最右列のデータ
▼行1の中で最右セルの数値は
数式 =LOOKUP(10^15,1:1)
数式 =LOOKUP(10^15,A1:H1)  ←範囲指定(行1のA列〜H列)の例

▼行1の中で最右セルの文字列は
数式 =INDEX(1:1,MATCH("",1:1,-1))
数式 =INDEX(A1:H1,MATCH("",A1:H1,-1))  ←範囲指定(行1のA列〜H列)の例
<注>データが全く存在しない場合は循環参照エラー、または#N/A となります。
<参考>数式中の 1:1 が行1を指定しています。


2、3、4、5行目ごとのデータ(数字・文字列)を取得するには
  A B C D E 数式
1 DATA 取得値(2) 取得値(3) 取得値(4) 取得値(5) 行2の各列に入力します
2 A B C D E 2行目ごと(B2数式)  =OFFSET($A$2,ROW()*2-3,0)
3 B D F H J 3行目ごと(C2数式)  =OFFSET($A$2,ROW()*3-4,0)
4 C F I L 0 4行目ごと(D2数式)  =OFFSET($A$2,ROW()*4-5,0)
5 D H L 0   5行目ごと(E2数式)  =OFFSET($A$2,ROW()*5-6,0)
6 E J 0    
7 F  L      
8 G  0      
9 H        
10 I         <参考>
11 J         10行目ごと  =OFFSET($A$2,ROW()*10-11,0)
12 K        
13 L        
<注>行2に取得したい数式を入力後、下方向にドラッグ(コピー)します。

<参考>各取得値は、元のデータが数字 ・文字列 ともに結果を返します。
<参考>対象(n行目ごと)のデータが有効範囲にない場合は ゼロ と表示されます。
<参考>元のデータがC列にある場合は、数式中の $A$2 を $C$2 とします。

<参考>数式例は入力されたセルの行番号に依存します。
この式では、ROW 関数は、数式が入力されたセルの行番号を返します。この番号に X を掛けます。
OFFSET 関数は元のセルから指定された行番号までアクティブ セルを下に移動し、n番目のセルごとに値を返します。

<参考>項目行(行1)が不要の場合は、数式(3行目ごとなら) =OFFSET($A$1,ROW()*3-1,0) とします。


一行おきの合計(例:A列 行1〜行10の場合)
=SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*A1:A10)
=SUMPRODUCT((MOD(ROW(A1:A10),
2)=0)*A1:A10)
または

{=SUM(IF(MOD(ROW(A1:A10),2)=1,A1:A10,0))} ←配列数式 (CTRL+SHIFT+Enter)
{=SUM(IF(MOD(ROW(A1:A10),2)=0,A1:A10,0))} 
上記数式中の2は、計算対象が1行おき(2,4,6,8,10行)、3なら3行おき(3,6,9行)、5なら5行おき(5,10行)、になります。
上記数式中の1は、計算開始方法を指定しています。ゼロは範囲の2番目から、1は範囲の先頭から計算します。


2、3、4、5列目ごとのデータ(数字・文字列)を取得するには
  A B C D E F G H I J K L M N O P Q R S T U 数式
1 DATA 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 B列の各行に入力
2 取得値(2) 2 4 6 8 10 12 14 16 18 20 0                   2列目ごと=OFFSET($B$1,,COLUMN()*2-3,1)
3 取得値(3) 3 6 9 12 15 18 0                           3列目ごと=OFFSET($B$1,,COLUMN()*3-4,1)
4 取得値(4) 4 8 12 16 20 0                             4列目ごと=OFFSET($B$1,,COLUMN()*4-5,1)
5 取得値(5) 5 10 15 20 0                               5列目ごと=OFFSET($B$1,,COLUMN()*5-6,1)
6 取得値(10) 10 20 0                                   10列目ごと=OFFSET($B$1,,COLUMN()*10-11,1)
<注>B列に取得したい数式を入力後、右方向にドラッグ(コピー)します。

<参考>各取得値は、元のデータが数字 ・文字列 ともに結果を返します。
<参考>対象(n列目ごと)のデータが有効範囲にない場合は ゼロ と表示されます。
<参考>元のデータが行3にある場合は、数式中の $B$1 を $B$3 とします。


一列おきの合計(例:行1 A〜F列の場合)
=SUMPRODUCT((MOD(COLUMN(A1:F1),2)=1)*A1:F1)
=SUMPRODUCT((MOD(COLUMN(A1:F1),2)=0)*A1:F1)
行1のA,C,E列
行1のB,D,F列
<または>
{=SUM(IF(MOD(COLUMN(A1:F1),2)=1,A1:F1,0))}
{=SUM(IF(MOD(COLUMN(A1:F1),2)=0,A1:F1,0))}

配列数式
CTRL+SHIFT+Enter
上記数式中の2は、計算対象が1列おき(A,C,E列)、3なら3列おき(A,D列)のようになります。
上記数式中の1は、計算開始方法をを指定しています。ゼロは範囲の2番目から、1は範囲の先頭から計算します。
<参考>項目ラベルが”金額”の列を合計する
  A B C D E F G
1 日付 金額 日付 金額 日付 金額 合計 数式
2 1/1 10 1/10 20 1/20 30 60 =SUMIF(A$1:F$1,"金額",A2:F2)
3 2/1 40 2/10 50 2/20 60 150 ↑数式を下方向へドラッグします↓
4 3/1 70 3/10 80 3/20 90 240


条件付き書式で、1行ごとに色分けするには
<例>範囲を選択して、メニューの [書式]-[条件付き書式]
[数式が] =MOD(ROW(),2)=0 または =MOD(ROW(),2)=1 とし、任意の書式を設定します。
偶数行、奇数行を区別する方法として、便利です。

<参考>A列に値がある場合、偶数行なら色を付ける
[数式が] =(MOD(ROW(),2)=0)*($A1<>"")
<参考>A〜F列のどれかのセルに値がある場合、奇数行なら色を付ける
[数式が] =AND(MOD(ROW(),2)=1,SUM(($A1:$F1<>"")*1))
日付入力されている範囲に、昨日まで、今日、明日以降を設定するには
<例>日付が入力されているセル(A1〜A20)を選択して、メニューの [書式]-[条件付き書式]
昨日までは、 条件1 [数式が] =A1<TODAY() 
今日の場合は、条件2 [数式が] =A1=TODAY()
明日以降は 条件3 [数式が] =A1>TODAY() として、任意の書式を設定します。
<参考>日付や数値に対する論理式の記号は >,>=,=,<=,<,<>  があります。
セルの値に対して、条件付き書式を適用するには
<例>選択範囲(A1からD10)で、セルの値が A または B なら条件を適用する
指定セル、またはセル範囲を選択して、
[書式]-[条件付き書式] [数式が] =OR(A1="A",A1="B") として、任意の書式を設定します。
<参考>A列の値が A または B なら、選択範囲行に条件を適用
=OR($A1="A",$A1="B") とします。
セルA1に入力があると、選択範囲に条件付き書式を適用するには
<例>選択範囲(A1からD1)で、セルA1に入力があると条件を適用する
セル(A1:D1)を選択して、[書式]-[条件付き書式] [数式が] =$A1>0 として、任意の書式を設定します。
選択範囲に空白があると、条件付き書式を適用するには
<例)>選択範囲(A1からD1)に、1つでも空白があると、条件を適用する
セル(A1:D1)を選択して、[書式]-[条件付き書式] [数式が] =OR($A1:$D1="") として、任意の書式を設定します。
または、=OR(ISBLANK($A1:$D1)) としても同じ結果となります。
部分一致の条件付き書式
<例>セルに”東京”が含まれている場合、条件を適用するには
数式が =SEARCH("東京",A1)
または、数式が =FIND("東京",A1)>0 または =FINDB("東京",A1)>0
<例>セルA1の文字や値を含む場合、範囲(A1〜D1)に条件を適用するには
数式が =IF($A1="",,OR(A1:D1,FIND($A1,A1)>0))
<例>セルA1と一致するセルに条件を適用するには
数式が =FIND($A$1,A1)>0
<例>セルA1が空白の場合、条件を適用されないようにするには
数式が =AND(FIND($A$1,A1)>0,$A$1<>"")


セルに特定単語を入力すると、対応する入力リストを表示するには
キャベツ
大根
白菜
と入力されている範囲に「野菜」という名前をつけます。
みかん
りんご
ぶどう
と入力されている範囲に「くだもの」という名前をつけます。
特定単語セルをA1セルとし、B1セルで入力リストから選択する場合
▼A1セルに入力規則を設定
A1選択/[データ]-[入力規則] の、入力値の種類→リスト→「元の値」の欄に 野菜 ,くだもの と入力します。
▼B1セルに入力規則を設定
B1選択/[データ]-[入力規則] の、入力値の種類→リスト→「元の値」の欄に=INDIRECT(A1)と入力します。
この手順で、A1セルに入力された値に応じた範囲名のリストが、B1セルで選択出来るようになります。
A1セルの値が【野菜】ならば、B1のリストには野菜のリストが出ます。
A1セルの値が【くだもの】ならば、同様にリストはくだものの種類になります。
  A B       A B
1   野菜     1 くだもの
キャベツ
大根
白菜
みかん  
りんご
ぶどう
<参考>名前をつけるには
セル範囲を選択して、名前ボックス(セル番地の表示欄)に直接入力が便利です。

<注>別のシートにあるリストを使いたい場合は =名前 を使用するか
元の値に =INDIRECT("Sheet2!$A$1:$A$10") などの範囲を指定した数式を入力します。
この場合、単一の行、または列の参照に限定されます。

<参考>入力規則で自動的にリスト項目を増やす設定
例:入力規則のリストがA1セル以下に入力されている場合
「挿入」「名前」「定義」で、たとえば「リスト」と名前をつけて、参照範囲に以下数式を入力します。
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
<参考>リストから重複項目のないリストを生成するには
  A B (1)セル A1:A10 のLISTのデータ範囲を選択します。
(2)[データ] メニューの [フィルタ] をポイントし、[フィルタ オプションの設定] をクリックします。
(3)[抽出先] の下の [指定した範囲] をクリックします。
(4)[リスト範囲] ボックスに $A$1:$A$10 と入力します。
(5)[重複するレコードは無視する] チェック ボックスをオンにし、
[抽出範囲] ボックスに $B$1 と入力し、[OK] をクリックします。

重複する項目のないリストが B 列に表示されます。
1  LIST  LIST
2 A A
3 B B
4 C C
5 A D
6 B  
7 C  
8 C  
9 C  
10 D  
<参考>この方法は複数の列に対しても有効です。
<参考>[フィルタ オプションの設定] コマンドで抽出先を選択範囲内とした場合、
A列の重複した行を非表示にすることができます。


入力リストから、対応する入力リストと値を表示するには
  A B C D E F G H
1 部署A   部署B   部署C   部署D  
2 米国1課 A-0001 業務1課 B-0001 営業1課 C-0001 宣伝1課 D-0001
3 米国2課 A-0002 業務2課 B-0002 営業2課 C-0002 宣伝2課 D-0002
4 中国1課 A-0003 業務3課 B-0003 営業3課 C-0003 宣伝3課 D-0003
5 中国2課 A-0004 総務部 BS-100 営業4課 C-0004    
6     渉外部 BS-200 営業5課 C-0005    
Sheet1 /Sheet2/Sheet3
▼入力リストの元データを作成します(Sheet1)
(1) A1〜G1 を選択し 部署 と名前を付けます。
(2) A2〜A5 を選択し 部署A と名前を付けます。
(3) C2〜C6 を選択し 部署B と名前を付けます。
(4) E2〜E6 を選択し 部署C と名前を付けます。
(5) G2〜G4 を選択し 部署D と名前を付けます。

(6) A2〜B5 を選択し codeA と名前を付けます。
(7) C2〜D6 を選択し codeB と名前を付けます。
(8) E2〜F6 を選択し codeC と名前を付けます。
(9) G2〜H4 を選択し codeD と名前を付けます。
<参考>名前は数式バー左側にある名前ボックスに入力します。
  A B C
1 部署A 米国2課 A-0002
2 部署C 営業4課 C-0004
3
Sheet1/ Sheet2 /Sheet3
▼データ入力するシート(Sheet2)
セルA1(部署のリスト選択を行うセル)で、
  データ → 入力規則 → 設定 → 入力値の種類 で「リスト」を選択し、
  「元の値」に =部署 又は =INDIRECT("部署") と入力する。

セルB1(項目のリスト選択を行うセル)で、
  データ → 入力規則 → 設定 → 入力値の種類 で「リスト」を選択し、
  「元の値」に =INDIRECT(A1) と入力する。

セルC1 に下記数式を入力します。
=IF(B1="","",IF(A1="部署A",VLOOKUP(B1,codeA,2,FALSE),IF(A1="部署B",VLOOKUP(B1,codeB,2,FALSE),IF(A1="部署C",VLOOKUP(B1,codeC,2,FALSE),IF(A1="部署D",VLOOKUP(B1,codeD,2,FALSE),"")))))

セルA1〜C1 を選択して、下方向へドラッグ(コピー)します。
<参考>上記例では、A列の値に応じたリストがB列で選択でき、それに対応する値がC列に表示されます。
<参考>名前や項目は一例です。必要に応じて変更してください。
<参考>項目追加の場合、挿入 → 名前 → 定義 でリストの元範囲の変更が出来ます。


シート名やファイル名、フォルダ名を表示するには
Sheet1 =MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)
C:\Data\[Kansu-Sample.xls]Sheet1 =CELL("filename")
C:\Data\[Kansu-Sample.xls]Sheet1$A$1 =CELL("filename")&ADDRESS(ROW(),COLUMN())
C:\Data\ =INFO("directory")
<注>数式例で得られるデータは、コンピュータの使用環境や各種設定状況により異なります。
<注>新規ブックの場合は一旦保存します。
<参考>INFO(検査の種類)

検査の種類と返される情報
"directory" カレント ディレクトリまたはカレント フォルダのパス名
"memavail" 使用可能なメモリ容量 (単位 : バイト)
"memused" データを一時的に保存するために使用されているメモリ容量 (単位 : バイト)
"numfile" 開かれているワークシートの枚数
"origin" "$A:" で始まる A1 形式の絶対参照。現在ウィンドウに表示されている範囲の左上隅のセル参照
"osversion" 現在使用されているオペレーティング システムのバージョン
"recalc" 現在設定されている再計算のモード ("自動"、"手動"のいずれか)
"release" Excel のバージョン
"system" 操作環境の名前 (Windows 版 Excel では "pcdos"、Macintosh 版 Excel では "mac")
"totmem" 既に使用されている総メモリ容量 (単位 : バイト)


金種計算
A B C D E F G H I J K
1 Name 金額 10000 5000 1000 500 100 50 10 5 1
2 田中 138,983 13 1 3 1 4 1 3 0 3
3 山本 123,456 12 0 3 0 4 1 0 1 1
4 80,540 8 0 0 1 0 0 4 0 0
上記レイアウトのように C1〜K1セルに数値(金種)を入力します。
C2セルに以下の数式を入力します。
=INT(B2/C$1)
D2セルに以下の数式を入力します。
=INT(MOD($B2,C$1)/D$1)

上記の各数式を入力後
D2セルを右方向にK2までフィル(ドラッグコピー)します。
続いて、C2〜K2を選択して、下方向へ人数分フィル(ドラッグコピー)します。


TOP / FAQ1 / FAQ2 / FAQ3 / 上へ

Copyright © 2013 TOMBO. All rights reserved.