リスキリングか Google SS/Excel【変幻自在の関数】

すぐ使える驚異の関数を紹介します

#004 №列に 表の最下にある行まで 自動で通番を付ける関数を考えた

こんにちは 能山 生土です

表を使う場合 一番左の列に №列 を置くことが多くあります

表内のデータが散らばっている場合

一番最下にある行まで 自動で通番を付けたい

というときに使える関数を考えます

 

以下のように学校でよく使う表で説明します

表はクラス(組)毎に データ(氏名)が散らばっています

 

 

さて №列 の通番は 1、2,3、4,5

これは 2組の 木島 が上から5番目で 最下行に位置しているからです

 

もし 4組の下に 志賀 が追加されたら

№列は 1~7に変化します



表の中で最下行である数字(7)を求めて1から通番を作る関数を作ります

関数は №列のすぐ下のセル(上の例では セルB3) に入れます

 

[完成した関数]

 

[使用例] データ範囲:C3:G・・・1組のすぐ下セル(C3)~5組の列(G列)

 

■使用方法

・下のリンク先ファイル(#004 最大行通番)を開き

 関数が入っている セルB4(黄色)の数式を コピーする

・別Googleスプレッドシートファイルに貼り付ける

・行間隔、最大値を変更する場合は

 =LAMBDA(・・・・・)(C3:G) の最後のカッコ内(データ範囲)を修正

 

■リンク先 URL

#004 最下行数連番

 

Excel で使う

・2022年2月、Microsoft Excel 365版で使用できる新しい関数が増えましたので

 この記事で使える関数は

 LAMBDA関数が使えるExcel最新のバージョンで利用してください

 実際に、以下の関数を入力してセルが「#NAME?」エラー表示にならなければ、

 利用できます

 

[完成した関数]

[使用例] 表右上アドレス=C4 表左列内アドレス =G1

     ただし 表左列内アドレスは G1 G2 G3 ・・・のように どのアドレスでもOK

 

■使用方法

・下のExcelが埋め込まれたフレームで

 関数が入っている セルB1(黄色)の数式を コピーする

・ローカルのExcelファイルの適当なシートに貼り付ける

 貼り付けた数式の先頭に 以下の通り = を付ける

 =LAMBDA(・・・

表右上アドレス表左列内アドレスを変更する場合は

 =LAMBDA(・・・・・)(C4,G1) の最後のカッコ内(アドレス,  アドレス)を修正

 

#003 1、1、1、2,2,2,・・・のように 一定の個数の数字が並ぶ関数を考えた

こんにちは 能山 生土です

 

 

今回は 1,2,3・・・ の自然数を 次のように並べます

 

1 1 1 2 2 2 3 3 3 4 4 4 5 5 5・・・

 

⇒ 自然数が 一定の個数並び 指定した最大値まで表示する

上の例では 同数個数 =  最大値 =

 

図例:同数個数 =  最大値 =  罫線は手入力



Google スプレッドシート で使う

[完成した関数]

 

[使用例] 同数個数 = 3 最大値 = 6 ただし 同数個数、最大値は1以上の整数

 

■使用方法

・下のリンク先ファイル(#003 自然数同数並び)を開き

 関数が入っている セルE3(黄色)の数式を コピーする

・別Googleスプレッドシートファイルに貼り付ける

・同数個数、最大値を変更する場合は

 =LAMBDA(・・・・・)(3,6) の最後のカッコ内

 (左が同数個数 右が最大値)を修正

 

Google スプレッドシートリンク先 URL

#003 自然数同数並び

 

Excel で使う

・2022年2月、Microsoft Excel 365版で使用できる新しい関数が増えましたので

 この記事で使える関数は

 LAMBDA関数が使えるExcel最新のバージョンで利用してください

 実際に、以下の関数を入力してセルが「#NAME?」エラー表示にならなければ、

 利用できます

 

[完成した関数]

 

[使用例] 同数個数=最大値 = ただし 同数個数、最大値は1以上の整数

■使用方法

・下のExcelが埋め込まれたフレームで

 関数が入っている セルB1(黄色)の数式を コピーする

・ローカルのExcelファイルの適当なシートに貼り付ける

 貼り付けた数式の先頭に 以下の通り = を付ける

 =LAMBDA(・・・

同数個数最大値を変更する場合は Google スプレッドシート同様

#002 行間隔は均等 最大値を決めたら 数字が並ぶ さらに 項目も一緒に表示する関数を考えた

こんにちは 能山 生土です

前回 #001 では №列を表示しました

実際の業務では №列と一緒に 項目列も表示する場合が多いので

今回は 図のように №列(連番)と項目 を同時に表示します

セルB2:C2 の № と 項目、罫線 は手入力 セルB3 に関数が入っています

 

図例:項目 = "氏名"、"メールアドレス"、"電話番号" の3つ

   繰返数 =



Google スプレッドシート で使う

[完成した関数]

項目名・・・ダブルコーテーション("項目名")で括り

      項目と項目の間は コンマ(,)を入れる

 

[使用例] 項目名 = "氏名","メールアドレス","電話番号"

     繰返数 =



■使用方法

・下のリンク先ファイル(#002 行間隔均等連番+項目)を開き

 関数が入っている セルB3(黄色)の数式を コピーする

・別Googleスプレッドシートファイルに貼り付ける

・項目名、繰返数を変更する場合は

 =LAMBDA(・・・・・)(Transpose({

・項目名は 3つ固定ではありません 追加してもOK

 追加例 "備考" を追加

  "氏名","メールアドレス","電話番号" ,"備考"

 

Google スプレッドシートリンク先 URL

#002 行間隔均等通番+項目

 

Excel で使う

・2022年2月、Microsoft Excel 365版で使用できる新しい関数が増えましたので

 この記事で使える関数は

 LAMBDA関数が使えるExcel最新のバージョンで利用してください

 実際に、以下の関数を入力してセルが「#NAME?」エラー表示にならなければ、

 利用できます

 

[完成した関数] スプレッドシートと同様

 

[使用例] スプレッドシートと同様

 

■使用方法

・下のExcelが埋め込まれたフレームで

 関数が入っている セルB1(黄色)の数式を コピーする

・ローカルのExcelファイルの適当なシートに貼り付ける

 貼り付けた数式の先頭に 以下の通り = を付ける

 =LAMBDA(a_itm、・・・

#001 行間隔は均等 最大値を決めたら 数字が並ぶ関数を考える

はじめまして 能山 生土(のうざん きと)と申します

表計算ソフト で 関数を組み合わせて新しい機能を作り出すのが好きです

多少マニアックですが お役に立てば 幸いです

 

以前 私は 仕事上 Excelを よく使っていましたが

現在は Google スプレッドシート を使うことが多くなりました

 

表計算ソフトでは 下図のような №列をよく作ります

下例では 行間隔が3 最大値は5 ですが

最大値が 1000とか 10000とかになると 手入力では かなり面倒

関数の組み合わせで実現しました

条件は、関数の組み合わせを一つ(ひとつのセルに)だけ入れること

セルB2 の № と 罫線 は手入力 セルB3 に関数が入っています

 

図例:行間隔 = 最大値 =

 

Google スプレッドシート で使う

[完成した関数]

 

[使用例] 行間隔=3 最大値=5 ただし 行間隔は2以上、最大値は1以上の整数

 

■使用方法

・下のリンク先ファイル(#001 行間隔均等連番)を開き

 関数が入っている セルB2(黄色)の数式を コピーする

・別Googleスプレッドシートファイルの適当なシートに貼り付ける

・行間隔、最大値を変更する場合は

 =LAMBDA(ntn_r,・・・・・)(3,5) の最後のカッコ内

 (左が行間隔 右が最大値)を修正

 

Google スプレッドシートリンク先 URL

※条件:Googleアカウントを持っていること

#001 行間隔均等連番

 

Excel で使う

・上と同等な機能を Excelでも実現してみました 

・2022年2月、Microsoft Excel 365版で使用できる新しい関数が増えました

 この記事で使える関数は

 LAMBDA関数が使えるExcel最新のバージョンで利用してください

 実際に、以下の関数を入力してセルが「#NAME?」エラー表示にならなければ、

 利用できます

 

[完成した関数]

 

[使用例] 行間隔=3 最大値=5 ただし 行間隔は2以上、最大値は1以上の整数

 

■使用方法

・下のExcelが埋め込まれたフレームで

 関数が入っている セルB1(黄色)の数式を コピーする

・ローカルのExcelファイルの適当なシートに貼り付ける

 貼り付けた数式の先頭に 以下の通り = を付ける

 =LAMBDA(ntn_r、・・・

・行間隔、最大値を変更する場合は Google スプレッドシート同様に修正