【Excel 小ワザ】オートフィルタで表示された数値を集計する
Excel の表でたくさんの数字を扱っているとき、「これとこれだけだと合計どのくらいになるんだろう？」と一時的に計算してみたくなるとき、ありませんか？
いろんなパターンで集計したいけど、いちいち足し算していくのは面倒くさい。
そんなとき、次の Excel 関数を用いると、オートフィルタと併用して自由に集計することができます！
リストの数値を集計する関数 SUBTOTAL
=SUBTOTAL(集計方法、範囲1,[範囲2],・・・)
（リストやデータベースの集計値を返す）
※[範囲2]以下は省略可能
SUBTOTAL 関数で使用できる集計方法はこれだけあります。
| 集計方法(非表示の値も含める) | 集計方法 (非表示の値を無視する) | 関数 |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
これを使えば、オートフィルタで任意の条件を選ぶだけで、表示した値だけ集計することができます。
（例）オートフィルタで表示させた値の合計を求める場合
① D14セルに =SUBTOTAL(109,D2:D13) と入力します
② D14セルをコピーし、E14～H14セルへペーストします
まだフィルタの条件は「すべて表示」なので、総合計が表示されます
③ 任意の条件でフィルタします
表示された数値の合計が表示されます
（例２）オートフィルタで表示させた値の平均を求める場合
① D14セルに =SUBTOTAL(101,D2:D13) と入力します
② D14セルをコピーし、E14～H14セルへペーストします
まだフィルタの条件は「すべて表示」なので、全体の平均が表示されます
③ 任意の条件でフィルタします
表示された数値の平均が表示されます
ぜひ、お試しください！
【Excel 小ワザ】自動で連続した番号を表示する
Excelで表を作っていて、項目に番号を付与したくなるとき、ありますよね。
一つ一つ数字を入力してもいいのですが、行を削除したり入れ替えたりすると、また数字を入力しなおさなければいけません。
表が大きくなればなるほどその作業は膨大になります。
そんなとき、次の Excel 関数を用いると、自動で番号を表示させることができます！
行番号を返す関数 ROW
=ROW()
（計算式が入力されているセルの行番号を返す）
これを使えば、行の追加や削除をしても同じ計算式が入っている限り、自動で番号を振りなおしてくれるんです。
入力ミスによる欠番や重複もありません。
（例）A1セル～A6セルに連続した番号を表示させたい場合
① A1セルに「=ROW()」と入力する
A1は1行目なので「1」と表示される
② A1セルをコピーし、A2～A6にペーストする
③ A2～A6にそれぞれ行番号が表示される
（例２）1行目にタイトルをつけた表に連続した番号を表示させたい場合
=ROW()-[タイトル行の行番号] と入力します
（応用）横向きに連続した番号を表示させたい場合
列番号を返す COLUMN 関数を使用します
=COLUMN()
（計算式が入力されているセルの列番号を返す）
ぜひ、お試しください！
Raspberry Pi 有線LANと複数の無線LANの固定IPアドレスを設定する
ラズパイの設定が終わり使えるようになったはいいが、小さいラズパイに大きなディスプレイやキーボードがつながっているのが邪魔になったりしていませんか？
別のPCやタブレットからラズパイを遠隔操作すれば、ディスプレイやキーボードは不要です。
リモート接続できるよう、ラズパイのIPアドレスを決めてしまいましょう。
有線LANの固定IPアドレス設定
有線LAN（イーサネット）接続の場合の固定IPアドレスを設定します。設定するIPアドレスを入力し、保存する
# イーサネット（有線LAN）の設定 interface eth0 static ip_address=(IPアドレスを指定する) static routers=(デフォルトゲートウェイのアドレスを指定する) static domain_name=(DNSサーバーのアドレスを指定する)
＜入力例＞
OS を再起動すると、設定が反映される
デスクトップ右上のネットワークのアイコンをクリックすると、設定したIPアドレスで接続されていることが確認できます
無線LANの固定IPアドレス設定
無線LAN（Wi-Fi）接続の場合の固定IPアドレスを設定します。ここでは、複数個所のWi-Fi、ステルス（SSIDを隠している）設定のWi-Fi、パスフレーズの暗号化を行います。
接続するアクセスポイントのパスワードを暗号化する（暗号化しない場合はこの手順は不要）
wpa_passphrase を実行して、パスフレーズを暗号化する
wpa_passphrase "(SSID)" "(パスワード)"
＜入力例＞
実行すると、SSID 、生パスワードのあとに、暗号化されたパスワードが表示される（「psk=」の後の長い文字列）
「psk=」の後に表示されたパスワードをメモしておく（後に使用するため）
暗号化されたパスワードは長いので、空のテキストファイルにコピー＆ペーストしておくとよいです
wpa_supplicant.conf を編集する
/etc/wpa_supplicant/wpa_supplicant.conf を任意のテキストエディタで開く
sudo nano /etc/wpa_supplicant/wpa_supplicant.conf
接続する Wifi の SSID とパスワードを入力し、保存する
network={
ssid="(SSID名)"
psk=(パスワード)
scan_ssid=1
priority=5
id_str="(SSIDの通称)"
}各項目の意味は以下のとおり
| 項目名 | 意味 |
|---|---|
| ssid | SSID名 ※ダブルクォーテーション（”）でくくる |
| psk | パスワード ※生パスワードを入力する場合は、ダブルクォーテーション（”）でくくる ※暗号化パスワードの場合は、くくらずにそのまま入力する |
| scan_ssid | ステルス機能ONのWi-Fiに接続する場合は「1」を設定する ※ステルス機能OFFの場合は、省略可 |
| priority | 複数のWi-Fiを検出した場合の優先順位を数字で入力する 設定した数字が大きいほど優先度が高い ※省略可 |
| id_str | SSIDを識別しやすい文字列を入力する ※ダブルクォーテーション（”）でくくる ※省略可 |
＜複数の Wi-Fi を設定した例＞
dhcpcd.conf を編集する
設定するIPアドレスを入力し、保存する
# Wi-Fi（無線LAN）の設定 interface wlan0 static ip_address=(IPアドレスを指定する) static routers=(デフォルトゲートウェイのアドレスを指定する) static domain_name=(DNSサーバーのアドレスを指定する)
＜入力例＞
/etc/network/interfaces の設定手順を紹介している記事もありますが、最新のOSでは設定不要です
Google AIY Voice Kit を組み立てる③ ～サンプルプログラム実行
前回の記事まででOS も箱の準備もできたので、いよいよ「オーケーグーグル」をできるように設定します。
前提条件
・Google Assistant SDK 0.4.2 以上がインストールされていること
・Google アカウントを取得していること
・Python3 がインストールされていること
・Google Assistant がインストールされたスマートフォンを持っていること
サンプルプログラムの実行
Google Cloud Platform（以下、GCP） プロジェクトの作成
GCP にアクセスし、Google アカウントでログインする
URL：https://console.cloud.google.com/
「無料トライアル～」が出た場合は、無視して「閉じる」をクリックする
「プロジェクトの選択」をクリックする
「新しいプロジェクト」をクリックする
任意のプロジェクト名を入力し、場所を選択して「作成」をクリックする
OAuth 認証
※後にファイルをダウンロードするため、ラズパイ上で作業すること！
作成したプロジェクトを選択して開く
認証情報画面を開く
「API とサービス」－「認証情報」をクリックする
「Oauth 同意画面」を開く
任意のサービス名を入力し、保存する
※入力は必須項目のみでも大丈夫
OAuth クライアント ID を作成する
「認証情報」－「認証情報を作成」－「OAuth クライアント ID」をクリックする
アプリケーションの種類を選択して、作成を実行する
種類は「その他」を選択し、自分でわかりやすい名前をつけましょう
OAuth クライアント ID とクライアントシークレットが発行される
「OK」をクリックする
JSON ファイルをホームディレクトリへ移動する
① デスクトップの Start dev terminal を起動する
② カレントディレクトリをホームに移動する
cd
③ ダウンロードした JSON ファイルをホームに移動する
mv Downloads/client_secret_* .
＜ワイルドカード「*」について＞
例では「client_secret_～」で始まるファイルが1つしかないことを前提にワイルドカード「*」を使用しています。
同じような名前のファイルが複数ある場合は、必ずユニークになるようにファイル指定してください！
④ 移動した JSON ファイルを「assistant.json」のファイル名でコピーしておく
cp client_secret_* assistant.json
OAuth 認証を実行する
① google-oauthlib-tool コマンドを実行する
google-oauthlib-tool --scope https://www.googleapis.com/auth/assistant-sdk-prototype --save --headless --client-secrets client_secret_*
コマンド最後の「--client-secrets」オプションの後に、先ほど移動した JSON ファイル名を指定します。
ファイル名が長いので、ワイルドカードを使用して指定しています。
② 表示された URL（https:// 以下、「prompt=consent」まで）をクリップボードにコピーする
※後の手順でコードを入力するので、ターミナル画面は開いたままにしておくこと
③ クリップボードにコピーした URL をブラウザで開き、Google アカウントにログインする
自分の Google アカウントでログインする
「許可」をクリックする
④ ブラウザに表示されたアクセスコードをコピーし、ターミナルの「Enter the authorization code:」の後に貼り付けて Enter キーを押下する
デバイスを登録する
デバイスを登録する際、モデル名の入力が必要になります。
モデル名は世界で一意である必要があるため、「プロジェクトID」＋任意の文字列の形が推奨されています。
（モデル名にプロジェクトIDを利用する場合）プロジェクトIDを確認する
Google Cloud Platform のプロジェクト選択画面で確認できる
モデル名を登録する
① デスクトップの Start dev terminal を起動する
② カレントディレクトリに移動する
cd
③ コマンドを実行する
googlesamples-assistant-devicetool register-model --manufacturer '任意の文字列' --product-name '任意の文字列' --type デバイスのタイプ --model モデル名
各オプションの意味は、次のとおりです
| オプション | 内容 |
|---|---|
| --manufacturer | メーカー名（任意の文字列） |
| --product-name | 製造者名（任意の文字列） |
| --type | デバイスのタイプ（「LIGHT」「SWITCH」「OUTLET」のいずれかを入力） |
| --model | モデル名（任意のユニークな文字列） |
＜入力例＞
④ 実行結果が「～ successfully registered」となれば登録成功
⑤ 登録したモデル名が正しく設定されていることを確認する
googlesamples-assistant-devicetool list --model
＜実行例＞
言語を日本語に設定する
設定画面を開く
Voice Kit のデバイス名を選択する
※ Google Assistant に反映されるまで時間がかかる場合があるので、表示されない場合は気長に待ちましょう
デフォルトでは「Voice Kit」と表示されます
デバイス名を登録した場合は、登録した名前が表示されます
「アシスタントの言語」をタップする
「日本語（日本）」をタップする
サンプルプログラムを実行する
サンプルプログラムは、以下のフォルダに格納されています
/home/pi/AIY-projects-python/src/examples/voice
現段階では3つのプログラムが実行できます
・assistant_library_demo.py
・assistant_grpc_demo.py
・assistant_library_with_button_demo.py
assistant_library_demo.py を試す
① デスクトップの Start dev terminal を起動する
② Python3 でプログラムを実行する
python3 src/examples/voice/assistant_library_demo.py
サンプルプログラムは Python3 で動作します。
今回試した環境は Python2 と Python3 が同居しており、デフォルトは Python2 だったので、サンプルプログラムを実行する際のコマンドは「Python3」としています。
＜Pythonバージョン確認＞
③ （初回実行時のみ）ブラウザが立ち上がるので、アクセスを許可する
④ （初回実行時のみ）「認証フローが完了した」という旨のメッセージが出るので、ブラウザを閉じる
⑤ ターミナルに待ち受け状態であるメッセージが表示される
⑥ ボタンがゆっくり点滅するので、「OK,Google」と呼びかける
ボタンが点灯したら、天気などを話しかけてみましょう！
日本語で回答してくれます。
⑦ Ctrl + C で終了
assistant_grpc_demo.py を試す
① デスクトップの Start dev terminal を起動し、Python3 でプログラムを実行する
python3 src/examples/voice/assistant_grpc_demo.py
② ボタンがゆっくり点滅するので、ボタンを押す
③ ボタンが点灯したら、天気などを話しかけてみましょう！
④ ターミナル画面に、認識された音声が表示されると同時に、日本語で答えが返ってきます
assistant_library_with_button_demo.py を試す
「OK,Google」か、ボタンを押すことでボタンが点灯して質問を待ち受けます。
その後の動作は assistant_library_demo.py と同じです。
いろいろ話しかけて遊んでみましょう！
Google AIY Voice Kit を組み立てる② ～配線と箱の組み立て
前回の記事では OS の準備をしました。
つぎは、Voice Kit を開封して組み立てましょう。
準備
ラズパイに各種部品を取り付ける
ラズパイにスペーサーを取り付ける
ラズパイの穴に比べてスペーサーの差込部分が大きく、そのまま取り付けようとするととても硬い！
一度取り付けると二度と取れなくなりそうだったので、差込部分をペンチでつぶして細くしてみました。
右上がもともとのスペーサー
左下が差し込み部分をつぶしたスペーサー
つぶしたスペーサーを、HDMI ソケット側の黄色い穴に差し込む
ラズパイに Voice HAT をかぶせる
GPIOピンの位置がずれないように注意！
しっかり差し込まないと、Voice HAT が機能しません。
（私はここで差し込みが甘かったため、実行時に何も反応せずハマっていました・・・）
スピーカーを接続する
精密ドライバー（プラス）を使用します。できるだけ細いほうがいいです。
精密ドライバーで、青いターミナル上部のネジを緩める
基盤上の「＋」「－」の位置を確かめ、
スピーカーの赤いケーブルを「＋」（左側）、黒いケーブルを「－」（右側）に差し込む
精密ドライバーでネジを締めて、ケーブルを固定する
ボタン用のケーブルを接続する
ボタンに接続するケーブル（一方が白いプラスチック、もう一方が4本の金属端子になっているケーブル）を、Voice HAT の「Button」と書いてあるソケットに差し込む
※差し込むケーブルの向きに注意！
白いプラスチックの凸が、ラズパイの外側（USB側）を向くように差し込みます
カチッと音が鳴るまで差し込んでね
Voice HAT と Voice HAT Microphone をケーブルで接続する
マイク用のケーブル（両端とも白いプラスチックが付いているケーブルが5本くっついたやつ）をマイクに差し込む
※ケーブルのどちら側でも差し込めるが、差し込む向きに注意してください（凸のあるほうが上になるように）
これもカチッと音が鳴るまで差し込んでね
ケーブルのもう一方を Voice HAT の「Mic」と書かれたソケットに差し込む
※差し込むケーブルの向きに注意！
白いプラスチックの凸が、ラズパイの外側（USB側）を向くように差し込みます
これもカチッてなります
基盤はいったんよっこします
ダンボール筐体を組み立てる
大きいほうのダンボールを折り目に沿って角柱型に折る
底を「FOLD 1」から「FOLD 4」まで順番に折り込む
※インターフェース用の穴が空いている部分が折れてしまわないよう注意！
※「FOLD 4」は「FOLD 1」の下に折り込みます
ふた側も折り目に沿って折っておく
小さいほうのダンボールの「①」と「②」が印刷されている面を折る
谷折り
スピーカー固定部分を折る
「FOLD UP」の下の切込みを裏側へ折り曲げる
（ビンゴカードの「FREE」みたく）
「FOLD UP」の矢印箇所を谷折り、矢印の間の小さい折り目も谷折りに折る
「SPEAKER HERE」のすぐ上の折り目を山折り、さらに上の折り目も山折りに折る
ラズパイ側を折る
「RASPBERRY PI GOES HERE」の上の折り目を谷折りに折る
ダンボール筐体にラズパイとスピーカーをセットする
小さいほうのダンボール「SPEAKER HERE」の場所にスピーカーを置く
ラズパイとスピーカーをダンボールの箱に入れる
※ microSD カードは必ず抜いた状態で作業を行うこと！
（カード破損の恐れがあるため）
ラズパイのGPIOピン側とスピーカーは、箱側面の丸い穴が7つ開いている面に向ける
ラズパイを底に落ち着かせてからスピーカーの位置を整えると、収めやすい
プッシュボタンを取り付ける
プッシュボタンを箱のふたにある穴に差し込む
黒いプラスチックのナットをふたの裏側から取り付ける
ナットの丸くなっている面をふた側になるように取り付ける
ナットを締めてボタンを固定する
ボタンの裏側は「宝」の文字が読める向きに
左上から、
白 黒
青 ピンク
となるように端子を差し込む
それぞれパチンと音がなるまで差し込む
万が一、すでに固定された端子を差し直す場合、マイナスの精密ドライバーを接続箇所に差し込むと抜けやすくなる
マイクを取り付ける
※両面テープを使用します
ふたの裏側から見て右側の穴にマイクボードの「Right」、左側の穴にマイクボードの「Left」側のマイクを合わせ、位置を決める
そのままマイクボード真ん中のトゲトゲをふた裏に押し込み、跡をつけておく
（写真では「MIC BOARD」の文字のあたりに跡がついている）
トゲトゲの跡をつけた場所に両面テープを貼る
※テープでマイクの穴をふさがないよう注意すること！
トゲトゲの跡とマイクの穴に合わせて、マイクボードをふたの裏に貼り付ける
箱の上から見て、穴からマイクが見えることを確認する
ケーブルを箱に収めてふたを閉じる
できあがり！
動作確認
ディスプレイ、キーボード、マウスを接続する
最後に AC 電源アダプタを接続し、コンセントにつなぐ
ディスプレイにデスクトップ画面が表示されることを確認する
スピーカーとマイクのチェックを行う
① デスクトップの「Check audio」をダブルクリックする
② ターミナル画面が開き、スピーカーから「Front,Center」と音声が大音量で流れる（結構びっくりします）
ターミナル画面に「テスト音が聞こえた？」と表示されるので、音声が聞こえていたら「y」を入力してEnter
③ 次に「Enterキーを押して『Testing,1,2,3』」と言え」と指示される
言われたとおりにEnterキーを押し、何か話す（「Testing,1,2,3」ではなくてもよいのだが）
④ しばらくすると、先ほど話した音声がスピーカーから聞こえてくる
「自分の声が聞こえたか？」と表示されるので、聞こえていたら「y」を入力してEnter
⑤ 「オーディオは機能しているよ」とメッセージが表示されるので、Enterを押して動作確認を終了する
※LEDボタンが光るかどうかは、後のデモプログラム実行時に確認できます
Google AIY Voice Kit を組み立てる① ～OSのインストール
ひょんなことから、Google AIY Voice Kit というオモチャを手に入れました。
Linux の経験はなく、「ラズパイ」という存在も今回初めて知りました。
今さら感がありますがLinuxに触れるいい機会なので、探り探り楽しんでみます。
準備
【用意したもの】
Voicd Kit だけでは何もできません。
立上げに必要なものを揃えます。
・Raspberry Pi 3モデルB（ケースなしバージョン）
・Google AIY Voice Kit
・SDカード（16GB）、SDカードアダプタ
・AC電源アダプタ（microUSB接続）
・ディスプレイ（HDMI接続）
・キーボード（USB接続）
・マウス（USB接続）
・PC（空き容量 8GB 以上）
・（PCに直接 SDカードを差し込めない場合）SDカードスロット
・十分な時間
【道具】
組み立ての際に使用します。
・精密ドライバー
・両面テープ（1センチ幅程度）
OSのインストール
すぐに組み立てたい気持ちをおさえて、まずは時間のかかる OS のインストールからはじめます。OS イメージのダウンロード（※1時間前後かかります）
GitHub の以下のリンクより、最新バージョンのOSをダウンロードできます。
ダウンロードに時間がかかるので、この間に配線と箱の組み立てをやると効率よく進められるかも。
※クリックするとダウンロードを開始しますhttps://dl.google.com/dl/aiyprojects/aiyprojects-latest.img.xz
ちなみに、バージョン番号や過去バージョンは以下のリンクから確認できます。
フォーマットツールを使用して microSD カードを初期化する
（SD Card Formatter 5.0 の例）
① 「上書きフォーマット」を選択
② 「フォーマット」をクリック
③ 「はい」をクリック
ダウンロードしたファイルを解凍する
ダウンロードしたファイルはXZ形式で圧縮されているため、解凍しておきます。
ディスクイメージライティングツールを使用して microSD カードに img ファイルを書き込む
① 解凍した img ファイルを指定
② 「Write」をクリック
③ 「Yes」をクリック
これで、OSの準備は完了です。
次は、Voice Kit の組み立てに入ります。
