個人的に好きな~Excel小技~

スポンサーリンク  

 

Excelで使える小技全般

Excelに限った小技じゃないかもしれませんが・・、それはお許しください。

 

F2

→入力値の編集

※文字を一部変更したいときとか、関数をコピーしたいときとか。

 

Shift押しながら、EnterとかTab

→逆の方向に移動する

※普通Enterは下、Tabは右に移動しますが、その逆に行ってくれます。

 

Ctrl+下

→次の加工済みセルへ行く。

※ここで言う”加工済みセル”は、”数値や文字列、関数を入力済みのセル”という意味です。

※その行に加工済みセルが無ければ、最終行(104万8576行目)にいきます。

 

<応用編>

加工済み、未加工済みのセルが入り混じっている状態で、一番下の加工済みセルに行きたいときは、「空いている列でCtrl+下」(一番下に行く)→「その列でCtrl+上」をします。

 

A1を選択し、Shift押しながらC5を選択

→A1:C5が範囲選択できる。

 

Alt+E → S → V

→値のみ貼り付け

※覚えてしまうと、右クリックしなくなる。

 

Ctrl+N

→新規のブックを開く

※関数をテストしたりするとき。

 

F4

→前回の作業を繰り返す

※セルの色替えとか、結合とかの作業を繰り返すとき。

 

Shift押しながらドラッグ

→行の入れ替えができる

例)セルA5を選択する→Shift押しながらA1~A2の位置にドラッグする。

※右クリックして「行の挿入」「行の削除」・・なんて操作は必要ありません。

 

Ctrlを押しながら、マウスの上下ロール

→表示の倍率が変わる

 

Ctrl押しながら、「+」ボタン

→日付出力

 

Ctrl押しながら、「*」ボタン

→時刻出力

 

Alt押しながら、図を縮小、拡大する

→セルの大きさにぴったり合わせてくれます。

 

痒い所に手が届きそうな関数

現実的にリクエスト多めな関数たちです。私も忘れちゃうので、コピペして使っています。

 

FIND系

“.”がいくつあるか確認する
=LEN(A1) – LEN(SUBSTITUTE(A1, “.”, “”))

例)
chintai.sumai.ur-net → 2

 

最初の”.”から”.go.jp”までの文字列を取り出す
=MID(B4, FIND(“.”, B4)+1, FIND(“.go.jp”,B4) – FIND(“.”,B4) -1)

例)
job.gakusei.go.jp → gakusei

 

最初の”.”が何文字目にあるのか調べる
=FIND(“.”, A1)

例)
newpay.nta.go.jp → 7

 

2回目の”.”が何文字目にあるのか調べる

=FIND(“.”, B6, FIND(“.”, B6)+1)

例)
newpay.nta.go.jp → 11

=REPLACE(A2, 6, 3,”関数”)

6文字目からの3文字「表計算」を「関数」に置き換えます。

 

REPLACE(置換)

=SUBSTITUTE(A1, “Word”, “Excel”)

例)Word Word → Excel Excel

 

SUBSTITUTE(置換)

=SUBSTITUTE(A2, “Word”, “Excel”, 2)

例)Word Word → Word Excel

※2 番目の「Word」だけ置き替わります。

 

=SUBSTITUTE(A2, “Word “, “”)

例)Word Word → Word

検索文字列「Word 」を空白文字列「””」に置き換えて削除します。

 

シート名をセルに入れたいとき

=CELL(“filename”)

使用例)
=MID(CELL(“filename”),66,10)

 

先頭を0で埋める方法

ユーザ定義で「00000」と打つ
※全5桁の場合

<その他やり方>

=text(A1,”00000″)

とか

=RIGHT(“000000″&F1,7)
※RIGHT関数は、右からxケタ数字を抜き出す。

 

全角、半角とか

全角を半角に変換する

=ASC(A1)

 

半角を全角に変換する

=JIS(A1)

 

先頭文字だけを大文字に変換する

=PROPER(A1)

 

<合わせ技>

=PROPER(JIS(A1))

=PROPER(ASC(A1))

 

セル内で改行させる

CHAR(10)

例)
=”あいう”&CHAR(10)&”えお”

 

日付+1する方法

=”2019/6/26″+1

 

=A1+1
→6/27

 

=TEXT(A1,”yyyy/m/d”)
→2019/6/26

 

=TEXT(A1,”m/d”)
→6/27

 

=TEXT(A1+1,”yyyy/m/d”)
→2019/6/27

 

=TEXT(A1+1,”mm/dd”)
→06/27

 

●今日の日付だけ取り出したいとき

=MID(TEXT(TODAY(),”yyyy/m/d”),8,2)

※今日が2019/06/26なら、26だけ取り出せる

 

=MID(TEXT(A1,”yyyy/m/d”),8,2)
※これでもよい

 

●明日の日付だけ取り出したいとき

=MID(TEXT(TODAY()+1,”yyyy/m/d”),8,2)

※今日が2019/06/26なら、27が出力される

 

=MID(TEXT(A1+1,”yyyy/m/d”),8,2)

※これでもよい

 

<補足>
A1:2019/6/26

 

日付を引き算したいとき

●年単位
=DATEDIF($A1,$B1,”y”)
例)5

●月単位
=DATEDIF($A1,$B1,”m”)
例)60(12*5)

●日単位
=DATEDIF($A1,$B1,”d”)
例)1826

●残りヵ月
=DATEDIF($A1,$B1,”ym”)
例)2015/02/01と2018/05/01だったら、「3」を返す

 

<補足>
A1:開始日
B1:ゴール日(日付を手入力してもいいし、TODAY()でもよい)

 

特定の文字列があれば1を立てる(countif、searchあたり)

<1ワードなら>

●任意の文字列

=IF(SEARCH(“*接続できない*”,A1),1,””)

 

●完全一致

=IF(SEARCH(“B1″,A1),1,””)

 

~こっちでもよい~

=IF(COUNTIF(A1,”*接続できない*”),1,””)

=IF(COUNTIF(A1,B1),1,””)

 

<2ワード以上>

●任意の文字列

=IF(COUNTIF(A1,”*接続できない*”)+COUNTIF(A1,”*接続できなかった*”)<>0,1,””)

 

もちろん、範囲の指定もできます。

=IF(COUNTIF(A1:A6,”*接続できない*”)+COUNTIF(A1:A6,”*接続できなかった*”)<>0,1,””)

 

<補足>

A1:abc接続できないxyz

B1:接続できない

 

百万単位にする方法

セルの書式設定>ユーザー定義で、「#,##0,,」と入力する

例)7,0000,00→7

 

「#,##0,」なら千単位になる

例)7,0000,00→7,000

 

■35万4500円と表示させるには?

セルの書式設定>ユーザー定義で、「以下を入力する

#”万”

####”円”

※改行を忘れずに。改行は「Ctrl+J」でできます。

 

 

以上です。

 

スポンサーリンク  

コメントを残す

メールアドレスが公開されることはありません。