Kuo's 3C 筆記

Excel 應用函數計算教室使用率範例實作：

原始資料如下：

資料是以學年、學期及課號為主的排課方式呈現。

整理資料的想法是先將每門課程在星期一至星期日的每一天排了多少節。

接下來星期一至星期日的節數應該如何計算呢？
計算的想法是把「,」移除後計算字元的長度就是排課的節數。

以課號H9儲存格「SUU121」這個課來說，排課是在星期一的「2,3,4」
目標是變成「234」後計算字元的長度，得知星期一排了3節課。

P9儲存格的公式為：=LEN(SUBSTITUTE(H9,",",""))
先用SUBTITUTE函數將儲存格H9中的「,」移除掉，然後在前面加入LEN函數計數字元後，等於3，由此可知「SUU121」這個課程在星期一排了3節課。

將排課教室儲存格套入公式，將星期一至星期日的節數合計後的結果如下：

一間教室可能排到好幾門課，接下來是把教室的資料整理成「惟一」後，才能加總統計。

把教室這欄複製到新的工作表，移除重複教室。

然後將教室資料貼回原來的資料表，欄位名稱取名為「惟一教室」。

接下來就要用SUMIF函數來整理106-1和106-2的排課資料了。
首先要找的是工作表1中106學年度第1學期上課教室的儲存格O2-O1379，選完儲存格後按F4，定義就是這邊的儲存格。

工作表中的A2，則是要符合的教室。

找到符合的教室後要將W2-W1379中的合計排課節給加總起來。

公式如下：

=SUMIF(工作表1!$O$2:$O$1379,工作表2!A2,工作表1!$W$2:$W$1379)

106-1和106-2都完成後，就可以得到106-1和106-2的排課節數。

假設一週排課45節為100%，將排課節數除以45節，即可得到教室的使用率。

這篇文章好久之前就想寫了，建置的過程也遇到好多坑。

曾經也用過不同的方案，其實也都能運作。
網路上最多人用的應該是買一個樹莓派小主機來建置。

我也曾經在HP MicroServer Gen 8上的VMWare ESXi上架一個Ubuntu Server，
成功的建置了HomeAssistant和Homebridge，架設的過程其實也不難。

如果你有Synology的NAS的話，那你就能快速完成這些設定。

總結爬文和實作過程最簡單的建置方式，記錄和分享給大家。

設備清單：
Synology Nas
Android手機
小米多功能網關
Broadlink rm pro（非必要）
Siri控制設備
--------------------------------------------------
首先，要先入手小米多功能網關（多功能閘道）。
在Android手機設定好後，打開米家app（地區只能設定大陸才找的到）。

選擇多功能閘道

點選右上角「更多」

點選關于

點選網關訊息

把多功能網關的mac address記錄下來

再回到上一頁，點選局域網通信協議

把密碼也記錄下來

以上是準備工作，為建立Homeassistant和Homebridge舖路

--------------------------------------------------------

接下來打開Synology主控畫面，下載Docker

開啟Docker，點選「倉庫伺服器」，輸入「homeassistant」，選第一個下載。

下載好了之後，開啟telnet功能，用putty連上Synology，鍵入以下指令：

sudo docker run -d --name="home-assistant" -e TZ="Asia/Taipei" -v /volume1/docker/home-assistant:/config -v /etc/localtime:/etc/localtime:ro --net=host homeassistant/home-assistant

以上就完成Homeassistant了。接下來只要輸入Synology的網址再加上8123的port就能連上。

當然你一開始看到的畫面不會跟我的一樣。

接下來進去File Station的home-assistant資料夾中，下載configuration.yaml。
這個檔案就是Homeassistant的基本設定檔。
我的設定檔如下：
homeassistant:
# Name of the location where Home Assistant is running
name: Home
# Location required to calculate the time the sun rises and sets
latitude: 22.5083333
longitude: 120.2416667
# Impacts weather/sunrise data (altitude above sea level in meters)
elevation: 25
# metric for Metric, imperial for Imperial
unit_system: metric
# Pick yours from here: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
time_zone: Asia/Taipei

# Show links to resources in log and frontend
introduction:

# Enables the frontend
frontend:

# Enables configuration UI
config:

http:
# Uncomment this to add a password (recommended!)
api_password: 123456789（這邊你要自己修改進去Homeassistant的密碼）
# Uncomment this if you are using SSL/TLS, running in Docker container, etc.
# base_url: example.duckdns.org:8123

# Checks for available updates
# Note: This component will send some information about your system to
# the developers to assist with development of Home Assistant.
# For more information, please see:
# https://home-assistant.io/blog/2016/10/25/explaining-the-updater/
updater:
# Optional, allows Home Assistant developers to focus on popular components.
# include_used_components: true

# Discover some devices automatically
discovery:

# Allows you to issue voice commands from the frontend in enabled browsers
conversation:

# Enables support for tracking state changes over time
history:

# View all events in a logbook
logbook:

# Track the sun
sun:

# Weather prediction
sensor:
- platform: yr

# Text to speech
tts:
- platform: google

# Xiaomi Gateway
xiaomi_aqara:
discovery_retry: 5
gateways:
- key: 888888888888（這邊你要自己改成小米網關中記錄下來的密碼）

# BroadLink
switch:
- platform: broadlink
host: 192.168.1.128
mac: '00:00:00:00:00:00'（這邊你要自己改成Broadlink rm pro的mac address）
timeout: 15
switches:
# 奇美電扇:
fans_chimei:
friendly_name: "chimeifan"
command_on: 'JgCqACwNLA0QKywOLA0sDRArDysQKhAqLA0s8CwOLA0QKiwOLA4sDRAqECsPKxAqLA0s8CwOLA0QKiwOLA4sDRAqECsPKxAqLA0s8CwOLA0QKiwPLA0sDRArECoQKhAqLA0t8CwKLw4QKiwOLA0sDhAqECoQKhAqLQ0s8CwOLA0QKiwOLA4sDRArECoQKhAqLA0t8CwNLA4PKywOLA0sDhAqECoQKhAqLQ0sAA0FAAAAAAAAAAAAAAAAAAA='
command_off: 'JgCqACwNLA0QKywOLA0sDRArDysQKhAqLA0s8CwOLA0QKiwOLA4sDRAqECsPKxAqLA0s8CwOLA0QKiwOLA4sDRAqECsPKxAqLA0s8CwOLA0QKiwPLA0sDRArECoQKhAqLA0t8CwKLw4QKiwOLA0sDhAqECoQKhAqLQ0s8CwOLA0QKiwOLA4sDRArECoQKhAqLA0t8CwNLA4PKywOLA0sDhAqECoQKhAqLQ0sAA0FAAAAAAAAAAAAAAAAAAA='

# 奇美循環扇:
fans_chimeicycle:
friendly_name: "chimeicyclefan"
command_on: 'JgDaACwNLAsSKywOLA0QKhAqECsPKw8rECos8CwNLA4QKiwOLA0QKw8rECoQKhAqECos8CwOLA0QKiwOLA4QKhAqECoQKhArDyss8CwNLA4PKywOLA0QKw8rECoQKhAqECos8CwOLA0QKiwOLA4QKhAqECoQKw8rECos8CwNLA4QKiwOLA4PKxAqECoQKhAqECos8SwNLAsSKywOLA0QKw8rECoQKhAqECos8CwOLA0QKi0OLA0QKhAqECsQKhAqECos8CwOLA0QKiwOLA4QKhAqECoQKw8rECosAA0FAAAAAAAAAAAAAAAAAAA='
command_off: 'JgDaACwNLAsSKywOLA0QKhAqECsPKw8rECos8CwNLA4QKiwOLA0QKw8rECoQKhAqECos8CwOLA0QKiwOLA4QKhAqECoQKhArDyss8CwNLA4PKywOLA0QKw8rECoQKhAqECos8CwOLA0QKiwOLA4QKhAqECoQKw8rECos8CwNLA4QKiwOLA4PKxAqECoQKhAqECos8SwNLAsSKywOLA0QKw8rECoQKhAqECos8CwOLA0QKi0OLA0QKhAqECsQKhAqECos8CwOLA0QKiwOLA4QKhAqECoQKw8rECosAA0FAAAAAAAAAAAAAAAAAAA='

# 嘉儀循環扇:
fans_qy:
friendly_name: "qyfan"
command_on: 'JgBQAAABKZIVNRQRFRAUERMSFBEUERQRFBETNhU2EzYUNhQ2FDYTNhU2EzYUERQRFBEUERQRFBEUERQQFTYUNRQ2FDYUNhQ1FQAFHQABKUgUAA0FAAAAAAAAAAA='
command_off: 'JgBQAAABKZIVNRQRFRAUERMSFBEUERQRFBETNhU2EzYUNhQ2FDYTNhU2EzYUERQRFBEUERQRFBEUERQQFTYUNRQ2FDYUNhQ1FQAFHQABKUgUAA0FAAAAAAAAAAA='

# 書房小捲窗:
window_funsamll:
friendly_name: "Funroom Small Window"
command_on: 'siU0ACENDiAiDSENIQ0hDSINIQ0hDSENDiAhDQ4gDiAOICENDiAOIA4gIQ0hDQ4gDiAOIA4AAVsAAAAA'
command_off: 'siU0ACINDiAiDSENIQ0iDCINIQ0iDSENDiAiDQ4gDiAOICINDiAOIA4gIQ0OIA4gIg0OIA4AAVsAAAAA'

# 書房大捲窗:
window_funbig:
friendly_name: "Funroom Big Window"
command_on: 'siA0ACENIQ0hDQ4gDiAhDSENIQ0hDSENDiAhDQ4gDiAOICENDiAOIA4gIQ0hDQ4gDiAOIA4AAVYAAAAA'
command_off: 'shg0ACENIA0hDQ4gDSAhDSENIQ0hDSANDiAgDQ0gDSANICENDSAOIA4gIQ0OIA0gIA0NIA4AAVYAAAAA'

# 客房1捲窗:
window_yueroom:
friendly_name: "Rooom1 Winodow"
command_on: 'snM0AA4gDiAOICENIQ0OIA4gDiAhDSENDiAhDQ4gDiAOICENDiAOIA4gIQ0hDQ4gDiAOIA4AAVsAAAAA'
command_off: 'siI0ACENIQ0hDQ4fDiAhDSENIQ0hDSENDiAhDQ4gDiAOICENDiAOIA4gIQ0OIA4gIQ0OIA4AAVcAAAAA'

# 客房2捲窗:
window_yoroom:
friendly_name: "Rooom2 Window"
command_on: 'si00ACAOIA4NICAODSEgDiAOIA4gDSAODSEgDg0hDSENISANDSENIQ0hIA4gDg0hDSENIQ0AAVgAAAAA'
command_off: 'sjY0ACENIQ0NICENDSAhDSENIQ0gDSENDSAgDQ4gDiENISENDSAOIA4gIA0OIA0gIQ0NIA0AAVgAAAAA'

group: !include groups.yaml
automation: !include automations.yaml
設定檔改好後，上傳取代configuration.yaml。

設定檔建議下載Sublime Text來編輯，比較一目瞭然。

上面設定檔中的broadlink就是接入Homeassistant的設定，
主要就是ip和mac address。
至於電扇要如何取得那些command_on和command_off的開關遙控碼呢？

進入Homeassistant的網頁，選左下角Developer第一個按鈕，然後選到Broadlink的紅外線學習

點「Call Service」，然後你的Boradlink橘燈會亮起來，
拿起你的遙控器，對著按下去，就可以學到遙控碼。

選左下角Developer第二個按鈕，就可以看到學到的代碼

把這些學到的代碼加到設定檔中，就可以用網頁直接控制你的設備了。

小米多功能網關可接入的設備參考網頁：
https://home-assistant.io/components/xiaomi_aqara/

------------------------------------------
（以上就完成Homeassistant的基本介紹了）

再來就是建置Homebridge，
Homebrige是什麼呢？
大家如果知道蘋果的iPhone或iPad中有一個「家庭」的app，
如果搭配Homekit功能的設備就能用「家庭」這個app來控制，
甚至是呼叫siri來幫你打開或關閉設備。

網路上傳說Homebridge是蘋果的前員工流傳出來的程式碼：
http://kittenyang.com/homebridge-practice/

HomeBridge 的核心是 HAP-NodeJS 这个框架，用 NodeJS 模拟了一个 HomeKit Accessory Server 。作者 KhaosT 是个在美留学的中国人，曾在苹果的 HomeKit 团队实习过，这也 make sense 了为何是他第一个逆向了苹果的 HomeKit 协议。这里面有个小八卦，当 KhaosT 逆向了 HomeKit 的时候写了篇博客，但由于涉及到商业机密被苹果法务要求删除文章，但好在代码已经早就 fork 开了所以代码才得以流传到现在。

以上的文說明了，通過Homebridge，我們可以做到如Homekit的功能。

接下來就在Synology建置Homebridge，讓你用siri來控制設備。

開啟Docker，點選「倉庫伺服器」，輸入「homebridge」，選第一個下載。

下載完後，先不要急著佈署。
先準備二個檔案，config.json和install.sh。

我的config.json如下：

{
"bridge": {
"name": "Homebridge",
"username": "CC:22:3D:E3:CE:30",
"port": 51826,
"pin": "123-45-678"
},
"platforms": [
{
"platform": "HomeAssistant",
"name": "HomeAssistant",
"host": "http://127.0.0.1:8123",
"password": "123456789",（這邊你要自己修改進去Homeassistant的密碼）
"supported_types": [
"automation",
"binary_sensor",
"climate",
"cover",
"device_tracker",
"fan",
"group",
"input_boolean",
"light",
"lock",
"media_player",
"remote",
"scene",
"script",
"sensor",
"switch",
"vacuum"
],
"default_visibility": "visible",
"logging": false,
"verify_ssl": true
}
]
}

我的install.sh如下：

#!/bin/bash
apt-get install -y libavahi-compat-libdnssd-dev
npm install -g homebridge-homeassistant

先在File Station的Docker目錄下建一個「homebridge」的目錄，
然後把準備好的config.json和install.sh這兩個檔案上傳到目錄中。

接下來開始設定佈署，點選進階設定，按下一步