بررسی هر یک از دو مقدار متن در اکسل

Question

کریس می‌خواهد سلول‌هایی را که حاوی مقدار متن A یا مقدار متن B هستند، در هر نقطه از متن سلول‌ها شمارش کند. اگر سلول دارای هر دو A و B باشد، او می خواهد آن را بشمارد، اما فقط یک بار. به عنوان مثال، کریس دارای سه سلول حاوی "دانه سیب"، "درخت سیب" و "دانه هلو" است و او می خواهد تعداد سلول های حاوی "سیب" یا "دانه" را بداند. (پاسخ مناسبی که باید برگردانده شود 3 است.)

راه های زیادی وجود دارد که می توان به این موضوع نزدیک شد. در بررسی راه‌حل‌ها، تنها راه‌حل‌هایی را بررسی کردم که از پاسخ‌های میانی اجتناب می‌کنند و ستون‌های اضافی را اشغال می‌کنند. اولین راه حل شامل استفاده از تابع COUNTIF به این صورت است:

=COUNTIF(A1:A9,"*apple*")+COUNTIF(A1:A9,"*seed*")
-COUNTIF(A1:A9,"*seed*apple*")-COUNTIF(A1:A9,"*apple*seed*")

فرمول تمام سلول های حاوی "سیب" یا "دانه" را شمارش می کند و سپس تمام سلول های حاوی "seed" و سپس "apple" (هر دو کلمه در سلول هستند) یا "apple" و سپس "seed" را کم می کند. همان کلمات به ترتیب معکوس).

راه حل دیگر، این راه حل کمی کوتاه تر، به توابع COUNTA و FIND متکی است، همانطور که در اینجا نشان داده شده است:

=COUNTA(A1:A9)-SUMPRODUCT(--(ISERROR(FIND("apple",A1:A9)))
*--ISERROR(FIND("seed",A1:A9)))

فرمول سلول های حاوی مقادیر را شمارش می کند و سپس تمام سلول هایی را که حاوی "سیب" یا "دانه" نیستند، کم می کند.

همچنین در صورت تمایل می توانید از یکی از توابع پایگاه داده Excels استفاده کنید. به شرطی که ستونی برای عبارات اصلی خود داشته باشید، انجام این کار چندان دشوار نیست و کوتاه ترین فرمول را به همراه دارد. تنها کاری که باید انجام دهید این است که جدول معیارهای مربوطه را تنظیم کنید. به عنوان مثال، فرض کنید داده‌های شما در A1:A9 هستند و اولین سلول در ستون حاوی سرصفحه‌ای مانند «عبارات من» است. در ستون دیگری باید همان هدر را قرار دهید و سپس در دو خانه مستقیماً زیر آن، این دو فرمول را قرار دهید:

*apple*
*seed*

معیارها مشخص می‌کنند که می‌خواهید سلول‌هایی را که حاوی «سیب» یا «seed» در داخل سلول هستند مطابقت دهید. با این تنظیمات (به فرض اینکه جدول معیارها را در D1:D3 قرار داده اید)، می توانید از فرمول زیر استفاده کنید:

=DCOUNTA(A1:A9,1,D1:D3)

البته، می‌توانید از فرمول آرایه (که با فشار دادن Ctrl+Shift+Enter وارد می‌شود ) برای دریافت پاسخ خود استفاده کنید. فرمول زیر یکی از این فرمول ها است که باز هم به عباراتی که در A1:A9 بررسی می شوند متکی است:

=SUM(--((ISNUMBER(FIND("apple",A1:A9))+ISNUMBER(FIND("seed",A1:A9)))>0))

اگر بیشتر به سمت کار با ماکروها تمایل دارید، می توانید یک تابع تعریف شده توسط کاربر ایجاد کنید که تعداد را برای شما برمی گرداند. در زیر نمونه ای از مواردی است که کار می کند:

Function FindTwoStrings(rng As Range, s1 As String, _
  s2 As String) As Integer
    Application.Volatile
    If TypeName(rng) <> "Range" Then Exit Function
    Dim cell As Range
    For Each cell In rng.Cells
        If (InStr(1, UCase(cell.Value), UCase(s1), _
          vbTextCompare) > 0) Or (InStr(1, UCase(cell.Value), _
          UCase(s2), vbTextCompare) > 0) Then _
          FindTwoStrings = FindTwoStrings + 1
    Next cell
End Function

برای استفاده از تابع، می توانید از این فرمول در یک سلول استفاده کنید:

=FindTwoStrings(A1:A9,"apple","seed")