Excelにはない便利なGoogleスプレッドシート機能

この記事は、Renat Shagabutdinovと共同執筆しました。







この記事では、Excelにはない非常に便利なGoogleスプレッドシートの機能のいくつかについて説明します（SORT、配列集約、FILTER、IMPORTRANGE、IMAGE、GOOGLETRANSLATE、DETECTLANGUAGE）



たくさんの手紙がありますが、興味深い事例の分析があります。ちなみに、すべての例は、Google Document goo.gl/cOQAd9で詳しく調べることができます（ ファイル- >コピーを作成して、ファイルをGoogleドライブにコピーし、編集できます）。



目次：



-数式の結果が複数のセルの場合

-数式で使用するために複数のデータ範囲を組み合わせる

-並べ替え

-SORTにテーブルヘッダーを追加する方法

-フィルター

-フィルター、2つの条件、日付の処理

-FILTERおよびSPARKLINEを使用したインタラクティブなチャート

-輸入

-ソース表からフォーマットをインポート

-別の関数の引数としてのIMPORTRANGE

-画像：セルに画像を追加

-GOOGLETRANSLATEおよびDETECTLANGUAGE：セル内のテキストを翻訳





数式の結果が複数のセルを占める場合



まず、Googleスプレッドシートに数式の結果を表示する重要な機能。 数式が複数のセルを返す場合、この配列全体がすぐに表示され、必要な数のセルと列を占有します（Excelでは、これらすべてのセルに配列数式を入力する必要があります）。 次の例では、これがどのように機能するかを確認します。

並べ替え

1つまたは複数の列でデータ範囲をソートし、すぐに結果を表示するのに役立ちます。



関数の構文：

= SORT（ソート可能なデータ; column_for_sorting;昇順;; [column_for_sorting_2、ascending_2; ...]]

以下のスクリーンショットの例では、セルD2にのみ数式を入力し、最初の列でデータを並べ替えます（TRUE / FALSEの代わりにTRUE / FALSEを入力できます）。

（以下-ロシアの地域テーブル設定の例、reg。設定はファイルメニューで変更されます→テーブル設定）

SORTにテーブルヘッダーを追加する方法



中括弧{}を使用して、テーブルA1：B1とSORT関数の見出しという2つの要素の配列を作成し、セミコロンを使用して要素を互いに分離します。







複数の範囲のデータを組み合わせて並べ替える方法（だけでなく）



関数で使用する範囲をどのように組み合わせることができるか見てみましょう。 これはSORTに適用されるだけでなく、VPRやSEARCHなど、可能な限りすべての機能で使用できます。



前の例を読んだ人はすでに何をすべきかを理解していました。中括弧を開き、配列を集めて結合し、セミコロンでそれらを分離し、中括弧を閉じます。







配列を組み合わせて数式で使用せずに、シートに表示するだけで、たとえば本の複数のシートからデータを収集できます。 垂直結合の場合、すべてのフラグメントで同じ数の列のみを観察する必要があります（どこにも2つの列があります）。







下のスクリーンショット-水平結合の例では、セミコロンの代わりにバックスラッシュを使用し、フラグメントの行数が一致する必要があります。一致しない場合、式は結合範囲ではなくエラーを返します。

（セミコロンとバックスラッシュは、ロシアの地域設定の配列要素の区切り記号です。例がうまくいかない場合は、ファイルを介してテーブル設定が行われていることを確認してください）

それでは、水平配列に戻ってSORT関数に挿入しましょう。 データを最初の列で降順に並べ替えます。







結合はどの関数でも使用できます。主なことは、垂直結合の場合は同じ列数を、水平結合の場合は行を観察することです。



解析されたすべての例は、

Google Doc

フィルター

FILTERを使用すると、1つまたは複数の条件に従ってデータをフィルター処理し、結果をワークシートに表示したり、結果を別の関数でデータ範囲として使用したりできます。



関数の構文：

FILTER（範囲;条件_1; [条件_2; ...]）

一つの条件



たとえば、従業員の売り上げのテーブルがあり、そこから1人の従業員のデータを取得します。



次の式をセルE3に導入します。

=フィルター（A3：C7; B3：B7 =“ Natalya Chistyakova”）

構文は、条件の範囲と条件自体がセミコロンを使用して区切られるSUMMESLINなどの通常の式とはわずかに異なることに注意してください。







1つのセルに入力された数式は、データを含む9つのセルの配列を返しますが、SORT関数を使用した例の後、これに驚くことはなくなりました。



条件の等号（=）に加えて、>、> =、<>（等しくない）、<、<=も使用できます。 テキスト条件の場合、=と<>のみが適切であり、数値または日付にはこれらのすべての文字を使用できます。



2つの条件と日付の処理



式を複雑にして、条件をもう1つ追加します。販売日に従って、すべての販売を17/01/02から開始します。



これは、条件の引数をすぐに入力し、DATEVALUEを使用した日付テキストエントリの変換に注意を払った場合の式の外観です。

=フィルター（A3：C7; B3：B7 =“ Natalya Chistyakova”; A3：A7> = DATEVALUE（“ 02/01/17”））

または、次のように、引数でセルを参照する場合：

=フィルター（A3：C7; B3：B7 = I6; A3：A7> = J6）

FILTERおよびSPARKLINEを使用したインタラクティブなチャート



FILTER関数を使用する他の方法を知っていますか？ 関数の結果をワークシートに表示することはできませんが、別の関数（スパークラインなど）のデータとして使用します。 スパークラインは、データに基づいてセル内にグラフを作成する関数です。スパークラインには、グラフのタイプ、要素の色など​​、多くの設定がありますが、ここではそれらにこだわることなく、追加の設定なしで関数を使用します。 例に移りましょう。



ドロップダウンリスト。 ドロップダウンリストで選択した従業員に応じてスケジュールが変更されます。リストは次のようになります。

• セルE2を選択します。
• メニューデータ→データ検証;
• ルール：範囲からの値、およびソースデータから従業員を含む列を選択する範囲では、名前が繰り返されることを心配せず、一意の値のみがドロップダウンリストに残ります。

[保存]をクリックして、選択したセルにドロップダウンリストを取得します。





ドロップダウンリストのあるセルは、FILTER式の条件になります。これを記述します。

=フィルター（C3：C7; B3：B7 = E2）

そして、この式をSPARKLINE関数に挿入します。これは、受信したデータに基づいて、セルにグラフを描画します。

=スパークライン（フィルター（C3：C7; B3：B7 = E2））

したがって、ダイナミクスに見えます：







しかし、実際の作業では、追加設定のあるスマートスパークラインは、1日のアクティビティの結果をグラフに表示し、緑のバーは正の値、ピンクのバーは負の値になります。

輸入

GoogleスプレッドシートはIMPORTRANGE関数を使用して、ファイル間でデータを転送します。



いつ便利になりますか？

• ピアファイルから最新のデータが必要です。
• 「表示専用」アクセス権を持つファイルからのデータを処理したい。
• 1つの場所で複数のドキュメントからテーブルを収集して、それらを処理または表示したい場合。

この式を使用すると、別のGoogleスプレッドシートから範囲のコピーを取得できます。 同時に、フォーマットには移植性がありません-データのみです（フォーマットの処理方法-少し後で説明します）。



式の構文は次のとおりです。

IMPORTRANGE（スプレッドシートキー、範囲文字列）

IMPORTRANGE（キー;範囲）

Spreadsheet_key（キー）-テーブルへのリンク内の属性 "key ="（キー）の文字シーケンス（ "spreadsheets / ... /"の後）。



キーを持つ数式の例：

= IMPORTRANGE（ "abcd123abcd123"; "sheet1！A1：C10"）

テーブルキーの代わりに、ドキュメントへの完全なリンクを使用できます。

= IMPORTRANGE（ " docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc "; "Sheet1！A1：CM500"）

ファイルには、適切なリンクにあるファイルの範囲A1：Sheet1のCM500が表示されます。







ソースファイルで列または行の数が変更できる場合、関数の2番目の引数に開いている範囲を入力します（サブセクション「フォームA2の範囲：A」も参照）。例：

Sheet1！A1：CM（行が追加される場合）

Sheet1！A1：1000（列が追加される場合）

！ 開いている範囲（たとえば、A1：D）を読み込む場合、IMPORTRANGE数式が配置されているファイル（つまり、データが読み込まれる最終的な場所）のA：D列にデータを手動で挿入することはできません。 それらは、その範囲が事前に知られていないため、オープン範囲全体で「予約」されているようです。



数式ではなく、ドキュメントのセルにファイルへのリンクと範囲へのリンクを入力して、それらを参照できます。



したがって、セルA1にデータを読み込むドキュメントへのリンク（引用符なし）を入力し、セルB1にシートと範囲へのリンク（引用符なし）を入力すると、次の式を使用してデータをインポートできます。

=インポート（A1; B1）

セルリンクを使用するオプションは、（セル内のリンクをクリックすることで）ソースファイルにいつでも簡単に移動できる、および/またはどの範囲とどのタブからインポートされるかを確認できるという意味で望ましいです。

ソース表からフォーマットをインポートする

すでにお気付きのように、IMPORTRANGEはデータのみをロードし、元のテーブルのフォーマットはロードしません。 これに対処する方法は？ ソースシートから書式をコピーして、事前に土壌を準備します。 これを行うには、ソースシートに移動して、ブックにコピーします。







[ コピー先... ]ボタンをクリックした後、データをインポートするブックを選択します。 通常、必要なテーブルは[ 最近 ]タブにあります（実際に最近使用した場合）。



シートをコピーした後、すべてのデータを選択します（左上隅をクリックして）。







そして、 削除をクリックします。 すべてのデータが消え、フォーマットは残ります。 IMPORTRANGE関数を入力して、データ部分とフォーマット部分の両方で、ソースシートの完全な通信を取得できます。







別の関数の引数としてのIMPORTRANGE



IMPORTRANGEは、イン​​ポートする範囲がこの役割に適合する場合、別の関数の引数になる場合があります。



簡単な例を考えてみましょう-別のドキュメントで見つかった範囲の売上の平均値。



これがソースドキュメントです。 データを追加してみましょう。2016年の売上の平均（つまり、セルD2からストップダウンまで）が必要です。







まず、この範囲をインポートします。

IMPORTRANGE（ " docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4 "; "Books！D2：D"）

そして、これをAVERAGE関数の引数として使用します。

=平均（IMPORTRANGE（“ docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4 ”;“ Books！D2：D”））

= AVERAGE（IMPORTRANGE（ " docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4 "; "Books！D2：D"））

新しい行がD列のソースファイルに追加されると、更新される結果が得られます。

IMAGE：画像をセルに追加します

IMAGE関数を使用すると、Googleスプレッドシートに画像を追加できます。



この関数の構文は次のとおりです。

IMAGE（URL、[モード]、[高さ]、[幅]）

必須の引数はURLのみです。 これは画像へのリンクです。 リンクは、引用符を使用して、式で直接指定できます。

= IMAGE（“ http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”）

または、リンクが保存されているセルにリンクを配置します。

=画像（B1）

後者のオプションは、ほとんどの場合により便利です。 したがって、書籍のリストと表紙へのリンクがある場合、それらすべてを表示するには1つの式で十分です。







実際には、画像へのリンクは別のシートに保存され、VLOOKUP関数などを使用してそれらを取得します。







mode引数には4つの値を指定できます（省略した場合、デフォルトで最初になります）。

1. 画像は、アスペクト比を維持しながらセルのサイズに引き伸ばされます。
2. 画像はアスペクト比を維持せずに引き伸ばされ、完全に塗りつぶされます
   
   
3. 画像は元のサイズで挿入されます。
4. [height]および[width]関数の3番目と4番目の引数で画像のサイズを指定します。 [height]、[width]は、それぞれ引数値がmode = 4の場合にのみ必要です。これらはピクセル単位で設定されます。

mode引数の4つの異なる値を持つ画像が実際にどのように見えるかを見てみましょう。







4番目のモードは、ピクセル単位の正確な画像サイズを選択する必要がある場合に便利です。パラメーターの高さ（高さ）と幅（幅）を変更します。 画像はすぐに更新されます。

2番目のモードを除くすべてのモードでは、セルに空白の領域があり、色で塗りつぶすことができることに注意してください。

GOOGLETRANSLATEおよびDETECTLANGUAGE：セル内のテキストを翻訳

Googleスプレッドシートには、セル内のテキストを直接翻訳できる面白いGOOGLETRANSLATE関数があります。







関数の構文は次のとおりです。

GOOGLETRANSLATE（テキスト、[ソース言語]、[ターゲット言語]）

textは翻訳されるテキストです。 テキストを引用して数式に直接書き込むことができますが、テキストが書き込まれているセルを参照する方が便利です。

[source_language]-翻訳元の言語。

[target_language]は、翻訳先の言語です。



2番目と3番目の引数は、2桁のコードes、fr、en、ruで指定されます。 関数自体で指定することもできますが、セルから取得することができ、ソーステキストの言語は自動的に決定されます。







しかし、異なる言語に翻訳したい場合はどうでしょうか？ 同時に、毎回ソース言語を手動で指定したくありませんか？



ここではDETECTLANGUAGE関数が便利です。 彼女には引数が1つしかありません-言語を定義する必要があるテキスト：







他の機能と同様に、ここの美しさは自動化にあります。 テキストまたは言語をすばやく変更できます。 1つのフレーズを10個の言語にすばやく翻訳します。 もちろん、これはオンライン翻訳者のテキストであることを理解しています。品質は適切です。



Evgeny NamokonovとRenat Shagabutdinov、また、電報でチャンネルを実施します。興味がある場合は、Googleスプレッドシートでさまざまなケースを解析します。訪問でドロップすると、リンクがプロフィールに表示されます。