https://tanuhack.com/useful-tool/spreadsheet/upscale-functions/ 2018.03.22 2018.07.08 知っていたらドヤれる!スプレッドシート独自の関数を10個まとめてみたこんにちは、色んな効率化が大好きなたぬ(@KAivlys)です! 今回紹介する10個の関数は全て、スプレッドシートのオリジナル関数です。 スプレッドシートには『ネットに繋がっていないと使えない』というデメリットがありますが、『常にオンラインである』という性質を最大限活かしたグレイトな関数がたくさん用意されています。 どれも強力すぎて、普段Excelを使っている人から見れば、カルチャーショックを受けるかもしれません! 効率化が捗ること間違いなしなので、1つでも持ち帰って積極的に使ってみて下さい。 目次 [表示] IMPORTRANGE:別のスプレッドシートから値を参照 構文 =IMPORTRANGE("スプレッドシートキー","範囲指定した文字列") 例:IMPORTRANGE("1-qd48UQf8EJMnEFELTCxZJ5KoTplq--BqeBwlJLBXto","挿入される元データ!A1:J30") 個人的に一番気に入っている関数です。 Excelと違って、別のスプレッドシートの値をリアルタイムで引っ張ってこれるのは、やっぱり便利ですね。 IMPORTRANGE関数を使うとスプレッドシートのクラウド管理の恩恵が、いかに素晴らしいか思い知らされてます…! 応用技:データベースのように使う 通常スプレッドシートには、200万セルしか文字を入力できませんが、IMPORTRANGEを使えばその制約を無理矢理解除することが出来ます。 スプレッドシートの『IMPORTRANGE』関数で200万の制約が無くなるって本当? 2017.12.26 ARRAYFORMULA:複数セルへ関数を一気に反映 例:TEXT関数を最終行まで反映させる 構文 =ARRAYFORMULA(配列数式または、非配列関数で配列を使用) 例:ARRAYFORMULA(TEXT(B3:B12,"ddd")) ARRAYFORMULA関数には、専用のショートカットキーがある なんと入力補完用のショートカットキーが用意されています。『ARRAYFORMULA』って入力するの大変ですもんねw セルに『=』を入力して、以下のショートカットキーを試してみてください! ショートカットキー • Mac:Command+Shift+Enter • Windows:Ctrl+Shift+Enter 【保存版】実務の効率化が加速するスプレッドシートのショートカットキー17選! 2018.05.06 読み込み速度を速くしたいならARRAYFORMULA一択! Googleスプレッドシートは、関数を数行に渡って入力するより、ARRAYFORMULAを使ったほうが断然速いので、積極的に使っていきましょう。 スプレッドシートの『ARRAYFORMULA』関数を使って表示速度を高速化させよう! 2017.12.30 UNIQUE:参照範囲をリアルタイムで重複削除 構文 =UNIQUE(範囲) 例:UNIQUE(A2:B21) 重複する行を破棄して、指定したソース範囲内の一意の行を返します。行はソース範囲内の先頭から順に返されます。 重複を削除した表を作りたいなら、この関数で決まりですね! 【知ってると便利】重複データを削除するスプレッドシートのUNIQUE関数の紹介 2018.04.09 FILTER:参照範囲をリアルタイムでフィルタリング 例:『商品名=A、個数2以上』でフィルタリング 構文 =FILTER(範囲, 条件1, [条件2, ...]) 例:FILTER(A3:C21,B3:B21="A",C3:C21>1) ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。 FILTER関数を使うメリットは、元データを変更しないフィルタリングなので、データの破損の恐れがないことですね^^ ただ、一つだけデメリットを挙げるならば、関数の性質上、条件にはAND検索しか使えないのがネックです…。 OR検索を行いたい場合は、後述しているQUERY関数を使えば解決出来ます。 SORT:選択範囲をリアルタイムで並べ替え 例:『品番を昇順でソート』 構文 =SORT(範囲, 並べ替える列, 昇順, [並べ替える列2, ...], [昇順2, ...]) 例:SORT(A3:B6,A3:A6,1) 指定した配列または範囲の行を、1列または複数の列の値に従って並べ替えます。 こちらもFLITER関数と同様に、元のデータを変更しないソートなので、データの破損の恐れがないのがいい感じです。 ソートの優先順位は前にあればあるほど強くなります。 QUERY:参照範囲をリアルタイムで配列集計 例:『商品名=A、個数2以上、個数を昇順でソート』 構文 =QUERY(データ, クエリ, [見出し]) 例:QUERY(A3:C21,"WHERE B='A' and C>1 ORDER BY C,A asc") 『Google Visualization API』のクエリ言語を使用して、データ全体に対するクエリを実行します。 少しだけ敷居が高そうに見えますが、実はそんなことはありません。 上手く使いこなせるようになれば、QUERY関数だけで前述したFLITERやSORT関数を組み合わせたような数式を作ることが出来ます。 クエリ言語 クエリは、SQLチックな書き方で記述します。 SELECT:表示させたい列だけ表示させる 構文 =QUERY(選択範囲,"SELECT 列1,列2,列3") WHERE:条件を満たした行のみ表示させる 構文 =QUERY(選択範囲,"WHERE 条件範囲列=条件") QUERY関数の6個のWHEREオプションで、データ抽出を捗らせる方法! 2018.07.06 ORDER BY:ソートさせる 構文 =QUERY(選択範囲,"ORDER BY 並び替えの基準列 asc(昇順)または、desk(降順)") 実践編:クエリ関数の使い方 例:『商品名=A、個数2以上、個数を昇順でソート』 例題 =QUERY(A3:C21,"WHERE B='A' and C>1 ORDER BY C,A asc") この場合だと、『B列の値がA』かつ『C列の値が2以上』を抽出して、C、A列を基準に昇順でソートしています。 スプレッドシートのQUERY関数で、条件に一致するデータをまとめて抽出する方法 2018.07.01 IMPORTHTML:HTMLページの表やリストを参照 構文 =IMPORTHTML(URL, クエリ, 指数) 例:IMPORTHTML("https://support.google.com/docs/answer/181110?co=GENIE.Platform%3DDesktop&hl=ja","table",4) HTMLページ内の表やリストからデータをインポートします。 Webサイトにあるテーブルをいちいち入力しなくて良いのは物凄く便利な機能だと思いませんか? クエリに指定する値 テーブル table リスト list IMAGE:HTMLページの画像を参照 構文 =IMAGE(URL, [モード], [高さ], [幅]) 例:IMAGE("https://tanuhack.com/wp-content/uploads/2018/03/upscale-functions.jpg",4,200,300) セルに画像を挿入します。 文字だや数字だらけになりがちなスプレッドシートだからこそ、画像があったほうが分かりやすくなりますよね。 GOOGLETRANSLATE:テキストをGoogle翻訳 構文 =GOOGLETRANSLATE(テキスト, [ソース言語], [ターゲット言語]) 例:GOOGLETRANSLATE(A2,"en","ja") テキストをソース言語からターゲット言語に翻訳します。 Google翻訳では、複数まとめて翻訳できないので、スプレッドシートで一気に翻訳できるのはありがたいですね。 日本語 ja 英語 en 中国語 zh 韓国語 ko ドイツ語 de フランス語 fr イタリア語 it REGEXEXTRACT:正規表現で文字列を置換 例:正規表現を使って、住所から都道府県を抜き出す 構文 =REGEXEXTRACT(テキスト, 正規表現) 例:REGEXEXTRACT(A2,"..+?[都道府県]") 正規表現に従って、一致する部分文字列を取り出します。 「正規表現?何それ、おいしいの?」という方は過去記事をどうぞ! 知らないと損!正規表現を使ってスプレッドシートの文字列を便利に抽出する方法 2018.01.27 まとめ 今回は、知っていたらドヤれる、スプレッドシート独自の関数を10個ほど紹介しました。 他にも独自の関数は山ほどあるんですけど、あまり実用性がないので省略してます。 この記事をきっかけにExcelからスプレッドシートに乗り換えてくれる人が増えると嬉しいなあ…。 https://www.relief.jp/docs/google-spreadsheet-function-sort-unique-filter.html • 2018年09月25日(火) 15時40分 • Googleスプレッドシートの使い方 SORT・UNIQUE・FILTER関数がExcelにも 対象:Googleスプレッドシート, 将来のExcel Googleスプレッドシートには、昔から存在していた、SORT・UNIQUE・FILTERなどの関数が、ようやくExcelでも使えるようになるようです。 • Bringing AI to Excel--4 new features announced today at Ignite 新しく使えるようになる関数 上記のブログ記事によれば、 FILTER UNIQUE SORT SORTBY SEQUENCE SINGLE RANDARRAY といった、配列を返す関数が使えるようになるようです。 もちろん2018年9月25日時点のExcel 2016に、これらの関数は存在していません。 SORT関数・UNIQUE関数・FILTER関数 記事で公開されている画像を見ると、SORT関数・UNIQUE関数・FILTER関数は、Googleスプレッドシートの、同じ名前の関数と同じ挙動をするようです。 並べ替えたデータを取得するSORT関数 並べ替えたデータを取得するのがSORT関数です。 ▼操作概要:データを並べ替える A5:A11セルのデータをB5セル以下に並べ替えて表示する B5セルに =SORT(A5:A11) という数式を入力する 重複しないユニークなデータを取得するUNIQUE関数 重複しないデータを取得するのがUNIQUE関数です。 ▼操作概要:重複しないデータを取得する D5:D12セルのデータから重複しないデータをE5セル以下に表示する E5セルに =UNIQUE(D5:D12) という数式を入力する フィルタリングしたデータを取得するFILTER関数 指定した条件でフィルタリングするのがFILTER関数です。 ▼操作概要:データをフィルタリングする G5:G11セルのデータをH5:H11セルの「Veg」でフィルタリングしてI5セル以下に表示する I5セルに =FILTER(G5:G11,H5:H11="Veg") という数式を入力する 最終更新日時:2018-09-27 17:13 ▼「インストラクターのネタ帳」を運営する伊藤潔人が、初めて書かせていただいた本『いちばんやさしいExcel VBAの教本』が、2018年1月に発売されました。 エクセルの鉄則:達人であることは隠せ 表計算ソフト「エクセル」は単純であると同時に気が遠くなるほど複雑だ Can you help me with this Excel formula? By Ira Iosebashvili 2018 年 10 月 10 日 07:50 JST 米イリノイ州オークブルック在住のコンサルタント、アーナンド・カレルカル氏(37)が大手保険会社で新しい仕事に就くと、社内からインスタントメッセージや電子メールが次々に舞い込み、カフェテリアでは自己紹介をしたがる同僚が相次いだ。 間もなく、カレルカル氏は自身の人気の理由に気付いた。多くの同僚は同氏がマイクロソフトの表計算ソフト「エクセル」の達人だと聞きつけ、手に負えない表計算シートやピボットテーブルをどうにかしてほしいと助けを求めてきたのだった。 「次々と人がやって来ては『エクセルが得意だと聞いたよ』と言う」とカレルカル氏。最近は「遠回しに不満を伝えるように」なり、助けを求める人には「ここに来る前にグーグルで調べて」と言うようになったという。 エクセルの達人はそのことを隠そうとする。マイクロソフトが1985年に発売したエクセルは、世界で数億人が使うまでに成長。かつては手作業や基本的なコンピュータープログラムで処理していた無数の事務処理をシンプルにし、予算編成やグラフ作成などに携わる人の負担を軽減した。上級ユーザーは、数千人の従業員の支出を把握するといった離れ業をやってのける。 アーナンド・カレルカル氏 PHOTO: ANAND KALELKAR
同時に、エクセルに精通した人たちの生活は以前より面倒なものになった。エクセル名人は「循環参照」や「セル結合」、素人の同僚が組んだめちゃくちゃなマクロと絶えず戦っている。 ピッツバーグ大学で働く統計専門家のアンドリュー・アルトハウス氏(32)は「『エクセルのシート数枚』について助けてほしいと言われたら、まわれ右をして逃げる」とツイート。「さらに、偽の電話番号や偽名を教えたくなるかも。極端な場合、死んだふりが良いかもしれない」と述べた。 問題の「エクセルシート数枚」を見せられたのは最近だ。それぞれのシートに列が400あり、不適切な条件やNG事項のオンパレードだった。修正には数週間かかった。 アルトハウス氏は手を貸す前に「何に足を踏み入れようとしているのかを把握する」ようエクセルの達人たちにアドバイスしている。 マイクロソフトでオフィスとウィンドウズのマーケティングを担当するジャレッド・スパタロ氏は最近、「信じられないほど柔軟なアプリケーション」である「エクセルのパワーはシンプルさから来ている」とブログに投稿した。 また同社の広報担当者は、「全ユーザーに新たな可能性を開く」人工知能(AI)機能が最近エクセルに加わったと述べた。 だがインテルのエンジニア、ジョン・ミカラス氏(48)が同僚の緊急事態に何年も対処してきた経験から学んだのは、エクセルの達人であることは隠すのが得策だということだ。 Demonstration of Microsoft Excel Demonstration of Microsoft Excel PHOTO: MICROSOFT トラブルは、エクセルに本当に詳しい人を探すグループメールで始まることが多い。ミカラス氏によると、「本当に簡単なことだ」と言われて「見てみると、簡単なことではない」のだという。 最近では、ミラカス氏は息をひそめ、切羽詰まった状況になってから助けるようにしている。意志の強さを試されたこともあった。プレゼンテーション中にめちゃめちゃな表計算シートを見て、「私に聞いてくれれば良いのに」と叫びたい衝動に駆られたのだ。 「私は利他主義者だが、世界を救うのは私の仕事ではない」とミラカス氏は語る。 ニュースレター購読 マサチューセッツ州でデータアナリストとプロジェクトマネジャーをするジェン・リプシッツ氏(32)は、自身や部内の同僚が他の同僚からエクセルについてよく相談を受けると話す。 リプシッツ氏によれば、彼らは「これはジェンの案件だ。彼女はスマート部門にいる」などと話し、データがおかしくなっている原因がわからない時は同氏に聞きに行けばいいと考えている。 同氏の解決策は「ただそこに立っている」ことだ。同僚が問題を説明するうちに自ら解決することも多いという。 リプシッツ氏は、エクセルの可能性に圧倒される人もいると考えている。エクセルは単純であると同時に気が遠くなるほど複雑だからだ。「人はエクセルにあまりに多くのことができるとおじけづく。トライする必要があることを忘れてしまう」 関連記事 財務部門で進む「エクセル離れ」 AIの未来、表計算ソフトの歴史がヒントに 職場のチームプレーで燃え尽きないために
[18初期非表示理由]:担当:要点がまとまってない長文orスレ違いの長文多数により全部処理
|