訂午餐/訂飲料/團購訂單 使用excel製作 - 3 統計項目唯一值/去除重複值 使用陣列公式

-

上篇文章統計了商品項目，但是中間會有空白的欄位，不太符合一般視覺感官，接下來我們就要將空白的部分移除掉。

作法是將抓出來的商品項目移除掉空白值，重新排序商品項目，由於要一次計算多個項目，所以我們這邊會需要用到陣列公式。

一、陣列公式簡單介紹

案例一：一次計算多個儲存格，回傳一個值

多個值相乘後再加總(A欄 * B欄)後加總，公式為下圖：


計算結果：


如果改為陣列公式計算只需要一行公式就可以解決，只佔用一格儲存格空間，省空間也美觀。
公式改為：
{=SUM(A2:A4*B2:B4)}
注意：A欄的格數要與B來的格數一致
{}為陣列公式符號，不能直接輸入{}，要使用 ctrl + shift + enter 輸入
步驟：
在儲存格內輸入 =SUM(A2:A4*B2:B4) ，按 ctrl + shift + enter 完成儲存格輸入

計算結果：

案例二：一次計算多個儲存格回傳多個值

判斷數值是否大於50
公式為：
{=A2:A11>50}

{}為陣列公式符號，不能直接輸入{}，要使用 ctrl + shift + enter 輸入
步驟：
框選C2:C11，接著輸入公式 =A2:A11>50 ，按 ctrl + shift + enter 完成儲存格輸入


二、所需公式介紹

  • OFFSET(reference, rows, cols, [height], [width])
  • INDEX(array, row_num, [column_num])
  • SMALL(array, k)

• OFFSET(reference, rows, cols, [height], [width])

描述：傳回指定列數及欄數之儲存格或儲存格範圍
reference - 參考位置
rows, cols - 以參考位置為基準的欄列位移量
[height], [width] - 回傳儲存格範圍的寬高

範例：回傳A3到A5儲存格範圍
公式：
=OFFSET($A$3,0,0,3,1)
以A3為基準，欄列位移量為0，高是3寬是1，因為A3到A5有三格。


• INDEX(array, row_num, [column_num])

描述：返回資料表或陣列中由列和欄號索引選取的元素值。

array - 儲存格範圍
row_num - 選取陣列中的列
[column_num] - 選取陣列中的資料行

如果使用陣列公式可以回傳多個值

將C4到C6選起來，輸入以下公式，按 ctrl + shift + enter 完成儲存格輸入 
=INDEX(A2:A11,ROW(A2:A4))
ROW(A2:A4) 回傳行數，2,3,4



• SMALL(array, k)

描述：傳回資料集中第 K 個最小值。
array - 儲存格範圍
k - 由小到大排序後，顯示第幾個值



如果使用陣列公式可以回傳多個值

將C4到C6選起來，輸入以下公式，按 ctrl + shift + enter 完成儲存格輸入 
=SMALL(A2:A11,ROW(2:4))
ROW(2:4) 回傳行數，2,3,4，與ROW(A2:A4)結果一樣


三、取得項目的唯一值

當A2:A11數值為：

不重複的唯一值會是：

將I2到I5選起來，輸入以下公式，按 ctrl + shift + enter 完成儲存格輸入 
=INDEX(A:A,SMALL(IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$11)-1),$A$2:$A$11)=1,ROW($A$2:$A$11),""),ROW($A$2:$A$11)-1))


1.一步一步拆解公式結果

拆解結果



拆解結果的公式

由於是陣列公式，所以每行的公式都會長得一樣



2.將 #NUM! 顯示為空白

在出現計算錯誤的公式最外層加上以下公式：
IFERROR(計算錯誤的公式,"")

所以取得唯一值的公式襪為加上 IFERROR變為以下樣子，記得要弄成陣列公式： 
=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$11)-1),$A$2:$A$11)=1,ROW($A$2:$A$11),""),ROW($A$2:$A$11)-1)),"")

留言

張貼留言

這個網誌中的熱門文章

C# 模擬鍵盤滑鼠控制電腦

-
滑鼠模擬 指定一個螢幕像素的座標位置 int x = 1255; int y = 161; Cursor.Position = new Point(x, y); ClassMouse.LeftClick(); //按下滑鼠左鍵 鍵盤模擬 官方文件 的範例是用小算盤程式，計算111*11= public class demo { [DllImport("USER32.DLL", CharSet = CharSet.Unicode)] public static extern IntPtr FindWindow(string lpClassName, string lpWindowName); // Activate an application window. [DllImport("USER32.DLL")] public static extern bool SetForegroundWindow(IntPtr hWnd); // Send a series of key presses to the Calculator application. public static void Calculator() { // Get a handle to the Calculator application. The window class // and window name were obtained using the Spy++ tool. IntPtr calculatorHandle = FindWindow("ApplicationFrameWindow", "小算盤"); // Verify that Calculator is a running process. if (calculatorHandle == IntPtr.Zero) { MessageBox.Show("Calculator is not running.&q
閱讀完整內容

python nn 聲音辨識 -1 傅立葉轉換

-
辨識環境聲音判斷出這是甚麼聲音 主要想法是用頻域赫茲分布做判斷 首先先錄三種聲音的聲音檔，我每一種聲音重複錄製九個sample，所以有27個wav檔 讀取wav檔案，作正規劃處理，在做傅立葉轉換 一開始由於自己做的fft_domyself() function跟其他聲音處理軟體做出來的不一樣，數值非常小，之後找到有內建的spectrogram() function，使用也是數值很小，最後才想到要20log()，但由於內建的覺得不適合自由調整，所以還是修改自己的function。 import matplotlib.pyplot as plt from scipy.fftpack import fft from scipy.io import wavfile # get the api import numpy as np from scipy import signal import math import pyaudio import wave import time #buf=[] #讀入單一檔案測試 def init(): fs, data = wavfile.read('0106.wav') # load the data b=[(ele/2**16.) for ele in data] # this is 16-bit track, b is now normalized on [-1,1) c = fft(b) # calculate fourier transform (complex numbers list) d = len(c)/2 # you only need half of the fft list (real signal symmetry) plt.subplot(211) plt.plot(b,'b') plt.title('time line') plt.subplot(212) plt.plot(abs(c[:(d-1)]),'r') plt.title('fft') plt.show() return fs, b #取樣率 dat
閱讀完整內容

android 定時通知(永久長期的) 本篇只講AlarmManager使用

-
本篇使用AlarmManager達到準時執行某程式，跟鬧鐘的方式一樣，我之前用過IntentService、Service、Handler，都不能長期使用。 Servicer簡單缺點整理： IntentService：只要關閉activity背景保存視窗，IntentService背景所執行的程式就被強迫結束，好像可以重開(我沒試過)。 Service、Handler：雖然關閉activity會繼續執行，但他是重新在開啟一次service，這到還好，但當手機螢幕關閉處於待機狀態時，service會停止運作暫停在那邊，等你喚醒再繼續執行，所以用這個加timer做定時通知會整個亂掉，timer會停下來不數了。 AlarmManager主要架構是 跟系統註冊鬧鐘甚麼時間點 → 時間到系統會發送一個鬧鈴 → 你的activity接收那個鬧鈴後執行你的程式 當你註冊好幾個鬧鐘已經晚於現在時間，他會馬上通通都執行完 先建立接收鬧鈴的程式 在Manifest.xml 中加入接收系統鬧鈴 <application> <activity android:name=".MainActivity"> </activity> <!-- 當鬧鈴時間到達時要執行的程式 --> <receiver android:name=".AlarmReceiver"> <intent-filter> <action android:name="activity_app" /> </intent-filter> </receiver> </application> 建立一個class檔，執行接收到鬧鈴後的程式 public class AlarmReceiver extends BroadcastReceiver { @Override public void onReceive(Context context, Intent intent) {
閱讀完整內容

raspberrypi 開機自動執行程式 與 在terminal開啟第二個terminal執行python

-
raspberrypi 開啟另一個terminal執行程式 在terminal輸入： lxterminal -e python3 demo.py 參考自： https://www.raspberrypi.org/forums/viewtopic.php?t=65607 ------------------------------------------------------------------------------------------------------------------------------------------------------- raspberrypi 開機自動執行程式 在terminal中輸入以下指令，開啟檔案： sudo nano /etc/rc.local 以下是加入執行python程式 #!/bin/sh -e # # rc.local # # This script is executed at the end of each multiuser runlevel. # Make sure that the script will "exit 0" on success or any other # value on error. # # In order to enable or disable this script just change the execution # bits. # # By default this script does nothing. # Print the IP address _IP=$(hostname -I) || true if [ "$_IP" ]; then    printf "My IP address is %s\n" "$_IP" fi sudo python /home/pi/demo.py  <==加入在這邊 exit 0 參考自： http://dirtypig8.blogspot.tw/2016/12/python.html#!/tcmbck
閱讀完整內容

python opencv 基本讀取、轉換、顯示、儲存等

-
檔案位置關系 filename = 'yourfilename.jpg'  #與程式碼相同位置的檔案 filename = './file/yourfilename.jpg' #程式碼資料夾裡的資料夾中的檔案 filename = '../yourfilename.jpg' #程式碼上一層資料夾的檔案 讀取image 及 顯示 import cv2 filename = 'yourfilename.jpg'     #與程式碼相同位置的檔案 image = cv2.imread(filename)    #讀取照片 cv2.imshow("window title", image)  #顯示照片 儲存照片 import cv2 filename = 'yourfilename.jpg'      #與程式碼相同位置的檔案 image = cv2.imread(filename)    #讀取照片 cv2.imwrite("filename.jpg", image)  #儲存照片，試過的檔案格式 .jpg .png 照片尺寸大小取得 image = cv2.imread(filename)    #讀取照片 hight = image.shape[1] width = image.shape[0] 色彩空間轉換 這邊要注意rgb色彩空間在opencv裡排列方式是bgr image = cv2.imread(filename) yuv = cv2.cvtColor(image,cv2.COLOR_BGR2YUV) #RGB to YUV hsv = cv2.cvtColor(image,cv2.COLOR_BGR2HSV) #RGB to HSV gray = cv2.cvtColor(image,cv2.COLOR_BGR2GRAY) #RGB to gray gray = cv2.cvtColor(image,cv2.COLOR_GRAY2BGR) #gray to rgb 照片三層色分離 import cv2 filename = 'yourfilename
閱讀完整內容