関数を使う時にこそ”絶対参照”と”相対参照”を意識しましょうTwitterFacebookはてブPocketLINEコピー2015.09.29Excelを経理や財務の実務で使う際には、関数を使うことが多いです。関数の入力で気をつけたいのが、”絶対参照”と”相対参照”。最初は難しいかもしれませんが、使い分けられるようになると、関数の入力の効率が高まります。空白セルにコピー&ペーストまとめおまけ実務で関数をよく使うのは、コピー&ペーストで効率よくデータを処理できるからExcelで関数を使えるようになると、その柔軟性と便利さに驚くと思いますが、実務の現場で関数を使うのには、もう一つ理由があります。それは、1つのセルで入力した関数をコピー&ペーストすることで、大量のデータを処理できるから。VLOOKUP関数を使って、データベースから必要なデータを抽出することを考えます。(VLOOKUP関数で、右の「商品データベース」から、左の「売上明細」に”商品名”と”単価”を転記します)売上明細にはD4セルからE12セルまでの18の空白セルがありますが、これに一つずつ関数を入力していくことはありません。最初の1つのセルに関数を入力して、あとは空白セルにコピー&ペーストすればOK。使い方ができると、大量のデータを効率よく処理することができるので、実務の現場でも関数がよく使われるのです。コピー&ペーストを前提に関数を入力する際、必ずおさえておかなければいけないのが、”参照”の方法です。言うのも、最初に入力した関数で使われた「セル番地(「D4」「E12」のようなシート上のセルの場所を示す記号)」ですが、そのままコピー&ペーストしてしまうと、移動距離に応じて変化してしまいます。※VLOOKUP関数による具体例(D4セルに、上のようなVLOOKUP関数の式を入力。最初のC4に注目して下さい)(D4セルから隣のE4セルにコピー&ペーストした結果です。移動した距離(1つ隣に移動)に応じて、セル番号も変化してC4がD4になっています。・コピー&ペーストと連動させて「動かすセル」を考えておかなければいけません。この「動かしてはいけないセル」と「動かすセル」の区別をするのが”参照”です。実務で関数を使う場合は、この”参照”を意識して使うようにしましょう。次で、具体的な”参照”の使い方を見ていきます。”行””列”に分けて、見ながら決めるここから、具体的な参照の仕方を説明します。”参照”の決め方まず、最初に”参照”の方法をどのように決めるかを説明しておきます。・”行”と”列”に分けて考える・関数を見ながら決めるこの2つに注意しながら決めていきます。機械的に手順を覚えるよりも、実際の関数を見ながら決めていくのが確実で早いので、この方法をオススメしています。次に”参照”の種類を確認します。参照には2つの種類があります。「絶対参照」と「相対参照」です。やって用語ごとにまとめておいて言うのも何ですが、本当に押さえてもらいたいのは、赤字の部分だけ。(強調したいので大きくしてみました)これだけです。言葉の説明だけでは分かりにくいので、実際に見ておきましょう。A1セルに「=A3」と入力します。この場合、A3セルに入力されている「福山」が表示されます。次に、A1セルの「=A3」を、右隣のB1セルにコピー&ペーストしてみます。式の中のセルも「A3」から「B3」へ移動しました。これが、「コピー&ペーストしたらセルが動く」という意味です。一つ見ておきましょう。今度は、A1セルの「=A3」を右斜め下の、B2セルにコピー&ペーストしてみます。「行」も「列」も動いて、「=B4」になりました。今度は、A1セルの式を「=$A3」としてB2セルにコピー&ペーストしてみます。「=$A3」のAには”$”がついているので、コピー&ペーストしてもAのまま。「=$A3」の3には”$”がついていないので、コピー&ペーストすると動いて4に。その結果「=A4」となって、A4セルの「山本」が表示されました。最後にもう一つだけクイズを。A1セルの式を「=A$3」として、B2セルにコピー&ペーストするとどうなるでしょうか。”$”のない「A」だけが動いて「3」はそのまま。「B$3」になるので、「B3」セルの「吹石」が表示されます。・”$”のあるなしを見る・「行」と「列」に分けて見るこの2つを押さえることで、コピー&ペーストした後の関数の動きが分かるようになります。最後に、VLOOKUP関数の入力で、参照の仕方を確認します。最初に出てきた「データベース」のデータを、「売上明細」の表に、VLOOKUP関数で転記することを考えます。(VLOOKUP関数で、右の「商品データベース」から、左の「売上明細」に”商品名”と”単価”を転記します)なお、今回はVLOOKUP関数の入力がメインではないので、こちらの細かい解説は別の記事に譲ります。(こちらの記事を見てみて下さい。「VLOOKUP関数の仕組み」が参考になると思います。)この転記で使うVLOOKUP関数は、次のような式になります。=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)こうなっています。”スルー”から”愛”に変わるまで」になっていますので、VLOOKUP関数の中身は正しいことがわかります。このVLOOKUP関数には、全く”$がついていません。関数の中のセルが動いてしまうことになります。これではコピー&ペーストしても、正しく転記されなくなるので、どこに”$”をつけるべきかを判断していきます。見つけたいデータ「C4」の参照まずは、=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)最初に入力されている、「見つけたいデータ」「C4」について。今回の「見つけたいデータ」は、「売上明細」の「コード」。横には、「コード」の列(=C列)を動かしてしまうとまずいので、関数のセルは、動かしてはいけません。・行:動かす・列:動かさないように参照を決めるので、「$C4」になります。調査する範囲「I3:O12」の参照つぎに、=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)「見つけたいデータ」があるかないかを、「調査する範囲」の参照です。「見つけたいデータ」を探すのは、データベースですから、この範囲は、コピー&ペーストしたとしても動かすわけにはいきません。・行:動かさない・列:動かさないように参照を決めます。$O$12」こうなります。こちらは、MATCH関数で入力しているのですが、これを解説すると説明が冗長になってしまいますので、答えだけ示しておきます。0)このようになりました。この関数を残りの空白セルにコピー&ペーストすると、まとめ関数の入力時には”参照”を意識しましょう。関数をコピー&ペーストしたときに、「セル番地を動かすかどうか」を「行」「列」に分けて考えるのがポイントです。おまけマイナンバーのセミナーを受けてきました。今まで自分で考えたことを、実務でどう生かすかについてのアイディアを学べたのが収穫。人から教わることも大事ですね。コピー&ペーストを前提にするExcelだからこそ、相対参照と絶対参照を使えるようにしておきますExcelでの作業の一つに、関数や数式を使った分析があります。VLOOKUP関数の列番号はMATCH関数で指定するのが効率的データの検索と抽出に力を発揮するVLOOKUP関数ですが、元データが必ずしも転記先と同じ形で作られていないことがあります。そんなときはMATCH関数と組み合わせてVLOOKUP関数の式を完成させて効率よくデータを抽出しましょう。SUMIF関数を使って、取引記録から重要な経費科目の金額を集計してみます会計ソフトやExcelファイルで記録した、日々の取引の記録を使うと、経費などの分析がラクにできます。SUMIF関数を使って、月ごとの変動が分かる一覧表を作ってみます。シートに出てくる気になる点線を削除しますExcelで作業していると何かの拍子に出てくる、謎の点線。枠線なら見慣れているので問題ないのですが、こう言った慣れない点線が現れると気になってストレスになります。不要なものは削除して、作業効率を落とさないようにしましょう。シートで点線が...ExcelMacでエクセルのショートカットを再現してみましょうMacでエクセルを動かしていると、Windowsではできたショートカットで使えないものが出てきます。Proを使っていますが、同じMAC上でExcelも動かしています。その際に困るのが、Excelのショートカットで使えないものがあること。この問題を解消していきます。東上野で税理士事務所タカジムを経営しています。公認会計士として監査法人に勤務し、上場企業の監査を担当。側面から会社の「利益」と「お金」を守るためのサポートしています。おっと、それから、監査、経理の現場でExcelを上手く使えず悔しい思いをした経験から(今でも当時の感情が蘇るくらい悔しかったみたいですよ)、”実務で使えるExcel”を伝えることにも力を入れています。東上野で税理士事務所タカジムを経営しています。公認会計士として監査法人に勤務し、上場企業の監査を担当。側面から会社の「利益」と「お金」を守るためのサポートしています。おっと、それから、監査、経理の現場でExcelを上手く使えず悔しい思いをした経験から(今でも当時の感情が蘇るくらい悔しかったみたいですよ)、”実務で使えるExcel”を伝えることにも力を入れています。lp=this.data.ListPrice,la=Number(lp.Amount),os=this.data.OfferSummary||{},sp=os.LowestNewPrice||{},sa=Number(sp.Amount);return(!isNaN(sa)&&saタイトルとURLをコピーしました。