[24/03/27] Excel の「スピル機能」を使った表記や計算方法

■◇◇■ 週刊なにしろパソコン ■◇◇■ 毎週水曜日発行 ■◇◇◇■
  パソコン・インターネットを   発行日:2024/03/27(配信は翌日)
  楽しむキッカケを提供します!  発行者:なにしろパソコン編集長
  https://www.724685.com/maga/  連絡先:faq@724685.com
■◇◇◇■ 1,115号 ■◇◇◇◇■ 今週の読者数: 3,516名 ■◇◇◇■

 ≪今週の目次≫
  (1) 今週のひとこと Excel の「スピル機能」に関するまとめ
  (2) 今週のQ&A  Excel の「スピル機能」を使った表記や計算方法
  (3) 今週の用語解説 「SORT関数」と「SORTBY関数」の違いとは
  (4) 編集後記    例題としてリアルな一覧表を使ってみた
  ※直接ジャンプ! 今週のQ&A今週の用語解説編集後記
 ☆ 配信中止ご希望の方は以下の「まぐまぐ!」の公式ページで「登録
   する」の下にある「メールマガジンを解除」から解除できます。
            → https://www.mag2.com/m/0000081405.html

  ・ すべてのバックナンバー https://www.724685.com/maga/
  ・ パソコントラブルQ&A https://www.724685.com/weekly/
  ・ パソコン用語解説    https://www.724685.com/word/
  ・ お問い合わせフォーム  https://www.724685.com/ask/


=========================== [Top に戻る]
 (1) 今週のひとこと Excel の「スピル機能」に関するまとめ
----------------------------------
      ☆「今日のひとこと」はこちらで https://www.724685.com/

 先々週から Excel の「スピル機能」を紹介しています。

 ◆ エクセル(Excel)の「スピル機能」とは(2024/03/13)
  https://www.724685.com/word/wd240313.htm

 さらに、この「スピル機能」を活用した関数も取り上げました。

 ◆ 「XLOOKUP関数」が「VLOOKUP関数」より便利な点
  https://www.724685.com/weekly/qa240313.htm
 
 ◆ エクセル(Excel)の「SORT関数」とは
  https://www.724685.com/word/wd240320.htm

 ◆ エクセル(Excel)の「UNIQUE関数」でできること
  https://www.724685.com/weekly/qa240320.htm

 これらを紹介していると、関数なしでも「スピル機能」を使った表記や
 計算方法に便利なものがあると気付いたので、今週はそれを紹介します。


=========================== [Top に戻る]
 (2) 今週のQ&A Excel の「スピル機能」を使った表記や計算方法
----------------------------------
    ☆「パソコントラブルQ&A」 https://www.724685.com/weekly/

 Q:関数を使わなくても Excel の「スピル機能」を活用できますか?

 A:シンプルに配列や範囲を入力したり、そこに簡単な四則計算を追加
   したりするだけでも「スピル機能」を利用することができます。


                ◆◇◆

 Excel で、ひとつのセルに入力した数式の結果が複数ある場合、複数の
 値を入力したセルから順番に隣接した複数セルに流し込むように並べて
 表示(配置)できる機能として「スピル機能」を紹介しました。

 ◆ エクセル(Excel)の「スピル機能」とは
  https://www.724685.com/word/wd240313.htm

 この「スピル機能」による表記や計算方法を簡単に紹介しましょう。


 1.元の表に連動した同じ表を複製

  たとえば以下のような表があるとします。

    A B C
  1 10 30 50
  2 20 50 80
  3 30 70 90

  すると「E1」に「=A1:C3」と入力すると一発で複製できます。

    A B C D E F G
  1 10 30 50   10 30 50
  2 20 50 80   20 50 80
  3 30 70 90   30 70 90

  つまり「E1:G3」の範囲に「A1:C3」の範囲にある値が流し込まれて
  (スピルされて)表が複製されますが、この状態で「B2」に「60」
  を入力すると「F2」の値も連動して「60」になります。

  もちろん「E1」に「=」を入力した状態で「A1」から「C3」まで斜め
  にドラッグして選択することで「=A1:C3」は簡単に入力できます。

  元は表でなくても 1行だけ、1列だけでも同じように複製できます。


 2.ひとつのセルの値を参照して使った計算で絶対参照が不要

  たとえば価格一覧に消費税を掛けた値を表示するとしましょう。

     A  B  C  D
  1 商品 税抜 税込 税率
  2  1   500  550  10%
  3  2   750  825
  4  3   900  990

  従来は「C2」に「=B2*(1+$D$2)」と入力して、税率の値を絶対参照
  で「$D$2」で指定して「550」となった状態で「C2」から「C4」まで
  「オートフィル」することで「C3」「C4」を表示させていました。

  ◆ エクセル(Excel)でセルの「絶対参照」「相対参照」とは
   https://www.724685.com/word/wd140910.htm

  ◆ エクセルで「オートフィル」「オートフィルハンドル」とは
   https://www.724685.com/word/wd141210.htm

  それが「スピル機能」なら「C2」に「=B2:B4*(1+D2)」と入力すれば
  絶対参照もオートフィルも使わずに「C3」「C4」を表示できます。


 3.「スピル範囲」を「#(スピル範囲演算子)」で表現

  たとえば上記「1」の例で「E1:G3」に「スピル機能」を使って複製
  した表は「I1」に「=E1#」と入力するだけで複製できます。

    A B C D E F G H I J K L M
  1 10 30 50   10 30 50   10 30 50   430
  2 20 50 80   20 50 80   20 50 80
  3 30 70 90   30 70 90   30 70 90

  つまり「E1」に「=A1:C3」を入力することでセルに値が流し込まれた
  (スピルされた)「E1:G3」という「スピル範囲」は「E1」の後ろに
  「#(スピル範囲演算子)」を付けた「E1#」で表現できるのです。

  応用として「M1」に「=SUM(E1#)」を入力すると「E1:G3」という範囲
  のすべてのセルを足し合わせた数値「430」が表示されます。


                ◆◇◆

 また「スピル配列」は中括弧 {} の中に数値を並べることでも表現でき、
 「行」か「列」かは値と値の間を「,(カンマ)」か「;(セミコロン)」
 かで表現できます。

 たとえば「A1」に「={10,30,50}」と入力すると

    A B C
  1 10 30 50

 とスピルされ、「={10;20;30}」と入力すると

    A
  1 10
  2 20
  3 30

 とスピルされ、「={10,30,50;20,50,80;30,70,90}」と入力すると

    A B C
  1 10 30 50
  2 20 50 80
  3 30 70 90

 となり、ひとつのセルに入力するだけで表を表現することもできます。


=========================== [Top に戻る]
 (3) 今週の用語解説 「SORT関数」と「SORTBY関数」の違いとは
----------------------------------
         ☆「パソコン用語解説」 https://www.724685.com/word/

 ・「SORT」と「SORTBY」は共に指定範囲/配列の値を並べ替える関数で、
  並べ替えの基準となる行または列が「SORT関数」は指定範囲/配列内
  にあり、「SORTBY関数」は指定範囲/配列内外で複数指定できます。

  「SORT関数」は並べ替えの基準となる行または列が、指定範囲/配列
  に含まれており、先頭行または先頭列でない場合には、何行目または
  何列目を基準にするかを示す数値を指定することもできます。
  
  ◆ エクセル(Excel)の「SORT関数」とは
   https://www.724685.com/word/wd240320.htm

  一方の「SORTBY関数」は並べ替えの基準は数値ではなく、配列/範囲
  で指定しますが、その配列/範囲は並べ替える指定範囲/配列内でも
  外でも指定でき、さらに複数回の並び替えを実行することも可能です。


 ・たとえば以下のような「コーヒー豆の生産量」の表があった場合、
 
     A   B     C   D
  1 順位  国名    地域  銘柄数
  2  1 ブラジル   南米   22
  3  2 ベトナム   アジア   8
  4  3 インドネシア アジア  19
  5  4 コロンビア  南米   16
  6  5 エチオピア  アフリカ 14

  飲んだ銘柄数の多い順(D列を基準)で並べ替えるには

   「SORT関数」だと =SORT(A2:D6,4,-1)

  「SORTBY関数」だと =SORTBY(A2:D6,D2:D6,-1)

  となり、「A8」に入力すると結果は同じになります。

     A   B     C   D
  8 順位  国名    地域  銘柄数
  9  1 ブラジル   南米   22
  10  3 インドネシア アジア  19
  11  4 コロンビア  南米   16
  12  5 エチオピア  アフリカ 14
  13  2 ベトナム   アジア   8


  「SORTBY関数」なら、最初に「地域」、次に「順位」、というように
  複数回の並び替えも可能です。「A8」に

  =SORTBY(A2:D6,C2:C6,-1,A2:A6,1)

  と入力すると結果は以下のようになります。

     A   B     C   D
  8 順位  国名    地域  銘柄数
  9  1 ブラジル   南米   22
  10  4 コロンビア  南米   16
  11  5 エチオピア  アフリカ 14
  12  2 ベトナム   アジア   8
  13  3 インドネシア アジア  19

  これで「地域」別での順位が分かります。


 ・「SORTBY関数」の構文は以下のようになります。

  =SORTBY(array, by_array1, [sort_order1],
   [by_array2, sort_order2], ...)

  [] で囲まれた引数は省略可能で、それぞれの役割は

  ・array で並べ替えの対象となる配列または範囲を指定
  ・by_array1 で並べ替えの基準となる配列または範囲を指定
  ・[sort_order1] で「1」で「昇順」、「-1」で「降順」に指定
  ・[by_array2] で並べ替えの基準となる配列または範囲を指定
  ・[sort_order2] で「1」で「昇順」、「-1」で「降順」に指定

  となります。

  ◆ SORTBY 関数 - Microsoft サポート
   https://go.724685.com/3vzqf9l


  ちなみに「SORT関数」の構文は

  =SORT(array,[sort_index],[sort_order],[by_col])

  であり「SORTBY関数」にはない [by_col] がありました。

  要するに「SORTBY関数」は by_array1 で並べ替えの基準となる配列
  または範囲を指定しているので、基準になるのは「行」なのか「列」
  なのかが分かるので [by_col] は必要ないということです。


============ ◇ 編集後記 ◇ =======[Top に戻る]

 「SORT関数」と「SORTBY関数」の違いを示す例題として、私が作成した
 「コーヒー豆 生産国一覧&ランキング」の上位 5か国分を使いました。
 実在しない仮想の表よりもリアルな感じが出せると思ったためです。

 ◆ コーヒー豆 生産国一覧&ランキング | コーヒー好き.com
  https://coffeezuki.com/country-sort/

 この表は「順位」「国名」「地域」「(私が飲んだ)銘柄」の各項目で
 並び替え(ソート)ができ、各項目の並び替える順により、地域ごとの
 生産量ランキングや銘柄数ランキングを表示することができます。

 そんなふうに複数の項目で並び替えたいときに使えるのが「SORTBY関数」
 というわけです。うまく紹介できていれば嬉しいです。

                           (編集長・林)

 ※ 次回の配信は 2024年4月4日(木曜日)の予定です。

関数関数関数関数関数関数関数関数関数関数関数関数関数関数関数関数関

  ※当メールマガジンの配信は「まぐまぐ(https://www.mag2.com/)」
   のシステムを使用しています。(マガジンID:81405 殿堂入り!)

  ※購読の解除は、以下の「まぐまぐ!」公式ページで「登録する」
   の下にある「メールマガジンを解除」から解除できます。
   https://www.mag2.com/m/0000081405.html
               ↓
   分からない、うまく解除できない場合は、当メルマガをそのまま
   返信いただけば、解除依頼と判断して代行させていただきます。

  ◆ご意見ご希望は電子メールで!アドレスは faq@724685.com
   またホームページからでもご意見をお送りいただけます。
   「お問い合わせフォーム」 https://www.724685.com/ask/


★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★

 発行者:なにしろパソコン編集長 林 俊二 faq@724685.com
 関連ホームページ:

  ◆ なにしろパソコン.com  https://www.724685.com/
  ◇ 週刊なにしろパソコン  https://www.724685.com/maga/

  ◆ 今日のひとこと・ブログ https://www.724685.com/blog/
  ◇ パソコントラブルQ&A https://www.724685.com/weekly/
  ◆ パソコン用語解説    https://www.724685.com/word/

  ◇ なにしろパソコン編集長 - Twitter
                https://twitter.com/724685
  ◆ なにしろパソコン - Facebookページ
                https://www.facebook.com/724685.fan
  ◇ なにごとも経験・ブログ https://www.nanigoto.com/


 ◎本文の著作権は発行者に属します。無断引用・転載はご遠慮ください。
  (家族や友人への転送や、個人的に楽しむ範囲ではご自由にどうぞ)

 ◎記述に誤りがあったり、記述通りの作業をしても無効、あるいは損害
  等が生じた場合でも責任は負いかねます。どうか、ご了承ください。

★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★☆☆★
 

amazon 楽天

紹介&バックナンバー

編集長のプロフィール

編集長

林 俊二 と申します。「なにしろパソコン・ドットコム」のサイト運営、メルマガ執筆、セミナー講師から出張サポートまでこなすパソコン生活応援アドバイザー
編集長のツイッター