毎日使える、あまり知られていない Google スプレッドシートの関数 11 選

2023/07/04

$毎日使える、あまり知られていない Google スプレッドシートの関数 11 選$

Google スプレッドシートには、おそらくいつも使っている素晴らしい機能がいくつかあります。しかし、機能に関しては、存在を知らなかった機能がいくつかあるかもしれません。これらの便利な関数とそれに付随する数式は、値の比較、財務データの取得、配列の列や行への変換などに役立ちます。このリストには、生産性の向上に役立つ、あまり知られていない Google スプレッドシートの関数が 11 個含まれています。

1. 値の比較: GT、GTE、LT、LTE、EQ

2 つの値、または 2 つの異なるセルの値を比較する場合、タスクを簡単にする 5 つの演算子関数があります。売上金額や経費を長期的に比較する場合でも、これらの機能が対応します。

以下に示す関数と各式の構文は同じです。

GT (より大きい) :GT(value1, value2)
GTE (以上) :GTE(value1, value2)
LT (未満) :LT(value1, value2)
LTE (以下) :LTE(value1, value2)
EQ (等しい) :EQ(value1, value2)

引数には、比較したい正確な値、値を含むセル参照、または両方を組み合わせて入力できます。

数式を入力すると、「True」または「False」の結果が表示されます。

いくつかの例を見てみましょう。

この例では、GT次の数式を使用してセル A1 と B1 の値を比較しています。

=GT(A1,B1)

セル A1 の値がセル B1 の値より大きくないため、結果は False になります。

小なり関数を使用して、値 15 と 20 を次の式で比較します。

=LT(15,20)

15 は 20 未満であるため、結果は True になります。

2. テキスト文字列の比較: EXACT

おそらく、必要な比較は 2 つのテキスト文字列に対するものです。このEXACT関数を使用すると、大文字と小文字の区別、スペース、隠し文字など、文字列に同じ文字が含まれているかどうかを確認できます。これは、一見しただけではわからないタイプミスを見つけるのに役立ちます。

数式の構文はですEXACT(string1, string2)。両方の引数が必要で、セル参照、テキスト、またはその両方を指定できます。この関数は、完全に一致する場合は「True」を返し、一致しない場合は「False」を返します。

この例では、セル A1 と B1 のテキスト文字列を次の数式で比較しています。

=EXACT(A1,B1)

結果は偽です。よく見てみると、セル B1 のテキストには B の後にピリオドが含まれていますが、セル A1 のテキストにはピリオドが含まれていないことがわかります。

別の例では、次の数式を使用して、セル A1 のテキストとテキスト文字列「第 1 四半期の場所 B の収益と支出」を比較します。

=EXACT(A1,"Location B Revenue and Expenses for Quarter 1")

繰り返しますが、この結果は False です。よく見ると、「Quarter」の綴りが違うことがわかります。セル A1 ではスペルが間違っていますが、数式のテキスト文字列では正しいです。

3.一意の値を数える: COUNTUNIQUE

Google スプレッドシートの関数を使用するとCOUNTUNIQUE、スプレッドシート内の個別の値の合計を取得できます。一意の顧客名、電子メールアドレス、または都市を検索したい場合があります。

数式の構文はですCOUNTUNIQUE(value1, value2,…)。最初の引数のみが必要です。引数にはセル参照、値、テキストを使用できます。

この例では、一部の顧客が複数回注文していることがわかっているため、シート内に個別の顧客名がいくつあるかを調べています。次の式は、セル範囲 A2 ～ A8 内で一意のレコードを検索するために使用されます。

=COUNTUNIQUE(A2:A8)

異なる名前が 5 つしか表示されないため、結果は 5 になりました。

別の例では、入力された値のリスト内の固有の項目をカウントしています。この数式を使用して、セル範囲とテキストを挿入します。

=COUNTUNIQUE(A2:A3, "Bill Brown", "Sue Smith")

結果 3 を受け取りました。セル範囲内の 2 つと数式内の 2 つのうち、一意の名前は 3 つだけです。

4. 条件を使用して一意の値を数える: COUNTUNIQUEIFS

この機能が便利だと感じたらCOUNTUNIQUE、その機能も評価していただければ幸いですCOUNTUNIQUEIFS。条件に基づいて一意の値のみをカウントする基準を入力できます。

数式の構文はですCOUNTUNIQUEIFS(range, criteria_range1, criterion_1, criteria_range2, criterion_2,…)。カウントするセル範囲には「range」を、評価するセルには「criteria_range1」を、条件には「criterion_1」を使用します。必要に応じて、複数の基準範囲と基準を入力します。

この例では、次の数式を使用して、セル B2 ～ B8 の注文合計のうち、75 ドルを超えるセル A2 ～ A8 の一意の値をカウントしています。

=COUNTUNIQUEIFS(A2:A8,B2:B8,">75")

結果は 4 です。$75 を超える注文合計は 5 つありますが、Jim Jones は 2 回出現するため、一意の値として 1 回だけカウントされます。

複数の条件を使用する例では、ここでも $75 を超える注文数をカウントしていますが、商品合計が 10 未満の注文数もカウントしています。

=COUNTUNIQUEIFS(A2:A8,B2:B8,">75",C2:C8,"<10")

結果は 1 です。ジムジョーンズには 75 ドルを超える注文が 2 件あり、どちらも商品数が 10 未満でしたが、一意の値としてカウントできるのは 1 回だけです。

5. 財務詳細の取得: GoogleFinance

Google スプレッドシートを財務の追跡や予算の管理に使用している場合は、投資にも活用できます。このGOOGLEFINANCE機能を使用すると、株式や投資信託に関するさまざまな詳細を Google Finance から直接取得できます。

数式の構文はですGOOGLEFINANCE(symbol, attribute, start_date, end_date, interval)。ティッカーシンボルには最初の引数のみが必要です。「属性」引数は、価格、高値、安値、時価総額など、必要な正確なデータを取得するのに最適です。属性のリストは広範囲に及び、現在、過去、投資信託のデータによって異なるため、 Google ドキュメントエディタのヘルプページで関数を検索できます。

最後の 3 つの引数を使用して、開始日と終了日、および頻度を含む履歴データを取得します。すべての引数を必ず引用符で囲んでください。

この例では、Google の現在の価格を調べています。これは、「attribute」引数を空白のままにした場合のデフォルトです。

=GOOGLEFINANCE("NASDAQ:GOOGL")

次の式を使用して、Google の今日の取引高を見てみましょう。

=GOOGLEFINANCE("NASDAQ:GOOGL","volume")

たとえば、過去のデータを使用して、2023 年 1 月 1 日から 2023 年 2 月 28 日までの Google の終値を週ごとに確認します。

=GOOGLEFINANCE("NASDAQ:GOOGL","close","1/1/23","2/28/23","WEEKLY")

6. 言語を識別する: DETECTLANGUAGE

別のソースからデータをインポートする場合、または Web サイトや電子メールからコピーして貼り付ける場合に、方言が不明な場合は、この関数を使用して方言を判断できますDETECTLANGUAGE。

数式の構文はですDETECTLANGUAGE(cells_text)。ここで唯一の引数を使用すると、セル参照、セル範囲、または識別する正確なテキストを入力できます。複数の言語が見つかった場合、結果には最初の言語のみが表示されます。その結果、地域を識別する2 文字のコードが得られます。

例として、次の数式を使用してセル A1 にどの言語が表示されるかを学習しています。

=DETECTLANGUAGE(A1)

結果は韓国語の「ko」です。

7. 言語を翻訳する: GOOGLETTRANSLATE

シート内の言語を単に識別するのではなく、を使用して翻訳する必要がある場合がありますGOOGLETRANSLATE。

数式の構文はですGOOGLETRANSLATE(cells_text, source, target)。最初の引数のみが必要で、セル参照または実際のテキストを入力できます。「source」引数を空白のままにすると、Google はソース言語を自動的に検出します。ただし、ターゲット言語を含める場合は、ソース (2 文字コードまたは「auto」) も含める必要があります。

この例では、同じテキストをフランス語に翻訳し、Google が次の式を使用してソース言語を自動的に検出できるようにしています。

=GOOGLETRANSLATE(A1,"auto","fr")

別の例では、次の式を使用して、セル A1 のテキストを、セル B1 の「ソース」(英語の場合は「es」) とセル C1 の「ターゲット」(韓国語の場合「ko」) で翻訳します。

=GOOGLETRANSLATE(A1,B1,C1)

8. 画像の挿入とカスタマイズ: IMAGE

スプレッドシートに画像を簡単に挿入できますが、Web ページから画像を追加してサイズをカスタマイズすることもできます。Google スプレッドシートの関数を使用するとIMAGE、寸法を指定することもできます。

数式の構文はですIMAGE(url, mode, height, width)。最初の引数のみが必要です。

引数は次のとおりです。

URL : 画像への Web リンク (「https://」を含み、引用符で囲まれたもの)。
モード: 画像のサイズ変更。1 はセルに収まるようにサイズを変更し、縦横比を維持します (省略した場合のデフォルト)。2 は画像をセルに収まるように拡大または縮小し、縦横比を無視します。3 は画像をそのままにします。元のサイズ、および 4 ではカスタムサイズを選択できます。
高さと幅: 使用する高さと幅をピクセル単位で指定します。mode 引数には必ず 4 を選択してください。

この例では、デフォルトの「モード」で URL「https://www.maketecheasier.com/assets/uploads/2023/03/Google-Finance-Amazon-Page-800×430.jpg」に画像を挿入しています。」この式を使用します。

=IMAGE("https://www.maketecheasier.com/assets/uploads/2023/03/Google-Finance-Amazon-Page-800x430.jpg")

別の例では、「mode」4 を使用して独自の「高さ」(230) と「幅」(400) を入力しています。

=IMAGE("https://www.maketecheasier.com/assets/uploads/2023/03/Google-Finance-Amazon-Page-800x430.jpg",4,230,400)

9. テーブルまたはリストをインポートします: IMPORTHTML

Web ページから画像を取得するだけでなく、Web からデータをインポートすることもできます。IMPORTHTML を使用すると、コピー/ペーストや追加の書式設定の手間をかけずに、ページから表やリストを取り込むことができます。

数式の構文はですIMPORTHTML(url, query, index)。ここでは 3 つの引数をすべて使用します。次の方法で式でこれらを使用します。

URL : 画像への Web リンク (「https://」を含み、引用符で囲まれたもの)。
クエリ: 「テーブル」または「リスト」(引用符を含む) を入力して、データの構造を指定します。
インデックス: Web ページ上のテーブルまたはリストを識別するための 1 から始まる番号。

この例は、ジェームズ・ボンドのファン向けです。以下の式を使用して、ジェームズボンド映画を含むテーブルを Wikipedia からインポートしています。

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_James_Bond_films","table",1)

式を詳しく説明すると、URL、「テーブル」としての「クエリ」、および Web ページの最初のテーブルであるため「インデックス」が 1 になります。

リストクエリを使用する別の例では、独自のページの 1 つを使用して目次をキャプチャしています。

=IMPORTHTML("https://www.maketecheasier.com/track-stocks-google-sheets/","list",7)

この数式には、URL、「リスト」としての「クエリ」、および「インデックス」としての数字 7 が含まれています (これはページ上の 7 番目のリストであるため)。

10. 労働日の数を数える: NETWORKDAYS

2 つの日付の間の稼働日または営業日の数を数える必要があったことがありますか? このNETWORKDAYS関数を使用すると、2 つの日付間の土日祝日を除いた稼働日数を取得できます。

構文はですNETWORKDAYS(start, end, holidays)。最初の 2 つの引数のみが必要です。「休日」引数を使用するには、それらの日付を含むシート内の範囲を参照します。

次の式を使用して、2023 年 6 月 1 日から 2023 年 12 月 31 日までの就業日を計算します。

=NETWORKDAYS("6/1/2023","12/31/2023")

数式に開始日と終了日を含める場合は、引用符で囲む必要があることに注意してください。

この例では、代わりに日付を含むセル参照を使用しています。

=NETWORKDAYS(A1,B1)

休日の例では、セル C2 から C7 にリストがあり、これを次の式の 3 番目の引数として含めます。

=NETWORKDAYS(A1,B1,C2:C7)

11. 配列の変換: TOROW と TOCOL

シート内の配列を操作し、配列を単一の行または列に変換する場合は、TOROWおよびTOCOL関数を使用できます。これらは 2023 年の初めに導入され、シート内のデータの配置がはるかに簡単になりました。

各関数の数式の構文はおよびTOROW(array, ignore, scan)と同じでTOCOL(array, ignore, scan)、最初の引数のみが必要です。

オプションの引数は次のように機能します。

Ignore : デフォルトでは、関数は値 (0) を無視しません。空白を無視するには 1 を、エラーを無視するには 2 を、空白とエラーの両方を無視するには 3 を使用します。
Scan : デフォルトでは、関数は行ごとにデータをスキャンします (False)。代わりに列ごとにスキャンするには True を使用します。

を使用した例ではTOROW、次の式を使用してセル A1 から C2 の配列を単一の行に変換しています。

=TOROW(A1:C2)

行ではなく列でスキャンするには、代わりに次の式を使用します。

=TOROW(A1:C2,,TRUE)

「scan」引数を True に設定すると、表示される結果の順序が変更されます。この関数は、左から右 (行) ではなく、上から下 (列) にスキャンします。

このTOCOL関数では、セル A1 ～ C2 の配列を単一の列に変換します。

=TOCOL(A1:C2)

この関数で行ではなく列ごとにスキャンするには、次の式を使用します。

=TOCOL(A1:C2,,TRUE)

繰り返しになりますが、結果はデータを左から右ではなく上から下に並べています。

次に、 VLOOKUP をマスターして、同じ行内の別の値に基づいて 1 つの値を検索します。

よくある質問

Google スプレッドシートで数式を表示するにはどうすればよいですか?

Google スプレッドシートに数式を入力する場合、セルを選択してツールバーの下にある数式バーを見ると、数式を確認できます。ただし、代わりにセル内の数式を表示したい場合は、「表示」メニューに移動し、カーソルを「表示」に移動し、ポップアウトメニューで「数式」を選択します。

Google スプレッドシートの数式と関数の違いは何ですか?

多くの人は「式」と「関数」という用語を同じ意味で使用しますが、同じではありません。数式は、シートに指示を与えるために作成される式です。数式は等号で始まり、その後に命令が含まれます。では=GT(A1,A2)、文字列全体が数式です。

関数は、Google スプレッドシートが作成する既成の数式です。これには、スプレッドシートに何をすべきかを指示する舞台裏の指示が含まれています。多くの場合、数式内に関数を配置できます。この式では=GT(A1,A2)、関数は GT です。

Google スプレッドシートの関数に関するヘルプはどこで入手できますか?

数式に関数を入力すると、セルの横に青い疑問符が表示されます。その疑問符を選択して数式ヘルプをオンにするか、F1キーボードのを押します。

例、各引数が期待する内容、関数の Google サポートページに移動する「詳細」へのリンクなどの詳細が表示されます。

画像クレジット: Pixabay。すべてのスクリーンショットは Sandy Writtenhouse によるものです。