googleスプレッドシート

スプレッドシートの使える関数~Query関数~

こんにちは!おまかせ事務代行YPP 伊澤です。
スプレッドシートの使える関数シリーズ3つ目はQuery関数を紹介します!

Query関数は、例えば表のデータを絞り込む場合、フィルタ機能を使用することが多いと思います。
でも行が増えるたびにフィルタを付け直す作業は面倒だったりしますよね^^;
そしてデータを間違って書き換えてしまわないか不安という気持ちもあります。。

そんなときにお勧めなのがQuery関数です。
数式を一度設定してしまえば自動更新をしてくれるので、データを誤って触る心配もないです。

いろいろ使えそう!!?Query関数でできること

Query関数でできること、慣れてしまえばやれることは沢山ありそうです!
ざっと出来ることを紹介しますね^^

①列の順番を入れ替える
元のデータをコピペ・・・という手間なく列の順番を変えられます

数式は =QUERY(範囲,”select 列”)

今回はA列とB列を入れ替えたいので以下のようにします。

=QUERY(A1:C20,”select B,A,C”)

特に列の入れ替えを行わない場合は

=QUERY(A1:C20,”select *”) とします。

②データの抽出をする
列の順番を入れ替え、且つデータの抽出もできます

数式は =QUERY(範囲,”select 列 where 抽出する列=’抽出したい文字列'”)

今回はAとB列を入れ替えて、C列の「伊澤」だけを抽出したいので以下のようにします。

=QUERY(A1:C20,”select B,A,C where C=’伊澤'”)

上記と同様に、列の入れ替えを行わない場合は

=QUERY(A1:C20,”select * where C=’伊澤'”) とします。

③別のシートへ表示させる

①②では、同じシートにデータを抽出して反映させましたが、別のシートへ反映させることも出来ます。

範囲の表記を変えることで別シートへデータを反映させることが出来ます

数式は =QUERY(‘シート名’!範囲,”select 列 where 抽出する列=’抽出したい文字列'”)

今回、反映元のシート名が「シート1」なので、以下のようにします。

=QUERY(‘シート1’!A:C,”select B,A,C where C=’メンバー'”)

また、範囲を「A1:C20」ではなく「A:C」にすることで列全体を反映するので、元データに新しい行が追加されれは自動的に更新されます。

④別のスプレットシートへ表示させる

「使えるのは同じスプレッドシートだけ??」と思った方もいるかもしれませんが、前回お伝えしたImportrange関数と組み合わせることで別のスプレッドシートへデータを反映させることができます。

これができたら、例えばYPPの稼働実績を予め自分だけが見られるシートへ反映させておいて、自分の稼働分だけ集計なんかもできちゃいます!

Query関数とImportrange関数を組み合わせて実際のデータを別のスプレットシートへ反映させています。

数式は
=Query(IMPORTRANGE(“スプレッドシートキー”,”!A10:G”),”select Col1,Col2,Col4,Col5,Col7 where Col4 =’YPP 事務所'”)

範囲を「A10:G」としているのは実際の表が10行目から始まっているためです。
「A:G」と表記しても問題ないですが、表と関係ない文字が1行目に集約されてしまうので、綺麗に表示させたい場合は表が始まる行数を入力することをおすすめします!

また、列の表記も先ほどとは異なっていることにお気づきでしょうか?
「select Col1,Col2,Col4,Col5,Col7」とさっきまで「A,B,C・・・」としていたのに違います^^;

はっきりした理由はわかりませんが、Importrange関数を組み合わせるときは列を「A,B,C・・・」だとうまくいかず、「Col1,Col2,Col3,Col4・・・」という表記でうまく反映することが出来ました。

Query関数を活用しよう!

若干とっつきにくいかもしれませんが、慣れればいろんな使い方が出来ると思います!
まずは稼働実績表をご自身のスプレッドシートへ反映するところから試してみてください^^

スプレッドシートを使いこなして、業務効率化を目指しましょう☆

引用

https://pumpuppineapple.com/query5/

合わせて読んでください^^

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。

スプレッドシートの使える関数~Importrange関数~

こんにちは!おまかせ事務代行YPP 伊澤です。

前回のArrayFormula関数に引き続き、スプレッドシート独自の便利な関数を紹介します。

今回は Importrange関数 です!
Importrange関数は他シートのデータを取り込むことができます。

他のスプレッドシートのデータを自動で取り込む!Importrange関数

複数シートで同じデータを管理している場合、Importrange関数を使えば、元データが更新されると取り込んだデータも自動で更新されるので、他のシートの情報を書き換える手間がなくなります。

使い方
初めに表示させるためには「アクセス許可」が必要なのでクリックします。

数式は、
=IMPORTRANGE(“スプレッドシートキー”, “!範囲”)

今回はデータ元であるシートのA~D列を反映させたいので範囲は「!A:D」とします。

※このときにダブルコーテーションで括るのをお忘れなく!(範囲の前には “!” を入れます)
※「スプレッドシートキー」は、データの読み込み元となるスプレッドシートの URL の内、「d/」の後の部分のことです。

スプレッドシートの住所をイメージすればわかりやすいです。
アクセス許可をすると元のデータがそのまま反映されます。
注意点

ArrayFormula関数と同様、Importrange関数も表示範囲内に文字を入力するとエラーになってしまうので気を付けましょう。

Importrange関数を使えば、同じデータを複数のシートで管理する手間がなくなります。
二度手間をなくすことでミスも防げて効率化にもつながりそうです^^

是非、いろんな現場で活用してみてください☆

引用

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。

スプレッドシートの使える関数~ArrayFormula関数~

こんにちは!おまかせ事務代行YPP 伊澤です。
題名を見て?と思った方も多いと思いますが、Googleスプレッドシート独自の関数があるってご存知でしたか?
数年前まではスプレッドシートはExcelよりも機能面では劣る・・・というイメージがありましたが
Excel以上に便利な機能も実は探せばたくさんあるんです♪

その中で今日はArrayFormula関数を紹介したいと思います!

データ軽量化に!ArrayFormula関数

Arrayformulaで関数を括ってあげれば、オートフィルを使うことなく数式を対象の列や行に反映させることが出来ます。
最初のセルにのみ数式を入力すれば良いので、データの軽量化にもつながります。
ショートカットキーもあるので是非覚えておきたい関数です。

OSショートカットキー
WindowsCtr+Shft+Enter
MacCommand+Shft+Enter

使い方

行が増えるたびに数式をコピーして反映させて・・・
というのは地味に大変ですが、ArrayFormula関数で最初の関数を括っておけばその必要もありません。

表示されている数式の違いに何か気が付きましたか?

Arrayformula関数を適用させるには、数式を見て判別してほしい範囲を全て選ぶ必要があるので・・・

数式が「=IF(I2-H2<>0,I2-H2,””)」 の場合は・・・

=ArrayFormula(IF(I2:I-H2:H<>0,I2:I-H2:H,””)) とします。

指定したセルにそれぞれ注目してほしいのですが、「I2:I」はI2セル以降のI列全体を見るという意味となり、行が増えても数式を追加で入力することなくついてきてくれます。
I18セルまでの反映させるなら「I2:I18」というように表記します。(他のセルも同じように範囲を指定します)

注意点

  1. Arrayformula関数が適用されている範囲に文字列を入力してしまうと数式がエラーとなってしまう。(画像参照)
  2. Arrayformula関数はすべての関数に適用するわけではない。(基本的な関数は使えますが、Index、Match関数の組み合わせ、Sumifs、Countifs、Ifs関数など未だ原因が解明されていないエラーなどもあります。)
あらかじめ範囲指定をするなど注意して設定しましょう。

読み込みが遅くなってきたな・・・
というスプレッドシートがありましたら、是非ArrayFormula関数の活用を検討してみてください^^

引用

https://aitrigger.co.jp/blog/efficient/arrayformula_useful_function/

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。

【事務効率化】ChatWorkの予約投稿を活用しよう~動作確認編~

こんにちは!おまかせ事務代行YPP 伊澤です。

前回紹介した【事務効率化】ChatWorkの予約投稿を活用しよう~AppsScript設定編~ではGoogleスプレッドシートを使ってAppsScriptの設定を行いました。

皆さんスムーズに設定できましたでしょうか?

今日は早速動作確認をしていきたいと思います!

スプレッドシートへ設定を行う

最初に用意したスプレッドシートへ投稿したい内容を入力します。

これで準備OKです。
入力した投稿日のトリガーで設定した時間帯に投稿されます。
※ルームIDの確認方法は以下をお読みください↓

ルームIDの確認方法
予約投稿したいグループチャットの歯車マークをクリック
「グループチャットの設定」を開いたら左下に表示されている「ルームID」をコピーすればOKです
実行してみた!結果・・・

エデュタも保存して、トリガーも設定して、投稿内容も入力して、これで投稿されるのを待つのみ!!!

・・・・と思ったら投稿されない^^;

トリガーの実行数を確認したら・・・失敗している;;
解決策

またまたGASを最初に動かすにはGoogleさんにアクセス許可を与えないとダメみたいです。

エデュタ画面から「実行」をクリックします

画面キャプチャがなくて申し訳ないのですが、実行後、「このアプリは Google で確認されていません」というエラー表示が出てくるので、手順は『AppsScript設定編』でお伝えした流れと一緒です。

  1. 画面下の「詳細」をクリック
  2. 「××××××[ファイル名](安全ではないページ)に移動」 をクリック
  3. 「××××××[ファイル名]がGoogleアカウントへのアクセスをリクエストしています」という画面が出てくる
  4. 「許可」をクリックする
実行されました!
実行日時と同じ時間帯に指定したチャットに投稿されましたー!

何回も「実行」をクリックしてしまうと同じ投稿がされてしまうので注意してくださいね。(笑)

まとめ

初めてGoogle Apps Script(通称GASと覚えましょう)を使う方はアクセス許可を3回くらい求められるので心が折れそうになりますが、そこをクリアすれば予約投稿が使えるようになります^^

予約投稿できるのは、管理者権限のあるグループチャットのみです。
管理者権限がない場合は管理者権限のあるYPP担当者へ「GASの予約投稿を使いたいので・・・」とお気軽にご相談ください^^

また、マイチャットや個人チャットへの投稿はできないのでご注意ください。

夜に申し訳ない気持ちでチャットを送信する方の気持ちが少しでも楽になることを願っております☆彡

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。

【事務効率化】ChatWorkの予約投稿を活用しよう~AppsScript設定編~

こんにちは!おまかせ事務代行YPP 伊澤です。

今日はメンバーさんからお聞きしたチャットワークに関する便利機能をご紹介します★
Googleスプレッドシートの「Apps Script」という拡張機能を使えば指定した日にチャットワークのグループチャットへ予約投稿ができるのです!

朝は忙しいから・・・とついつい遅い時間にチャットを送ってしまうというとき、時期尚早だけど忘れないうちに連絡しておきたい!というときなど、是非ご活用ください^^

「準備の前の準備」初めてGoogle Apps Scriptを使う方・・・

GoogleドライブからGoogle Apps Scriptを取得します。
※Google Apps ScriptとはGoogleが提供するプログラミング言語で、プログラムをすることで作業の自動化が行えます。Excelで言うマクロと似たイメージです。

「新規」をクリック

「その他」から「アプリを追加」をクリック

「Google Apps Script」を検索してクリックします

スプレッドシートを開いたときにメニューバーに「拡張機能」が表示されます

1.スプレッドシートを用意する

スプレッドシートを新規で作成します。
ファイル名は何でもOKですが、のちのScriptの編集上、シート名は「シート1」のままを推奨します。

以下の必要項目を1行目に入力します。

項目名入力内容
NO管理しやすいように連番を付けるのがおすすめです
ルームID投稿したいグループチャットの「ルームID」を入力
※確認方法はのちほど説明します
投稿内容投稿したい内容を入力。TOへ絵文字も指定可能です
投稿日投稿日を「yyyy/mm/dd」で入力する
実行日時スクリプト内で自動で記録するので空白でOK
実行日時の表示形式は「日時」で設定します
2.Apps Scriptの設定を行う
「拡張機能」 → 「APPs Script」を開く
Apps Scriptを開くとこの画面が表示されます

最初に入力されているコードは全削除して、以下のコードをコピーして貼りつけます。

// 【編集対象】Chatwork API Token
const token = '●●●';

function myFunction() {

  // スプレットシート読み込み
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getLastRow()

  // ChatworkAPIクライント作成
  const client = ChatWorkClient.factory({token: token});

  // 実行日時
  const today = new Date();

  // スプレットシートに記載がある分だけ繰り返す
  for(var i = 2; i <= lastRow; i++) {

    const room_id = sheet.getRange(i, 2).getValue(); // ルームID
    const message = sheet.getRange(i, 3).getValue(); // 投稿内容
    const reservation_date = new Date(sheet.getRange(i, 4).getValue()); // 投稿日
   
    // 同じ日付の場合、メッセージ送信
    if(diffDate(reservation_date,today)){
      client.sendMessage({
        room_id: room_id,
        body: message
      });

      // 実行日時の記録
      sheet.getRange(i, 5).setValue(new Date());
    }
  }
  
}

// 日付を比較する(同じ場合、trueを返す)
function diffDate(date1, date2){
  if(date1.getFullYear() == date2.getFullYear()){
    if(date1.getMonth() == date2.getMonth()){
      if(date1.getDate() == date2.getDate()){
        return true;
      }
    }
  }
  return false;
}

2行目の const token = ‘●●●’; の●●●のところには自分のトークンを入力します。
※トークンの確認方法は次で説明します。
※シート名を変更した場合は6行目の const sheet = ss.getSheetByName(‘シート1’); のシート名1のところも変更しましょう。

3.トークンの確認方法

自分のチャットワークにログインして、トークンを発行します。

チャットワーク右上の自分の名前をクリック → 「サービス連携」をクリック
新しいタブが開く → 「API Token」をクリック
チャットワークのログインパスワードを入力します
表示されたトークンをコピーします

先ほどのApps Scriptへ戻って●●●の部分へコピーしたトークンを貼り付けてプロジェクトを保存します。

プロジェクトを保存するアイコンは上の方にあります(ちょっとわかりづらいです)
【補足】個人契約ではない場合は管理者の承認が必要なため、こんな画面も表示されます
左側のライブラリから「ライブラリの追加」を行います

スクリプトIDは以下をコピーして貼り付けてください。

1nf253qsOnZ-RcdcFu1Y2v4pGwTuuDxN5EbuvKEZprBWg764tjwA5fLav
スクリプトID
「検索」をクリック → 「バージョン」と「ID」を確認します

・バージョン → 最新バージョン(一番大きな数字)
・ID → 変更なし(ChatWorkClient)

上記を確認したら「追加」をクリックします。

ライブラリの下に追加されたら次に進みます!
4.トリガーの設定をする

トリガーとは・・・ITの分野では、きっかけになる出来事が起こったら自動的に特定の処理を起動するソフトウェアの仕組みなどをこのように呼ぶようです。
ここでは毎日決まった時間にスクリプトを動かしChatworkへ通知するための設定を行います。

https://e-words.jp/w/%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC.html
左側のメニューから時計マーク(トリガー)をクリック
画面右下の「トリガーを追加」をクリック
必要な設定を行い、「保存」をクリックします

以下の内容で設定を行います。

・イベントソースを選択 → 時間主導型
・時間ベースのトリガーのタイプを選択 → 日付ベースのタイマー
・時刻を選択 → 通知したい時間を選択 ※指定した時間の間に実行されます

設定が完了したら「保存」をクリックします。

【注意】ポップアップがブロックされた場合は「常に許可」を選んで「完了」をクリックしてください
※※「このアプリは Google で確認されていません」というエラー表示が出て決まったら・・・

Google Apps Scripsを初めて実行するときに承認作業が必要なため、こんな表示がされてしまう場合があります。
その際は以下の手順で進めてください。

  1. 画面下の「詳細」をクリック
  2. 「××××××[ファイル名](安全ではないページ)に移動」 をクリック
  3. 「××××××[ファイル名]がGoogleアカウントへのアクセスをリクエストしています」という画面が出てくる
  4. 「許可」をクリックすると実行できるようになります。
    (キャプチャなくてすみません^^;)

ここまででAppsScriptの設定が以上です!

実際に予約投稿の動作確認についてはこの後のブログで紹介しますので楽しみにしていてください^^

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。

おすすめツール【Googleスプレッドシート編】2

こんにちは! おまかせ事務代行YPP 伊澤です。

久しぶりに晴れたーーー!と思ったらまた雨…なんてはっきりしないお天気が関東は続いていますね。
休みの日くらいはすっきり晴れてもらって大量の洗濯物を片付けたいものです。

さて、前回から引き続き今日もGoogleスプレッドシートの便利な機能を紹介します。

【うっかりミス対策】Googleスプレッドシートのシート保護を活用しよう

スプレッドシートは「オンライン上のExcelシート」と言えばイメージしやすいと思いますが、
Excelと違う点のひとつとして自動的に上書き保存をするという特徴があります。

例えば確認したいことがあってスプレットシートを開いてすぐに閉じるとき。
その時無意識にセルの情報を書き換えてしまったとすると、
Excelならデータが編集されたので閉じるときに「上書き保存しますか?」とお知らせしてくれますよね。
しかし、スプレットシートは何も知らせずに上書き保存をしてしまうので知らないうちにデータが書き換えられてしまった……!なんてことが起きてしまう場合もあります。

この場合、応急処置として変更履歴を確認すれば「どこのセルを・いつ・誰が・どのように」変更したのかを探しに行くこともできますが、書き換えてから気が付くまでに時間が経っていたり共同作業者が多かったりすると見つけ出す作業が地味に大変です。(←経験者)

前置きが長くなりましたが、こんなトラブルを予め防ぐのがシート保護機能です。
シート保護を使えば勝手に書き換えられたら困るシートもしくは指定のセル範囲を保護することができます。

保護の選択肢は二つです。

警告を表示する場合はシートもしくはセルを編集しようとすると「編集しようとしています。実行しますか?」とポップアップが表示されます。OKをクリックしなければ書き換えられません。

編集できるユーザーを制限する場合は編集できる人を指定できます。
特定の人ではないと編集ができないので、管理者と作業者がいるとしたら管理者だけが編集するシートやセル範囲に保護をしておけば安易に書き換えられる心配もないです。

使い方

  1. [ツール]→[シートを保護]→画面右側にツールバーが開かれます
  2. シートを保護する場合:指定のシートを選択→権限を設定→4へ続く
  3. 範囲を保護する場合:開いたツールバーから範囲を選択→保護したい範囲を選択→権限を設定
  4. 「警告する」を選択→6へ続く
    「編集できるユーザーを制限する」を選択→「自分のみ」を選択→6へ続く
    「カスタム」を選択
  5. 共有中のユーザーから編集権限を付与するユーザーを選択する
  6. 完了

※画面右側のツールバーの上に説明を入力する欄があり空欄でも設定できますが、「何を保護しているのか」や「特定のユーザーのみ編集可能」など保護の目的等を入れておくと見直すときに便利です。

保護されているシートや範囲を確認する方法

[データ]→[保護されたシートと範囲]を選択すると現在シート上でどこに保護を設定しているのかを確認できます。
もちろんここから権限や保護範囲を変更したり削除したりもできます。

終わりに

今回はシート保護について紹介しました。
私も実際に現場で使っていたこともあり、保護をしているのとしていないとでは作業する方にとっても安心感が違うと感じております。
特にお客様のデータをお預かりして編集しているとなるとうっかりミスを起こすわけにはいきません…
触る必要のないデータはあらかじめ保護をしておくとよいでしょう。
ただ、範囲保護を乱立してしまうと後で編集者に変更があったりすると修正するのが大変なので必要最低限に取捨選択することをおすすめします。

https://gsuiteguide.jp/sheets/guide-protect_unprotect_sheet/

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。

おすすめツール【Googleスプレッドシート編】

こんにちは!おまかせ事務代行 伊澤です。

残暑が長く続くかと思いきや、急に冷え込んで何を着ていいかわからない今日この頃です。
自分の服装はおろか子どもは何を着せたらいいかとても迷います。
夜寝るときは私は毛布を被っているのに子どもはパジャマを脱いで裸で転がっている…という我が家の日常です。
みなさまも寒暖差が激しい日が続くと思いますのでお身体気を付けてお過ごしください。

さて、今日はYPPメンバーさんに超おススメのツールのご紹介です!
Googleスプレッドシートを更にさくっと使いやすくしてくれる機能ですのでぜひご一読ください

【同時編集】Googleスプレッドシートの個人フィルタ機能を使おう

手軽に共有できるGoogle版Excelといえばスプレッドシート。
別の場所から複数人で作業することの多いYPPではなくてはならないツールです。

しかし、同時作業中に他の人が編集していて自分の作業がうまく進まなかった経験はないでしょうか?
特に多いのが作成した表をフィルタで絞り込むときです。
作成した表を活用するのに便利なフィルタ機能ですが、複数人で同時に作業するのは難儀なもの。
そんなとき、個人フィルタ機能を使えば他の人には影響を与えずに自分だけの絞り込み表示ができるので、複数人で作業をするときにとても便利です。
また、設定した個人フィルタに名前を付けられるので、自分が見たい絞り込み表示を設定しておけば次回使うときにフィルタで再度絞り込みをする手間が省けます。

フィルタ機能が有効になっている状態です。

使い方

  1. [データ]をクリック
  2. [フィルタ表示]をクリック
  3. [新しいフィルタ表示を作成] をクリック
  4. 画面のデータ周りの領域がグレー表示になる(フィルタ表機能が有効になっている状態)

このフィルタ表示状態にしてから絞り込みを行うと、自分だけの絞り込み表示をすることが出来ます。

使い方はとっても簡単です!

注意点

  • 同時共有&編集できる画面なので、作成した個人フィルタは自分以外の作業者も見ることが出来ます。
    自分が作成したフィルタ以外を触らないように気を付けましょう。
    また、他の人が作成したフィルタ機能を使用する際は声を掛け合って使うようにすることをおすすめします。
  • フィルタ設定後、表に行を追加する場合は要注意です。
    フィルタ範囲の設定は個人フィルタに反映されないのでグレー表示になっている領域の上部に表示されている範囲を修正して正しく絞り込み出来る状態にしましょう。

実際に使っているメンバーさんの感想

受発注業務でこの機能を活用しているメンバーさんに感想をお聞きしました。

2万行を超えるGoogleスプレッドシートの出荷管理表へ注文入力しています。

今までフィルタをかけて作業をすると、すべてのユーザー画面に反映されてしまい他の人の業務に支障がありましたが、個人フィルタを使うことで他の人の画面の見え方を気にせずに作業が出来るようになりました。

また、作成されたフィルタごとに絞り込み条件が保存されるのでフィルタに名前を付けてそれぞれ使い分けています。

例えば出荷管理表に注文を入力する人と出荷管理表から納品書や請求書等の書類を作成する人で業務が分かれていますが、両方担っている人はフィルタを業務ごとに分けて使っているようです。

そうすることでその時見たいフィルタを素早く表示できますよね。

2万行を超える表を使うとしたらフィルタ機能は必須ですね。
使い方はとてもシンプルだけど複数人で編集する場合はとても使いやすいので是非一度試してみてください!

参照元

https://support.google.com/a/users/answer/9308952?hl=ja
https://qiita.com/little_hand_s/items/c02135385698146b3229

<YPPがLINE公式アカウントに登場!>
お得な情報を受け取るには、以下のリンクから友だち追加してください。