記事の詳細

こんにちは。

本日は以前

「グーグルスプレッドシートで特定のシートのみ共有させたい!IMPORTRANGEを使用し共有する方法」

という記事を書きました。
下記リンクをご覧ください。

こんにちは。今日はGoogleスプレッドシートネタです。最近インクループでは、Googleスプレッドシートを使う事が多くなってきています。以前は「Excelの方が使いやすい」...

今回はこの上級編です。

IMPORTRANGEのみとQUERYを使った時の違い

IMPORTRANGEのみの場合

シートを共有する際、共有したくない情報がある場合があります。
Step1では共有したくない情報を除いた情報を掲載する為のシートを作成します。
Step2でStep1で作成したシートに情報を抜粋し記載をします。
Step3で共有用のファイルにIMPORTRANGEを使用し、別ファイルに出力をします。

IMPORTRANGE+QUERYの場合


IMPORTRANGEとQUERYが使えると、「共有してもいいシート」を別途作る必要はありません。
共有したくない情報をQUERYを使って相手には見せないようにする事が可能になります。

QUERYの使い方

Googleのページを見ると、

QUERY(データ,クエリ,[見出し])となっています。
※[]は省略可

データは「クエリを実行したいセルの範囲」を指定します。
クエリはそのままで実行したいクエリを記載します。
※見出しは今回特に必要はないので省略します。

それでは実際にやっていきましょう。

表示したいセル範囲の指定

下図のような家計簿があるとします。

N1のセルに=Query(G1:L8)という式を入れました。

このクエリを実行しますと下図のようになります。

N1~S8まで出力しました。
これはクエリを実行したい範囲(自分のお財布からSuicaの1,136円までの表)を表示しただけなので、そのまま指定したセルの範囲を表示させます。

表示させたい列の指定

続いて、表示する範囲のセルを選択したいと思います。
表示するテーブルは自分のお財布と銀行A、銀行B、Suicaだけ表示させます。

=QUERY(G1:L8,“select G,H,I,L”)
という式を入れます。

このQueryの式を実行しますと下図のようになります。

「G1からL8の範囲内を指定し、selectで自分のお財布、銀行A、銀行B、Suicaのみ表示する」という式になっています。

これで、へそくりAとBを表示しない方法ができました。

さらに表示する条件を指定する

次はデータを抽出します。
銀行B(=I列)が50万円未満の金額を抽出する場合です。

=QUERY(G1:L8,“select G,H,I,L where I<500000”)

この式を実行しますと下図のようになります。

銀行Bが50万円未満のデータが抽出されています。

これでQUERYの使い方がなんとなくわかってきたと思います。
もうイメージが付いている方もいるのではないでしょうか。
次はいよいよ本番で、IMPORTRANGEとQUERYを使って特定のシートのみ共有させる方法です。

 

IMPORTRANGEとQUERYを使って特定のシートのみ共有

まず、準備するものは、「実際に入力する用のファイル」と「共有用のファイル」です。

【実際に入力する用のファイル】

へそくりの項目は見せたくないですよね。

【共有用のファイル】

先ほどのクエリを使って表示をするのは、こちらのファイルになります。

へそくりA、Bを相手には見せないように、「共有用ファイル」に表示させましょう。
式は下記のようになります。

=QUERY(IMPORTRANGE(“実際に入力する用のファイルのURL”,”シート名!A:L”),”select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col12 Where Col2!=’へそくりA’ And Col2!=’へそくりB'”)
※URLは実際に入力する用ファイルのGoogleスプレッドシートのURL(https:~editまでのURL)を記載します。

 

この式は

「セルの範囲をIMPORTRANGEを使って【実際に入力する用のファイル】を表示するようにし、Selectで列A~IとL(へそくりA、へそくりB以外の列)を表示、B列でへそくりA、へそくりB以外のものを表示する」

という式になっています。

実際に実行をしますと下図のようになります。

J列とK列、5行目と6行目にあった、へそくりA、へそくりBに該当しているものが表示されていません。
IMPORTRANGEを使う事で、特定のシートの共有。
QUERYを使う事で表示させたい部分、させたくない部分を選択し自由に表示させる事ができるようになります。

 

別のファイルを参照する場合、列指定の方法が変化する。

お気づきの方もいるかと思いますが、先ほどQueryの時は、A列B列とアルファベットで指定していましたが、今回Col1、Col2としました。
IMPORTRANGEを使ってファイルをまたがる場合、A列やB列という指定ではERRORになってしまうので、Queryの列指定には「Col」を使用するようにしましょう。
※表記は最初の「C」は大文字、「ol」は小文字で記載しましょう。

 

おわりに

今回、家計簿を例に使いましたが、非常に便利です。
特に、夫婦や同棲しているカップルなど、複数の人で1つのお財布や銀行を管理しているところにはとても役立ちます。
Googleスプレッドシートはアプリでも提供されているので、外出先で入力も可です。
ぜひ利用してみてはいかがでしょうか。

 

この記事であなたの課題を解決することができましたか?

疑問点があったり、解決できなかったことがありましたら、お気軽にご相談してください。


ほかの投稿も読んでみませんか?

  1. アバター
    • くら
    • 2019年 2月 07日

    はじめまして。こちら参考にさせて頂きました。ありがとうございます。

    一つ質問なのですが
    こちらを使って、重要機密情報以外を社外に共有しようと考えています。(編集可能で)

    こちらでいうとへそくりに当たる部分をみるために
    編集可能な空いたスペースで、数式からスプレッドIDをコピーしてして、列指定などをせずシート全体を指定してしまえば
    そのシート全体をみることは可能なのでしょうか?
    このシートでアクセス許可を出してしまっているので
    参照列を変えてしまえば見られてしまうようなきがするのですが、
    そこは特定の人しかIMPORTRANGEを変えられないようになっているのでしょうか。
    無知な質問で申し訳ありません。
    よろしくお願いいたします。

  2. インクループ株式会社
    • インクループ株式会社
    • 2019年 2月 07日

    こんにちは。
    くらさま
    インクループの田中です。

    ブログを拝見して頂きましてありがとうございます。
    質問に回答させて頂きます。

    ■数式からスプレッドIDをコピーして、列指定などをせずシート全体を指定してしまえばそのシート全体をみることは可能なのでしょうか?

    →現在のGoogle スプレッドシートの使用上列の指定は必要のようです。
    https://support.google.com/docs/answer/3093340?hl=ja
    「”[シート名!]範囲” の形式で指定します」
    との記載があります。

    ■特定の人しかIMPORTRANGEを変えられないようになっているのでしょうか。
    編集可能なアクセス権を持たせている場合、
    おっしゃる通りIMPORTRANGEの関数を直接修正する事ができてしまいます。
    そのため、IMPORTRANGE関数内で参照列を表示されたくない場所を指定された場合、
    見られてしまう可能性はあります。

    その対策として、
    IMPORTRANGE関数のあるセルの列に保護をかけ(編集不可状態)、
    その列を非表示にする事で
    対応ができるかと思います。

    今回のブログで言いますとA1のセルにIMPORTRANGE関数の式が入っていますので、
    IMPORTRANGE関数の式をそもそも非表示にします。

    スプレッドシートの「データ」タブの中に「保護されているシートと範囲」があります。この場所で列を選択します(この場合A列)
    権限の設定にて「この範囲を編集できるユーザーに制限する」で「自分のみ」を選択し完了します。
    その後、IMPORTRANGE関数のある列を選択し、(この場合A列)非表示にします。
    編集権限を持っていても、このA列だけは編集ができない状態となります。

    このようにすることで、IMPORTRANGE関数を第3者が編集する事ができなくなります。
    また、列が非表示になっている事で、計算式も見られる事はなくなります。

    この方法も100%完全というわけではありません。
    ・Googleスプレッドシートの仕様が変わる。
    →今回の操作方法で仕様変更が入る事はないとは思いますが、
    仕様変更が入った場合は新しい仕様が適用されます。

    ・今回で言うとA列ですが、何らかの編集をしなければならなくなった場合、一時的に非表示を解除する事になります。
    非表示を解除すると、当然他の編集権限のある人もA列を見る事ができるようになります。
    A列は保護が利いていますが、数式バーにある計算式はコピーができますので、
    悪用しようと思えば悪用する事ができてしまいます。
    ですので、IMPORTRANGE関数を再編集する際には注意が必要です。
    (例えば誰もそのスプレッドシートを使用しない時などに修正するなど運用面でカバーをする事で対応できるのではないでしょうか)

    このような回答となりますが、
    いかがでしょうか?

    またご不明点等ございましたら、お問い合わせくださいませ。

  1. この記事へのトラックバックはありません。

相模原のお店やサービスをどんどん紹介!

行ってみよう相模原 相模原市のお店限定

飲食店

ページ上部へ戻る