Excel関数(文字列の操作、関数を使った集計・抽出)

##特定の文字の前を取り出す

「事業所」の前

=LEFT(A2,FIND("事業所",A2)-1)

##特定の文字の後ろを取り出す

「：」の以後

=RIGHT(A2,LEN(A2)-(FIND("：",A2)-1))

「：」の後ろ

=RIGHT(A2,LEN(A2)-(FIND("：",A2)))

##特定の文字と文字の間を取り出す
A1に「ほげほげR4.1」、「R」と「.」の間の年を取り出す
B44に「左の文字列あいうえお右の文字列」、「左の文字列」(文字数が1文字以外)と「右の文字列」の間の文字を取り出す。

=MID(A1,FIND("R",A1)+1,FIND(".",A1)-FIND("R",A1)-1)
=MID(B44,FIND("左の文字列",B44)+len("左の文字列")+1,FIND("右の文字列",B44)-FIND("左の文字列",B44)-len("左の文字列")-1)

#スペースで区切られた姓と名をそれぞれ取り出す

予めスペースを全角に統一する
=TRIM(JIS(A1))

予めスペースを半角に統一する
=SUBSTITUTE(A1,"　"," ")


姓(スペースの左側)
=LEFT(A1, FIND(" ", A1)-1)

=TRIM(JIS(LEFT(A1, FIND(" ", A1)-1)))
=SUBSTITUTE(LEFT(A1, FIND(" ", A1)-1),"　"," ")

名(スペースの右側)
=RIGHT(A1, LEN(A1)-FIND(" ", A1))

=TRIM(JIS(RIGHT(A1, LEN(A1)-FIND(" ", A1))))
=SUBSTITUTE(RIGHT(A1, LEN(A1)-FIND(" ", A1)),"　"," ")


住所のスペースより前を取り出す
=SUBSTITUTE(A1,"　"," ")
=IF(ISERROR(FIND(" ", A1)), A1, LEFT(A1, FIND(" ", A1) - 1))

##左から1文字だけ取り出す

=LEFT(A2,1)

#末尾2文字を削除する

=LEFT(A2, LEN(A2)-2)

#左から〇文字目以降を取り出す

2文字目以降
=MID(A2,2,LEN(A2)-1)

8文字目以降
=MID(A2,8,LEN(A2)-7)

#何文字目から何文字取り出す

2文字目から4文字
=MID(A2, 2, 4)

6文字目から2文字
=MID(A2,6, 2)

#ゼロ埋め、ゼロパディング

A1が「1」の場合、「0001」になる
=TEXT(A1,"0000")

都道府県名を抜き出す

=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))

#全角・半角スペースを削除

SUBSTITUTE(SUBSTITUTE(c2,"　","")," ","") 

#半角・全角をそろえる

半角にする
=ASC(A1)

全角にする
=JIS(A1)

#　0以上の数をカウントする

0以上
=COUNTIF(C2:C20, ">=0")

0より大きい(0は数えない)
=COUNTIF(C2:C20, ">0")

#空白か判定する

=IF(A1<>"", "値が入ってます", "空白セルです")

#日付操作

##テキスト形式を日付形式に変換する

=DATEVALUE(A1)

##○か月後

=EDATE(A1,○)

##2年経過判定
2年経過していたら○、していなければ何も表示しない

IF(DATEDIF(A1,TODAY(),"y")>=2,"○","")

##経過月数

=DATEDIF(開始年月日,終了年月日,"M")&"ヶ月"

##型の変換
vlookupで数字がヒットしないとき

文字列を数値に変換 1
=値*1

文字列を数値に変換 2
=VALUE(値)

数値を文字列に変換
=TEXT(値,表示形式)
=text(参照先,"@")


文字列型か判定
=ISTEXT(値)

数値型か判定
=ISNUMBER(値)

指定した位置のデータを取り出す

=INDEX(範囲,行番号,列番号)

選択範囲の3行目の2列目の値を取り出す
=INDEX($A$1:$B$8,3,2)

検索値の相対位置を出す

=MATCH（検索値, 範囲(特定の一列or一行), 照合の種類(0で一致)）

=MATCH（あいう, a1:a10, 0）

VlookupをINDEXとMATCHで行う

=INDEX(A1:D8,MATCH(F6,A1:A8,0),3)

下記と同じ結果となる
=vlookup(F6,A1:D8,3,0)

INDEXとMATCHで値を取り出す

=INDEX(A1:D8,MATCH(F6,A1:A8,0),MATCH(G5,A1:D1,0))
=INDEX(検索範囲,MATCH(行の検索値,行[縦方向]範囲,0),MATCH(列の検索値,列[横方向]範囲,0))

Vlookupと異なり2列目から1列目の値も返せる

個数が17の商品名を返す
=INDEX(A1:D8,MATCH(F6,B1:B8,0),1)

##担当者別の合計額を出す

=sumif(担当者の列,検索値,合計する列)

#スプレッドシートの関数

and と or の組み合わせ

=query('シート名'!範囲:範囲,"select * where (E = '条件1' or E = '条件2') and D = '条件3'",1)

日付操作

前月1日
=TEXT(eomonth(today(),-2)+1,"YYYY-MM-DD")

前月末日
=TEXT(eomonth(today(),-1),"YYYY-MM-DD")

#数字を取り出す

##フィルタをかけて結果の合計

9は集計の種類指定SUMを表す
=SUBTOTAL(9,A2:A100)