如何計算一行中數字大于7的單元格的數量作為文本的一部分，例如“8天”

我有一個包含幾列的電子表格。我只在這里展示其中 2 個的資料，因為它們是我在這個問題中要處理的 2 個。

第一列是 IP 地址。第二列是上次回復的時間或上次回復的日期：

我需要做的是計算出一些計數。我想知道每個 IP 子網有多少個

1. 超過 1 周的回復
2. 超過 1 個月的回復。

在示例資料中，您可以看到 3 個 IP 子網：10.4、10.16 和 10.64。我期望得到如下結果：

我有一個“> 周”的公式，但我不喜歡它。我無法弄清楚如何根據該列中文本開頭的數字進行計數。我嘗試了這樣的公式：

=COUNTIFS(AllIPAddresses,"10.1.*",AllResponses, NUMBERVALUE(LEFT(AllResponses, FIND(" ",AllResponses)))&">7")

顯然這是行不通的。它給了我一個充滿 0 的列。

我為“>周”欄作業的內容：

=COUNTIFS(AllIPAddresses,CONCAT(A2,"*"),AllResponses,"<>7 days",AllResponses,"<>6 days",AllResponses,"<>5 days",AllResponses,"<>4 days",AllResponses,"<>3 days",AllResponses,"<>2 days",AllResponses,"<>Today",AllResponses,"<>Yesterday")

但就像我說的，我不喜歡它，因為它只是查看列而不計算 8 個選項。如果我能有辦法讓它查看該列并計算那些天數大于 7 的人，我會更愿意。簡單的東西會很棒，但比我擁有的更短和/或更簡單的東西我會拿。而且我無法在“> 月”結果中有效地重復使用它，因為那樣我就必須列出大約 30 個我不想計算的不同選項。
最好讓它尋找我想要的 1 選項。

我希望有類似的東西：

• 第一個 COUNTIFS 計算所有數字 > 7 的文本
• 第二個 COUNTIFS 計算今天之前 7 天以上的所有日期

=COUNTIFS(AllIPAddresses, CONCAT(A2,"*"),AllResponses, LEFT(AllResponse,2)&">7") COUNTIFS(AllIPAddresses, CONCAT(A2,"*"),AllResponses,"<"&today()-7)

然后我可以通過將 7 更改為 30 來在“> 月”中重復使用它。

雖然我知道這個公式不起作用。

任何有關此問題的幫助將不勝感激！

關于我的公式的一些注意事項
為了便于使用，我命名了范圍：
AllIPAddresses = A2:A700
AllResponses = B2:B700
（在我的公式中 > 周） A2指的是“10.1”。以便CONCAT將“10.1.*”的結果提供給COUNTIFS

uj5u.com熱心網友回復：

這可以通過以下方式完成：

=LET(data,A2:B15,
     _d1,INDEX(data,,1),
     _d2,INDEX(data,,2),
         lr,TODAY()-IF(ISNUMBER(_d2),_d2,TODAY()-(LEFT(_d2,LEN(_d2)-LEN(" days")))),
         lft,TEXTBEFORE(_d1,".",2),
         unq,UNIQUE(lft),
         sq,SEQUENCE(COUNTA(lft),,1,0),
         mm,--(TRANSPOSE(unq)=lft),
            wk,MMULT(--(TRANSPOSE(lft)=unq)*TRANSPOSE(lr>7),sq),
            mn,MMULT(--(TRANSPOSE(lft)=unq)*TRANSPOSE(lr>30),sq),
               stack,HSTACK(unq,wk,mn),
VSTACK({"IP Subnet","> Week","> Month"},stack))

lft用于TEXTBEFORE獲取 IP 地址的前 2 部分。 lr計算上次回應與今天相比的天數。 unq是lft（IP 子網）的唯一值。 wk用于計算大于 7 MMULT的唯一 IP 子網值的條件計數。與 相同，但大于 30。lrmnwklr

uj5u.com熱心網友回復：

使用新的 365 功能，生成唯一的 IP 值，并計算 >7 和 >30 天。如果日期檢索差異，否則提取天數：

=LET(IP,TEXTBEFORE(AllIPAddresses,".",2),U,UNIQUE(IP),S,SEQUENCE(COUNTA(AllIPAddresses),,1,0),D,IFERROR(TODAY()-AllResponses,TEXTBEFORE(AllResponses," ")*1),W,MMULT((TRANSPOSE(IP)=U)*(TRANSPOSE(D)>7),S),M,MMULT((TRANSPOSE(IP)=U)*(TRANSPOSE(D)>30),S),HSTACK(U,W,M))

與HSTACK合并列結果。

uj5u.com熱心網友回復：

也許你可以嘗試：

? 單元格中使用的公式D2

=LET(_ipaddress,TEXTBEFORE(Address,".",2),
_days,IFERROR(TODAY()-ISNUMBER(Last_Response)*Last_Response,
           SUBSTITUTE(Last_Response," days","") 0),
_uip,UNIQUE(_ipaddress),
_week,BYROW(_uip,LAMBDA(x,SUM(--(x=_ipaddress)*(_days>7)))),
_month,BYROW(_uip,LAMBDA(x,SUM(--(x=_ipaddress)*(_days>30)))),
VSTACK({"IP Subnet","> Week","> Month"},HSTACK(_uip,_week,_month)))

用于進行計算的每個命名變數的說明：

?地址--> 是定義的名稱范圍，指的是

=$A$2:$A$15

? Last_Response --> 是一個定義的名稱范圍，指的是

=$B$2:$B$15 

? _ipaddress --> 提取IP 子網使用TEXTBEFORE()

TEXTBEFORE(Address,".",2)

? _days檢查范圍是數字還是文本，因為在 Excel 中日期存盤為數字，我們ISNUMBER()用于檢查哪些回傳值TRUE和FALSE文本值，

也就是說，檢查并回傳天數的第一部分，IFERROR()

TODAY()-ISNUMBER(Last_Response)*Last_Response

雖然第二部分是文本值，但我們只是用空替換“天”并將其轉換為數字。

SUBSTITUTE(Last_Response," days","") 0

=IFERROR(TODAY()-ISNUMBER(Last_Response)*Last_Response,
           SUBSTITUTE(Last_Response," days","") 0)

? _uip --> 這為我們提供了唯一IP 子網

UNIQUE(_ipaddress)

? _week --> 這為我們提供了每個唯一值行的計數，并作為輸出陣列回傳，對于那些大于 7 天的天數。

BYROW(_uip,LAMBDA(x,SUM(--(x=_ipaddress)*(_days>7))))

? _month --> 雖然這為我們提供了每個唯一值行的計數，并作為輸出陣列回傳，對于那些大于 30 天的天數。

BYROW(_uip,LAMBDA(x,SUM(--(x=_ipaddress)*(_days>30))))

? 最后但同樣重要的是，我們將所有需要顯示為輸出的變數打包在一個HSTACK()

HSTACK(_uip,_week,_month)

為了讓正確的標題看起來不錯，我們將它VSTACK()與標題一起包裝在 [1x3] 陣列中

好吧，您也可以使用Power Query輕松執行此類任務：

要使用Power Query完成此任務，請按照以下步驟操作，

? 選擇資料表中的某個單元格，

?資料選項卡=>獲取&轉換=>從表/范圍，

? 當 PQ 編輯器打開時：主頁=>高級編輯器，

? 記下所有2 個表名，

? 粘貼下面的M 代碼代替您看到的內容。

? 并參考注釋

let
    //IPAddresstbl Uploaded in PQ Editor,
    Source = Excel.CurrentWorkbook(){[Name="IPAddresstbl"]}[Content],

    //Date Type Changed
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Last Response", type text}}),
    
    //Extracting the IP SUBNET 
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Address", each Text.BeforeDelimiter(_, ".", 1), type text}}),

    //Replacing " days" in last response column
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter"," days","",Replacer.ReplaceText,{"Last Response"}),
    
    //Removing " 12:00:00 AM" from Date Time since we changed the data type of lastresponse as text
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Replaced Value", {{"Last Response", each Text.BeforeDelimiter(_, " 12:00:00 AM"), type text}}),

    //Adding custom column return the numbers of days
    #"Added Custom" = Table.AddColumn(#"Extracted Text Before Delimiter1", "Custom", each if Value.Is(Value.FromText([Last Response]), type number) then [Last Response] else Date.From(DateTime.LocalNow()) - Value.FromText([Last Response])),
    
    //Changing the data type of the custom column to ensure they are numbers
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    
    //Removing unwanted columns
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Last Response"}),
    
    //Returning 1 for those days which are more than 7 else returning as 0    
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "> Week", each if [Custom] > 7 then 1 else 0),

    //Returning 1 for those days which are more than 30 else returning as 0
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "> Month", each if [Custom] > 30 then 1 else 0),
    
    //Grouping by each IP Address
    #"Grouped Rows" = Table.Group(#"Added Conditional Column1", {"Address"}, {{"> Week", each List.Sum([#"> Week"]), type nullable number}, {"> Month", each List.Sum([#"> Month"]), type nullable number}}),

    //Renamed the IP Address Column
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Address", "IP SUBNET"}})
in
    #"Renamed Columns"

? 將表名更改為SUBNETtbl，然后再將其匯入 Excel。

? 匯入時，您可以選擇帶有要放置表格的單元格參考的現有作業表，也可以簡單地單擊新作業表

標籤：擅长excel公式办公室365

上一篇：兩個值之間的條件格式

下一篇：如何優雅地獲得2個int32數字的中間而不會溢位？