1ヶ月でWebデザイン・プログラミングスキルをオーダーメイドのカリキュラムで学べるスクール、それがWebCamp。

マクロ不要でここまで出来る!仕事に役立つエクセル関数テンプレート

Pocket

Excelの標準関数は、バージョンが上がる毎にどんどん進化しています。以前はVBAを組まなければ実現出来なかった処理が、最新版では標準関数ひとつで簡単に実装出来る様になっていることも。

標準関数の総数は、2013年バージョンで既に450個以上あります。そのすべてを覚える必要はありませんが、代表的なものをいくつか使いこなせる様になっておけば、仕事の効率は大幅にアップするはず!

そこで今回は、実際の業務で使えるExcel関数のテンプレートをいくつかご紹介します。

作業中ファイルのフルパス・ファイル名のみ・パスのみ を取得する

現在、自分が開いているExcelファイルの置き場所や、ファイル名を取得する方法です。
基本的には全て問題なく動作しますが、フォルダ名やファイル名に「[ ]」が含まれている場合は正しいパスを取得出来ないので注意してください。

フルパス

=SUBSTITUTE(SUBSTITUTE(LEFT(CELL(“FILENAME”),FIND(“]”,CELL(“FILENAME”))),”[“,””),”]”,””)
上の関数を実行すると、「C:\Users\Owner\Desktop\テスト.xlsx」といったファイルのフルパスが表示されます。

CELL(“FILENAME”)だけでもフルパスを取得することは可能です。しかし、これだけでは「C:\Users\Owner\Desktop\[テスト.xlsx]Sheet1」という結果が返ってきてしまうため、ここからさらに、余分な「[ ]」やシート名などを削除する必要があります。

そこで、まずはLEFT関数とFIND関数を使って、「]」までの文字列を取得し、シート名を切り離します。続いて、SUBSTITUTE関数(1回目)で「[」を削除し、再度SUBSTITUTE関数を使って「]」を削除するのです。

  • LEFT関数:特定の文字列を、左側から指定した文字数分だけ抜き出す。
  • FIND関数:指定した文字が左側から数えて何文字目にあるのかを調べる関数。
  • SUBSTITUTE関数:指定した文字列を置換する。

パスのみ

=LEFT(CELL(“FILENAME”),FIND(“[“,CELL(“FILENAME”))-2)
フルパスではなく、パス部分だけを取得したい場合はこれだけでOK。「C:\Users\Owner\Desktop」という結果が返ります。

CELL(“FILENAME”)で「C:\Users\Owner\Desktop\[テスト.xlsx]Sheet1」というフルパスを取得し、LEFT関数とFIND関数で「\[」よりも左側にある文字だけを抜き出しています。

ファイル名のみ

=REPLACE(LEFT(CELL(“FILENAME”),FIND(“]”,CELL(“FILENAME”))-1),1,FIND(“[“,CELL(“FILENAME”)),””)
CELL(“FILENAME”)の結果「C:\Users\Owner\Desktop\[テスト.xlsx]Sheet1」から、「[ ]」で囲まれた部分(ファイル名)だけを抜き出しています。

シート名を取得する

=MID(CELL(“FILENAME”),FIND(“]”,CELL(“FILENAME”))+1,LEN(CELL(“FILENAME”)))
先ほど登場したCELL(“FILENAME”)を使用すれば、現在のシート名も簡単に取得可能です。上の処理では、CELL(“FILENAME”)の結果「C:\Users\Owner\Desktop\[テスト.xlsx]Sheet1」から、最後の「]」以降(シート名)をMID関数で切り出しています。

MID関数:特定の文字列を、指定された場所から指定された文字数分だけ抜き出す。

空行を挟んで連番を振る

※開始セルがA1の場合
=MAX($A$1:OFFSET(INDIRECT(ADDRESS(ROW()-1,COLUMN())),0,1))+1
まず、A1セルに連番(連続番号)の最初の数字を入力してください。A2セル以降に上の関数をコピペしていくと、簡単に連番を振ることが出来ます。連番は動的に計算されるため、途中で行を追加・削除したり、空行を作ったりした場合でも、飛び番(欠番)となることがありません。

  • ADDRESS関数:指定した行番号と列番号から、セルの参照を文字列で返す。例えば、=ADRESS(1,1)を設定した場合は、「$A$1」という結果が返ってくる。
  • ROW関数:指定したセルの行番号を取得する。何も指定しなかった場合は、自分自身の行番号が返ってくる。
  • COLUMN関数:指定したセルの列番号を取得する。何も指定しなかった場合は、自分自身の列番号が返ってくる。
  • INDIRECT関数:指定されたセルに入力されている文字列を介して、他のセルを参照する(C言語などのポインタの様なイメージ)。
  • OFFSET関数:基準となるセルから、指定した行数分・列数分だけずらした先のセルを参照する。例えば、A1セルに=OFFSET(A1,2,3)と入力した場合は、D3セル(A1セルから2行+3列目に位置するセル)を参照する。
  • MAX関数:指定した範囲内の最大値を取得する関数。

同じ番号がx個ずつ続く連番を振りたい

※開始セルがA1で、同じ番号を3つずつ振りたい場合
=ROUNDUP(ROW($A1)/3,0)
「1,2,3,4…」という連番ではなく、「1,1,1,2,2,2,3,3,3,4,4,4…」といった連番を振る方法です。一見、分かりにくいですが、A1セルから順に= ROW($A1)/3をコピペしていくと動きを理解出来ます。

A 622

セル = ROW(セル)/3 の結果 =ROUNDUP() の結果(小数点第1位を切り上げ)
A1 0.333 1
A 2 0.666 1
A 3 1 1
A 4 1.333 2
A 5 1.666 2
A 7 2.333 3
  • ROUNDUP関数:指定した桁数で切り上げを行う。

郵便番号から住所を取得する

※Excel2013以降で使用出来る技です。
※A1に郵便番号が入力されている場合
=CONCATENATE(FILTERXML(WEBSERVICE
(“http://zip.cgis.biz/xml/zip.php?zn=”&SUBSTITUTE($A1,”-“,””)),”/ZIP_result/ADDRESS_value/value[@state]/@state”),FILTERXML(WEBSERVICE
(“http://zip.cgis.biz/xml/zip.php?zn=”&SUBSTITUTE($A1,”-“,””)),”/ZIP_result/ADDRESS_value/value[@city]/@city”),FILTERXML(WEBSERVICE
(“http://zip.cgis.biz/xml/zip.php?zn=”&SUBSTITUTE($A1,”-“,””)),”/ZIP_result/
ADDRESS_value/value[@address]/@address”))

使い方

A1セルに郵便番号を入力し、B1セルに上の関数をコピペしてください。郵便番号のハイフンは、アリ・無し両方に対応済みです。例えば、A1セルに「104-0061」と入力した場合、B1セルには「東京都中央区銀座」と表示されます。

解説

Excel2013ではWeb APIを操作出来る関数が追加されており、これまでJSONやVBA無しでは実現出来なかったWeb上の情報取得が可能となりました。

上の例では郵便番号API(http://zip.cgis.biz/)にアクセスして郵便番号検索を行っていますが、WEBSERVICE関数のURLを変えれば、SNSの更新情報や、特定サイトのhtmlソースを取得することも出来ます。例えば、「=FILTERXML(WEBSERVICE(“http://wikipedia.simpleapi.net/api?output=xml&keyword=” & ENCODEURL(“Microsoft Excel“)),”/results/result/body”)」を実行すると、Wikipediaに掲載されている「Microsoft Excel」の解説文が表示されます。

  • WEBSERVICE関数:指定したURLから情報を取得する。
  • FILTERXML関数:指定されたパスに従って、xml上の特定データを返却する。
  • CONCATENATE関数:指定された文字列を結合する。

【おすすめ記事】

業務効率をアップする!コピペで動くVBA5選
エンジニアにおすすめ!とにかく捗るChrome拡張機能11選
必ず作業スピードを飛躍させるエディタでのテクニックまとめ!

投稿者:プロスタ編集部

  • このエントリーをはてなブックマークに追加
  • follow us in feedly

おすすめスクール

全国30校舎で展開されるヒューマンアカデミーのWeb講座は修了者数4万人超、講師とカリキュラムの満足度がどちらも9割を超えています。就職や転職に必要なレベルの実務スキルを身に付けられます。

初心者がプログラミングで挫折しない学習方法を無料動画で公開中。オンラインに特化したプログラミングスクール「TechAcademy(テックアカデミー)」が解説。

この記事が気に入ったら
いいね!しよう

プロスタの最新情報をお届けします

あわせて読みたい

関連記事

イチ押しランキング

1リナックスアカデミー

リナックスアカデミー

2システムアーキテクチュアナレッジ

システムアーキテクチュアナレッジ
ページ上部へ戻る