【Excel】函數公式第3問

羅仔
個人積分：6分
文章編號：86897970

6分

樓主

2023-02-18 23:05

很謝謝各位大神的解答，我問的函數公式第1問及第2問，在短時間內就獲得解答，而且還不只一種方法，讓我獲益良多，在此我又發起了第3問，題目如下圖所示，請不要回我「樞鈕分析表」，我是要練習寫出函數的公式，先謝謝大家囉

【Excel】函數公式第3問

有需要此範本檔案的在這：
https://drive.google.com/drive/folders/164vHOditec1mJ1-QH4zFzS-N4DpTyPNL?usp=sharing

2023-02-18 23:05 發佈

文章關鍵字 excel 函數公式

zxcvbnm2434

zxcvbnm2434
個人積分：5771分
文章編號：86898270

5771分

2樓

2023-02-19 0:24

不就三階正規化
鍵[男女] -->類別(姓名)<->引數[數值] 遇到多項次就是進行矩正排序抓N的第M項進行函式計算
帶用類別的時候排序姓名[引數]
若使用引數的時候去排序男女[鍵]
使用鍵的時候帶出引數排序[姓名]
https://zh.wikipedia.org/zh-tw/%E7%AC%AC%E4%B8%89%E6%AD%A3%E8%A6%8F%E5%8C%96

簡單來說鍵只會有單一 (男或女 OR) (單一男 NOR 單一女) (男加女 AND)
(類別)姓名可以重複存在<->對應的就是(數值)

看懂資料庫正規化就知道怎麼做了
盲目套用人家寫好的參數沒太大意義
要了解主鍵連結的指標內容
這樣寫程序的時候才知道怎麼做方程
不會因為數據一多就不知道"主從"關係

用套用的函式寫其實並不好寫(沒有基礎理解不了) 只是針對那些不會的人去套參數還是搞不懂其中的原理

羅仔樓主

謝謝您

2023-02-19 22:05

jaredsam

jaredsam
個人積分：1547分
文章編號：86898413

1547分

3樓

2023-02-19 1:21

我都習慣+輔助列或是複製資料到另一個分頁做資料轉換原分頁保留避免資料遺失
不然真的很難用...

假設輔助列的話
D4=IF(B4="","",COUNTA(B$1:B4)-2)
G4=IF(E4="","",COUNTA(B:B)+COUNTA(E$1:E4)-4)
以標記總共人數
L4=1 L5=N+1 即可

J4=IF($L4<=(COUNTA(B:B)-2),INDEX(B:B,MATCH($L4,D:D,0)),INDEX(E:E,MATCH($L4,G:G,0)))
就可以按照姓名排序了
K4=複製

嫌棄輔助列礙眼文字顏色改成白色即可~

點我看大圖

jaredsam

jaredsam
個人積分：1547分
文章編號：86899708

1547分

4樓

2023-02-19 11:06

早起想到另一個方式
不用輔助列
可是公式就很複雜喔~

J4=IF(COUNTA(J$1:J3)+1<=(COUNTA(B:B)),B4,INDEX(E:E,COUNTA(J$1:J3)-COUNTA(B$1:B3)+4))
K4=複製

公式:
假如計算J列數量小於等於B列數量，直接顯示女性姓名，不然則顯示男性第(J列數量-女性數量)

ps1 因為counta不能計算自己，COUNTA(J$1:J3)再+1 才是J1-J4數量
PS2 因為counta不能計算自己，COUNTA(J$1:J3)-女性到J3數量，所以不用額外+1，另外+4是因為男性是從第四個開始排序

羅仔樓主

非常謝謝您詳細的解說，確實~輔助列可幫助我們簡化公式，另外你也給我不用輔助列的方法，超級感動

2023-02-19 12:27

Yaude Huang

Yaude Huang
個人積分：5289分
文章編號：86901126

5289分

5樓

2023-02-19 16:23

用這兩個就可以了

=FILTER(B:C, ISNUMBER(C:C))
=FILTER(E:F, ISNUMBER(F:F))

用ISNUMBER來避掉前面的TITLE部分，剛好成績是數字，用這方式把有數字的欄位挑出來

擺的太遠會有間隔

擺得太近會出現溢出錯誤，女生資料跟男生資料重疊

擺剛好就合併完成，唯一要做的就是複製貼上，橋到剛好的位置

至於這樣類似的方法能不能合併，用一行指令完成，我還沒有想法

羅仔樓主

非常謝謝您喔，感恩

2023-02-19 22:07

Yaude Huang

Yaude Huang
個人積分：5289分
文章編號：86901357

5289分

6樓

2023-02-19 17:26

另一個簡單的方式

=VSTACK(B4:C6,E4:F5)

如果是要自動產生，我通常會這樣做

我會加上一排特地標顏色的區塊，然後把所有欄位拉到這個區塊上

如這範例就是拉到C11 F11
=FILTER(VSTACK(B4:C11,E4:F11),VSTACK(C4:C11,F4:F11) <> "")

然後想辦法用這範圍去達成目標，以後只插入刪除都沒關係
只要這個黃色區塊不刪除，自然公式都不用修改

如果有可能第一個欄位有可能被刪除掉，那我也會在最開頭插入一排黃色區塊
那這兩個區塊之間的資料可以隨意任意增加刪除

上述案例是用VSTACK達成相對目標，但是這樣會產生一堆0的內容
用FILTER去把空白內容刪除

羅仔樓主

哇，您的解答簡單明瞭，不用複雜的函數公式，原來用VSTACK就可以，也謝謝您告訴我一些小技巧，超感謝

2023-02-19 22:10

Yaude Huang

Yaude Huang
個人積分：5289分
文章編號：86915938

5289分

7樓

2023-02-21 17:28

結合前面我說的兩種方法，合併其優點

=VSTACK(FILTER(B:C, ISNUMBER(C:C)),FILTER(E:F, ISNUMBER(F:F)))