データ入力の効率化 ~履歴を使って入力

『履歴を使ってすばやく入力』

すでに入力したことのある文字列を再び入力するときは

[Alt]キーを押しながら、[↓]キーを押す。

すると、入力中のセルと同じ列内に入力された、文字列の履歴が表示されるので、選択してEnter。

データ入力の効率化2 ~項目をリスト化してプルダウン入力

『項目をリスト化してプルダウン入力する』

決められたデータを入力する時、入力候補の一覧からプルダウンで項目を選ぶようにすると、入力ミスをなくすことができます。

1,リスト入力したい範囲を選択
2,[データ]タブ[データツール]グループにある[データの入力規則]ボタンクリック
3,[設定]タブで[入力値の種類]爛の[▼]ボタンから[リスト]を選択
4,[元の値]欄にリスト項目をカンマで区切って入力してOK


ちょっとした応用
別のシートに作成した入力候補で、リストを作成
リスト入力したい項目を別シートに作成して範囲選択→その範囲に名前を付ける→リスト入力したい範囲を選択→[データの入力規則]ボタンクリック→[設定]タブ[入力値の種類]欄の[▼]ボタンから[リスト]を選択→[元の値]欄に[=]とセル範囲に付けた名前を入力しOK

同じシート内に作成した入力候補でリストを作成
リスト入力したい範囲を選択→[データの入力規則]ボタンクリック→[設定]タブ[入力値の種類]爛の[▼]ボタンから[リスト]選択→[元の値]欄にカーソルを置き、リストにする項目のセルをドラッグしてOK

数式と関数21 ~1行おきにセルに着色する

『1行おきにセルに着色する』

1行おきにセルに着色すると単純な表がすごく見やすくなります。
1行ずつ着色していくことは可能ですが、時間がかかりますし、行の並べ替え、削除などをするとすぐに乱れてしまいます。
MOD関数とROW関数を使うと楽に着色できます。

1,1行おきに着色したい範囲を全て選択
2,[ホーム]タブクリックし[スタイル]グループの[条件付書式]をクリック
3,[新しいルール]をクリック
4,[新しい書式ルール]ダイアログボックス内[ルールの種類を選択してください]で数式を使用して[書式設定するセルを決定]を選択
5,[ルールの内容を編集してください]で[次の数式を満たす場合に値を書式設定]に=MOD(ROW(),2)=0を入力
6,[書式]→[セルの書式設定」から色を選択

MOD関数は割り算の余りを求める関数。例えばセルに「=MOD(10,3)」と入力すれば戻り値として「1」を返します。
ROW関数は行番号を調べる関数。例えば「ROW(D3)」と入力すれば戻り値は「3」になります。「ROW(A3:B5)」と範囲を指定した場合は範囲の左上のセル「3」が戻り値になります。


「=MOD(ROW(),2)=0」は「行番号を2で割った余りが0」(偶数行)の場合に書式が適用される。
「=MOD(ROW(),3)=0」とすれば3行おきに着色される。

ウィンドウ4 ~もろもろを非表示

『セルの枠線を非表示にする』
・[表示]タブの[表示/非表示]グループにある[枠線]をオフにする


『ブックを非表示にする』

・[表示]タブの[ウィンドウ]グループにある[ウィンドウを表示しない]ボタンをクリック
[再表示]ボタンをクリックすると再表示

多数のブックを表示している場合はかなり使えます。


『特定のシートを非表示にする』

・非表示にしたいシート見出しを右クリックし、ショートカットメニューから[非表示]を選択
任意のシート見出しを右クリックし[再表示]で再表示される。


『行、列を非表示にする』

・非表示したい行列を選択し、右クリックからショートカットメニュー[非表示]を選択。
非表示にした行列が含まれるように行列を選択し、右クリックから[再表示]で再表示される。


『リボンを一時的に非表示にする』
・アクティブになっているタブをダブルクリック。

画面をより広く使いたい時に使えます。


『シート見出しを非表示にする』

・[Office]ボタンをクリック→[Excelのオプション]をクリック→[Excelのオプション]ダイアログボックス内左側で[詳細設定]を選択し、右側で[次のブックで作業する時の表示設定]で[シート見出しを表示する]をオフにして[OK]

シート見出し上で右クリックし[非表示]を選択するとそのシートだけを非表示に出来る。

ウィンドウ3 ~範囲に名前をつけて、すぐに選択できるようにする

『範囲に名前をつけて、すぐに選択できるようにする』

知らなくても問題ないけど、知ってると何かと使える技。
同じ形式の表を使ったシートを多く作る場合、フォーマットにはそれぞれの範囲に名前をつけておくと便利です。

1,名前をつけたい範囲を選択
2,[名前ボックス]にその範囲の名前を入力しEnter
3,どのセルをクリックしている状態でも、[名前ボックス]の▼をクリックすると登録している範囲名が表示され、それを選択すると指定の範囲が選択されている状態になる。


名前をつけたい範囲を選択し、右クリック→[範囲に名前を付ける]を選択しても名前を付けられる。

数式と関数20 ~指定の数だけ文字を抜き出す

『指定の数だけ文字を抜き出す』
頭文字だけ表示したい。1番目の値を抜いて2番目の値から表示したい。
そんなときはMID関数を使います。

1,結果を表示したいセルをクリック
2,数式バーの[関数の挿入]クリック
3,[関数の挿入]ダイアログボックス内[関数の分類]で[文字列操作]を選択し、[関数名]で[MID]を選択しOK
4,[関数の引数]ダイアログボックス内[文字列]にカーソルを置いて対象セルをクリック
5,[開始位置]に左から何文字目から抜き出すか[文字数]にそこから何文字抜き出すかを数字を入力しOK


数式と関数19 ~複数のセルにある文字列をまとめる

『複数のセルにある文字列をまとめる』

仕事でこの手の作業をすることはありますが、その際はcsvデータでExcelで処理できる件数超えている場合が多いので、正直使う場面があまり思い浮かばないのですが・・。
CONATENATE関数を使います。

1,結果を表示するセルをクリック
2,数式バー[関数の挿入]ボタンをクリック
3,[関数の挿入]ダイアログボックス内[関数の分類]で[文字列操作]を選択[関数名]で[CONCARWNATE]を選択してOK
4,[関数の引数]ダイアログボックス内[文字列1]にカーソルを置いて結合したい一方のセル、[文字列2]にカーソルを置いてもう一方のセルをクリックしOK

この関数は「&」で代用できます。使うとしても「&」を使って他の関数と組み合わせて使うことになりそうです。

数式と関数18 ~同じ文字列を繰り返す

『指定した同じ文字列を指定した回数だけ繰り返して表示する』

トップを取った回数の横セルに◎をその回数分表示したい。
そんなときにはREPT関数を使います。

1,結果を表示したいセルをクリック
2,数式バー[関数の挿入]ボタンクリック
3,[関数の挿入]ダイアログボックス内[関数の分類]で[文字列操作]を選択[関数名]で[REPT]を選択しOK
4,[関数の引数]ダイアログボックス内[文字列]に繰り返したい文字列を入力
5,[繰り返し回数]に繰り返す回数を数値で入力。参照セルがある場合はセルを指定しOK



数式と関数17 ~指定した順位の値を求める

『指定した順位の値を求める』

1番大きな数値を示しているセルの値を取り出したり、ある順位にある値を抜き出したい時に使います。
そんな時はLARGE関数を使います。
1番大きい(いい)値か1番小さい(悪い)値ぐらいしか抜き出すことはない気がしますが、大きいほうから何番目の値でも抜き出すことは出来ます。トップ3を抜き出したりとかに使えますね。

1,結果を表示させるセルをクリック
2,数式バー[関数の挿入]ボタンクリック
3,[関数の挿入]ダイアログボックス[関数の分類]で[統計]を選択[関数名]で[LARGE]を選択してOK
4,[関数の引数]ダイアロボックス内[配列]にカーソルを置いて対象範囲をドラッグ
5,[順位]に表示したい順位を入力しOK

小さいほうから何番目かを表示する場合はSMALL関数を使います。


数式と関数16 ~順位を求める

『順位を求める』
RANK関数を利用します。

1,結果を表示するセルクリック
2,数式バー[関数の挿入]クリック
3,[関数の挿入]ダイアログボックス内[関数の分類]で[統計]を選択。[関数名]で[RANK]を選択OK
4,[関数の引数]ダイアログボックスが表示されたら[数値]にカーソルを置いて、順位の基準となるセルをクリック
5,[参照]にカーソルを置いて、順位の基準となるセル範囲をドラッグして選択し、F4を押して絶対参照にする。
6,[順序]に[0]を入力しOK

手順6で0か指定を省略すると降順の順位が表示され、0以外を指定すると昇順の順位が表示されます。

数式と関数15 ~ふりがなをつける

『ふりがなを取り出す』

指定した範囲のふりがなを取り出します。
PHONETIC関数を使います。

1,結果を表示するセルクリック
2,[関数の挿入]クリック→[関数の挿入]ダイアログボックス[関数の分類]で[情報]を選択[関数名]で[PHONETIC]を選択してOK
3,[関数の引数]ダイアログボックスの[参照]にカーソルを置いて、ふりがなを取り出したいセル範囲をドラッグして選択OK

ちょっとした応用
直接エクセルに入力した文字以外(コピペだったり)の文字のふりがなを抽出しようと上記の関数を使うと、漢字は漢字のまま表示されてしまいます。
その場合は関数が参照しているセル(ふりがなを抽出したいセル)を選択し、Shift+Alt+↑でふりがなを修正していくと、ふりがなの方も修正されます。

ふりがなをカタカナ⇔ひらがなに変換するには、ふりがなを抽出したいセルをドラッグで選択し、[ホーム]タブ内[ふりがなの表示/非表示]の[ふりがなの設定]から変換します。

数式と関数14 ~表から条件に合ったデータを取り出す

『表から条件に合ったデータを取り出す』

社員データが入力されているリストや表のデータを検索し、条件に合ったデータを取り出したい時などに利用できます。
VLOOKUP関数を使います。

社員データ表でIDを調べ、そのIDに対応する社員名を自動的に調べて入力する場合
1,社員名を表示したいセルをクリック
2,数式バー[関数の挿入]クリック
3,[関数の挿入]ダイアログボックス内[関数の分類]で[検索/行列]を選択[関数名]で[VLOOKUP]を選択肢し[OK]
4,[関数の引数]ダイアログボックス[検索値]にカーソルを置いて社員IDが入力されているセルをクリック[範囲]にカーソルを置いて、出来上がっているほうのリスト、表の全体をドラッグし、F4キーで絶対参照にする。
5,[列番号]に範囲内で表示するデータが入力されている列を、左からの列数で指定
6,[検索方法]に0を入力しOKクリック

VLOOKUPのVはヴァーティカルの頭文字で「垂直」の意味。指定した数値を列方向に検索するということです。
間作する値が引数の[範囲]で指定した表の左端の列に入力されている必要があります。
左端の列で一致するデータを検索し、その行にある指定した列番号の値を取り出します。

数式と関数13 ~2つの日付の間の期間を求める

『2つの日付の間の期間を求める』

入社年月日と現在の日付から勤続年数を求める時などに利用できます。
DATEDIF関数を使います。

1,結果を表示するセルをクリック
2,=DATEDIF(開始日付セル,終了日セル,"単位")を入力

この関数は[関数の挿入]ダイアログボックスからの入力は出来ません。
単位の部分は["]でくくります。
単位
Y:期間内の年数
M:期間内の月数
D: 期間内の日数
MD:年、月を無視した日数
YM:年、日を無視した月数
YD:月、日を無視した日数

ちょっとした応用
年と月を両方表示する場合はDATEDIF関数を2つつなげます。
入社年月日と現在の日付から勤続年数を求める、セルA1に今の年月日、E列に入社年月日が入力されていて、F列に勤続年数を表示する場合は、
F列のセルをクリックし(ここではF3。入社年月日がE3)、=DATEDIF(A1,$E$3,"Y")&"年"&DATEDIF(A1,$E$3,"YM")&"ヶ
月"
と入力すると、~年~ヶ月と表示されます。

数式と関数12 ~今日の日付を求める

『今日の日付を求める』

TODAY関数を使います。

1,日付を表示するセルをクリック
2,数式バー[関数の挿入]ダイアログボックス内、[関数の分類]で[日付/時刻]を選択。
3,[関数名]で[TODAY]を選択→OKクリック
4,[関数の引数]ダイアログボックスOKクリック

ちょっとした応用

F9キーで最新の日付に更新。
現在の日付はCtrl+;でも表示させられます。

Excelでの日付・時刻の計算は「シリアル値」という数値で行われます。
時刻を合わせたシリアル値を表示する場合は、NOW関数を使う。

さらに応用

予定表などで「今日」だけに色をつけ、目立つようにする場合は
範囲の選択(表であれば表全体)→[ホーム]タブ→[スタイル][条件付き書式]→[新しいルール]→[新しい書式ルール]ダイアログボックス[ルールの種類を選択してください]で[数式を使用して、書式設定するセルを決定を選択→[ルールの内容を編集してください]で=TODAY()=$A2を入力(選択したセルの全ての行で列Aを参照させるので、絶対参照)→書式から色等を選択


書式設定8 ~全角半角を揃える

WEB関係の仕事をしている人は、全角英数字を使用しないといったりしますが・・・。
恥をかかないためにも。

『全角半角を揃える』

記事リンク

キー操作1つで表全体を一気に選択

縦書き文書に合わせて右から左へと表作成したい

データの入っていない“空白セル”にキー操作で一発ジャンプ

“男女別の平均点”など,条件に合うデータだけを抽出して平均を求める【DAVERAGE関数】

ウィンドウ2 ~編集を禁止にする

『編集を禁止にする』

複数人で回覧するときに勝手に数値をいじられないようにロックをかけることができます。

1,[ツール]→[保護]→[シートの保護]をクリック
2,[シートの保護]でパスワードを入力

※俺も今まで何度かあるのですが、このパスワード忘れると解除できなくなるため、注意必要。ロックかかっていて開けようとする人は少ないと思うので、自分の名前をもじったものを常に使うとか毎度同じパスワードにすることをお勧めします。

[ツール]→[保護]→[シート保護の解除]をクリックし、パスワードを入力すると解除できます。

数式と関数12 ~分岐する処理を増やす

『分岐する処理を増やす』

関数の引数に別の関数を指定することを「ネスト」と言います。
例えば評価づけを『〇』『×』にするときは、IF関数を使いますが『〇』『△』『×』の3段階の評価を行う場合はIF関数をネストします。

1,結果を表示するセルをクリック
2,[関数の挿入]ダイアログボックス内[関数の分類]で[論理]、[関数名]で[IF]を選択。
3,[関数の引数]ダイアログボックス内[論理式]に式を入力し、[真の場合]に条件入力、[偽の場合]にカーソルをおき、[関数ボックス]の[▼]をクリックし再度[IF]を選択
4,再度[関数の引数]ダイアログボックス[論理式]等を入力

※他の連続するセルでも同様の処理をする場合は、結果を表示したセルのフィルハンドルをダブルクリック。

数式と関数11 ~条件によって異なる処理をする

『条件によって異なる処理をする』

IF関数を使います。

1,結果を表示するセルをクリック
2,[関数の挿入]ダイアログボックス内[関数の分類]で[論理]、[関数名]で[IF]を選択
3,[論理式]に数式を、[真の場合][偽の場合]に表示される文字等をそれぞれ入力

※[論理式]にはTRUEかFALSEの基準になる式を入力。
=(等号)
>(~より大きい)
<(~より小さい)
>=(~以上)
<=(~以下)
<>(不等号)
対象セルがC3で、90以上を真にする場合の論理式は[C3>=90]と入力。

数式と関数10 ~数値の切り捨て

『数値の切り捨て』

ROUNDDOWN関数を使います。

1,結果を表示するセルをクリック
2,[関数の挿入]ダイアログボックス内[関数の分類]で[数学/三角]を選択、[関数名]で[ROUNDDOWN]を選択
3,[関数の引数]ダイアログボックス[数値]にカーソルを置いて対象セルをクリック
4,桁数を入力(0なら小数点以下が切り捨てられる)

ちょっとした応用
切り捨ての関数には、他にINT関数(小数点以下を切り捨てて整数にする)TRUNC関数(小数部を切り捨てるが、桁数を指定できる)
  • ライブドアブログ