帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2021-10-8 09:58 | 只看該作者

[發問] 空白<>0值的函數公式

空白不視為0值的函數公式.rar (5.74 KB)
目前：
C19
=IF(C18="","",INDEX($1:$1,LARGE(IF($C14:$AY14=C18,COLUMN($C:$AY)),COUNTIF($C18:C18,C18))))
即當第14列的某欄值=C18=0時，則C19顯示第14列的該欄值之第1列的最大同欄值。

問題：
因為目前的C19公式會將$C14:$AY14=""的儲存格~都判斷為0值~所以C19=49；
但答案是錯誤的。

需求：
將C19公式的判斷條件修正為~
當$C14:$AY14的某儲存格=""時，則判斷為"空白格"，不視為0值~
當$C14:$AY14的某儲存格=0時，才判斷為0值。
即C19=05

以上 C19的函數公式~懇請各位大大賜正。
謝謝!

樓主熱帖

hcm19522

大學生 Rank: 4

帖子: 1412
主題: 4
精華: 0
積分: 1422
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2025-7-1

熱烈參與勳章

2^#

發表於 2021-10-8 10:42 | 只看該作者

本帖最後由 hcm19522 於 2021-10-8 10:51 編輯

{=IF(C18="","",INDEX(1:1,MAX(IF(($C14:$AY14=C18)*($C14:$AY14<>""),COLUMN($C:$AY)))))

=IF(C18="","",LOOKUP(1,($C14:$AY14=C18)/($C14:$AY14<>""),$C1:$AY1))

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

samwang

大學生 Rank: 4

帖子: 976
主題: 7
精華: 0
積分: 1018
點名: 0
作業系統: Win10
軟體版本: Office 2016
閱讀權限: 50
性別: 男
註冊時間: 2013-4-19
最後登錄: 2025-1-10

3^#

發表於 2021-10-8 10:46 | 只看該作者

回復 1# ziv976688

{=IF(C18="","",INDEX($1:$1,LARGE(IF($C14:$AY14<>"", IF($C14:$AY14=C18,COLUMN($C:$AY)),),1)))}

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

4^#

發表於 2021-10-8 11:48 | 只看該作者

本帖最後由 ziv976688 於 2021-10-8 11:50 編輯

回復 2# hcm19522
回復 3# samwang
二位大大 :
測試成功

承教了~感恩

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2021-10-8 12:43 | 只看該作者

C19 =INDEX(1:1,MATCH(C18,14:14,))

取巧
C19 =MATCH(C18,14:14,)-2

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

6^#

發表於 2021-10-8 17:03 | 只看該作者

本帖最後由 ziv976688 於 2021-10-8 17:25 編輯

回復 5# ML089
不僅是非陣列的一般公式解(利於套用在程式碼)，且精明(利用固定A&B的-2欄差)而簡短的解法~
承教了~感恩

另外再延伸請教：
C19
=IF(C18="","",INDEX($1：$1,LARGE(IF(($C14：$AY14=C18)*($C14：$AY14<>""),COLUMN($C：$AY)),COUNTIF($C18：C18,C18))))
陣列公式 右拉到AY19填滿(因為公式有套用在程式碼內，事前不知第18列數字的顯示迄止格，所以預拉到AY19)，
即C19=
當$C14：$AY14的某儲存格<>""且$C14：$AY14=C18時，
則C19顯示第14列該欄的第1列同欄值；
另當第18列有2格以上的相同值時，則該第19列的值以由大而小排序。
詳如附件 :

TEST_1008.rar (3.54 KB)

請問：您的C19公式會如何作解？
謝謝您

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2021-10-8 18:18 | 只看該作者

C18:AB19 區域陣列公式，排列 14列數值由小至大及1列數值由大至小
=IFERROR(INDEX(A:AY,{14;1},1/MOD(SMALL(IF(C14:AY14<>"",C14:AY14*10^4+1/COLUMN(C:AY)),COLUMN(A:Z)),10^4)),"")

輸入公式 (以前陣列公式要先刪除)
1 先複製公式
2 選擇 C18:AB19 儲存格
3 編輯列貼上公式
4 鼠標位於編輯列，用三鍵(CTRL+SHIFT+ENTER)輸入公式

COLUMN(A:Z) 取出幾個，最大應該是資料有幾欄就設幾欄，但貼上(顯示)欄位可以縮短至空格出現1格位置，表示資料完全出現。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

高中生 Rank: 3 Rank: 3

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

8^#

發表於 2021-10-8 19:16 | 只看該作者

回復 7# ML089
版大 :
區域陣列公式的效率很高。
謝謝您的耐心指導~感恩

TOP