【Excel】効率化!10個のおすすめの便利関数・機能まとめ

assistant すいみん

こちらのCGメソッドの記事では「【Excel】効率化!10個のおすすめの便利関数・機能まとめ」を紹介します!
この記事の著者
CGブロガー すいみん

CGブロガー

すいみん

Suimin

プロフィール

某美大の油絵科を卒業後、大手CG映像プロダクションに入社。
その後ゲーム業界に転職。
現在は技術系のCGデザイナーをしています。 書籍:Unity デザイナーズ・バイブル
プロフィール詳細はこちら

Excelの効率化!10個のおすすめの便利関数・機能まとめ

本記事ではExcelで役に立つ便利な機能をまとめました。

セルに合わせてぴったり画像を合わせる方法

ショートカットまたはマクロを使って画像をきれいにサイズを合わせる方法をまとめました。
詳細は下記の記事にて。



【Excel】セルに合わせてぴったり画像を合わせる方法

特定の文字がある時に隣のセルを塗りつぶす方法

Find関数を使います。
詳細は下記の記事にて。



【Excel】特定の文字がある時に隣のセルを塗りつぶす方法 [FIND関数]

結合セルになっている表から数字を参照するやり方

関数を駆使してなんとか参照します。
基本的には結合セルは使わないでほしいですよね。

詳細は下記の記事にて。



【Excel】結合セルになっている表から数字を参照するやり方

変更箇所を赤字で表示する方法

差分を別シートにとって変更箇所を赤文字に変更する方法の紹介です。
詳細は下記の記事にて。



【Excel】変更箇所を赤字で表示する方法

空白を飛ばして連番をつける方法

SUMPRODUCT関数を使って空白を飛ばして連番をつけます。
詳細は下記の記事にて。



【Excel】空白を飛ばして連番をつける方法 [SUMPRODUCT関数]

隣のセルに文字がある場合にアンダーバーを付ける方法

ISTEXT関数を使います。

=IF(ISTEXT(A2),"_","")

別の方法もあります、CONCATENATE関数を使います。

=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2)

※ 途中、文字列がない場合はちゃんと抜かした状態でつながれます。
※ 数字を入れたい場合は'00のようにアポストロフィーを付けてやります。

土日・祝日・指定日を除いて期日を計算する方法

納品日と工数が見えていて、いついつまでにデータくださいみたいなリストを作りたい時に土日や祝日、特定の日を除いて期日を出す方法です。

WORKDAY(開始日, 日数, 祭日)

=WORKDAY(C2,-B2,F:F)

開始日を納品日、日数を-工数、祝日をリストから取ってきています。

「完了」の文字を入れるとその行全てグレーに塗りつぶされる方法

プルダウンで完了とした場合に、その行すべてグレーに塗りつぶす方法です。

  1. 条件付き書式
  2. カスタム数式で以下のような記述
=$A1="完了"

以上で、特定の文字に反応して行がすべて塗りつぶされます。

応用例1 特定の単語が含まれている場合にその行全てグレーに塗りつぶされる方法

=(COUNTIF($A1, "完"))

応用例2 特定の単語が含まれない場合は、その行全てグレーに塗りつぶされる方法

=(COUNTIF($A1, "<>完了"))

[マクロ]各セル単位の内容をそれぞれ1つずつテキストファイルに出力する方法

  1. Alt+F11でマクロ起動
  2. 挿入→標準モジュール
  3. 下記のコードをコピペ
  4. 実行
Sub OutPutMacro()
Dim fs As Object
Dim objTxt As Object
Dim i As Long
Dim orgPath As String
orgPath = CurDir
'上書きオプション
Const OVRW As Boolean = True
'出力する場所
ChDir "C:Usersユーザー名Desktop"
Set fs = CreateObject("Scripting.FileSystemObject")
For i = 1 To Range("A65536").End(xlUp).Row
If Cells(i, 1).Value <> "" Then
'列番号[テキスト名]
Set objTxt = fs.CreateTextFile(Cells(i, 1).Text & ".txt", OVRW)
'列番号[テキストの内容]
objTxt.WriteLine (Cells(i, 2).Text)
objTxt.Close
End If
Next i
ChDir orgPath
Set fs = Nothing
MsgBox "処理が完了しました", Title:="メッセージ"
End Sub


参考
エクセルの各セルの内容をそれぞれ1つずつテキストファイルに出力する- その他(Microsoft Office) | 教えて!goo教えて!goo

エクセルデータの中身(画像)を取り出す方法

エクセル・ワード・パワーポイントのファイル形式 (.docx、.xlsx、.pptx)は実はただのZIPファイルということを最近知りました。

これをリネームして解凍するとマクロや機能を使わずとも編集することができます。

・適当に画像を入れたtest.pptxというデータを用意
・test.zipにリネーム
・右クリックから「すべて展開」で解凍

エクセル・ワード・パワーポイントの中身について

解凍したtest.pptの中身を見てみるとこんな感じです。

例えばmediaファイルの中に画像が格納されているので、ここで画像を差し替えもできますし、使われている画像だけを抽出することもできます。

エクセル・ワード・パワーポイントのデータを元に戻す方法

  • 中身をすべて選択してZIP圧縮

※ あたりまえですが一番上のフォルダ(testフォルダ)を選択ではないので注意。階層が変わると開かなくなります。

このZIP圧縮を7ZIPなど圧縮率の高い方式で圧縮するとデータを軽量化することもできます。

Excelの便利な小技まとめ

基本的なTIPSです。

左上(A1)に表示を戻す

→Ctrl+Home

保存する時の礼儀でもあります。
いらないセルをすべて消して、A1にカーソルを合わしてから保存することでデータを開いたときにわかりやすくしましょう。

文字と数値の関連付けをしたい

→=IF(A1=”あ”,1,IF(A1=”い”,2,IF(A1=”う”,3,IF(A1=”え”,4,IF(A1=”お”,5,””)))))

指定された値の倍数になるように、数値を切り上げまたは切り捨てたい

→MROUND関数

全ての数式を表示させたい

→Ctrl+Shft+@で表示

数式と目標値が決まっていて、式の変動値を逆計算したい

→データ>データツール>What-If分析>ゴールシーク

指定した条件と一致するセルの隣の数値を合計したい

→SUMIF関数

エクセルに関する参考記事・ツール・書籍のまとめ

エクセルに関する記事を下記にまとめました。



excel-summary-article


【Excel】効率化!10個のおすすめの便利関数・機能まとめ

エクセルのソフトウェア購入

エクセルの周辺機器

エクセルの参考書籍