【別スプレッドシートからのデータ参照】特定行の抜き出し方法。FC本部の会員・売上一元管理シートを作ってみた。

月額制の会員サービス（スクールやオンラインサロン）を想定した名簿管理ツールを作ってみました。
FC本部が各FCのデータを一元管理し。本部マスターを更新したら、各FC用のスプレッドシートに自動反映します。

■会員マスター名簿を作る（本部用）

全ての校舎の名簿を作ります。
入退会イベントごとに、入会日or退会日、区分（通学/退会）を更新するという想定です。

■別スプレッドシートからの参照はIMPORTRANGE関数を

一番左上のセルに下記を記入すると、C列に「秋葉原校」があるセルだけを抜き取ってきます。

= query(IMPORTRANGE(“スプレッドシートID”,”名簿!A1:G”),” where Col3 = ‘秋葉原校'”)

※スプレッドシートIDとは下記の○○○○○○○○にあたるものです。
　　https://docs.google.com/spreadsheets/d/○○○○○○○○/edit

※select文に使う列指定は「Col」を使います。「col」だと動きません。

※基本構文は = IMPORTRANGE（“スプレッドシートキー”,“シート名!範囲の文字列”）

■COUNTIFS関数を使って、会員数、入会者数、退会者数を出す

■会員数 = その日までの入会者数 – その日までの退会者数

= COUNTIFS(‘名簿’!$E:$E,”<=”&B2) – COUNTIFS(‘名簿’!$F:$F,”<=”&B2)

※「$」をつけることで、ドラッグコピーした時も、セル位置が固定となります。
※「&」で変数を引っ張ってきます。今回は該当セルの値で期日になります。ドラッグコピーできます。

■入会数 = その期間の入会者数

= COUNTIFS(‘名簿’!$E:$E,”>=”&B1,’名簿’!$E:$E,”<=”&B2)

■退会数 ＝ その期間の退会者数

= COUNTIFS(‘名簿’!$F:$F,”>=”&B1,’名簿’!$F:$F,”<=”&B2)

■別スプレッドシートの売上行をごっそり持ってくる

起点となるセルに下記を記述すると、右にその行がゴソっと抜き取られます。

= IMPORTRANGE(“スプレッドシートID”,”売上推移!B4:4″)

※秋葉原校の売上 = B4からはじまる行データ

■各校舎の会員数推移を集計する

「名簿」シートのC列の「東京本校」を抜き出し、その日までの「入会者数 – 退会者数」を求める
= 　COUNTIFS(‘名簿’!$C:$C,$A3,’名簿’!$E:$E,”<=”&B1)　
　-　 COUNTIFS(‘名簿’!$C:$C,$A3,’名簿’!$F:$F,”<= “&B1)

■複数の条件に一致するデータの個数の求め方

= COUNTIFS（範囲1, 検索条件1, 範囲2, 検索条件2, …）

※A3は「東京本校」
※ドラッグコピーでセルが変わらないように＄を入れる
※＆で変数指定。期日データを参照。ドラッグコピーできるように。

■サブスク型ビジネスのKPIの説明

■退会率：Churn Rate

＝　当月退会者数/前月の会員数　

■ACLT ：Average Customer LifeTime

= 1 / 退会率

平均利⽤継続⽉数となります。

■LTV：Life Time Value　

= 客単価 * 滞在月数

■入会が止まった場合の予想累計売上（事業価値）

= LTV * 会員数

将来生み出すであろう累計売上予想となります。

■GASスタンドで販売中

■フランチャイズ本部の会員名簿。入退会とKPIを一元管理。各FC向けの別シートにも自動反映