Eszter یک لیست طولانی از سلول های ستون A دارد که حاوی یک سری کدهای جهش مانند "AKT 142" یا "BRAF 1975" است. در ستون B مقادیر مرتبط با این کدهای جهش وجود دارد. او به فرمولی نیاز دارد که مقادیر ستون B را که کد جهش مربوطه در ستون A با همان دنباله شروع می شود، مانند همه مواردی که با AKT یا BRAF شروع می شوند، جمع کند. Eszter مشکوک است که این کار را می توان با تابع SUMIF انجام داد، اما او نمی داند که چگونه می تواند فقط به بخش اول کد جهش توجه کند.
راه های زیادی وجود دارد که می توانید به این مشکل نزدیک شوید، اما در این نکته، من فقط بر روی سه راه حل بالقوه تمرکز می کنم.
استفاده از ستون کمکی
اگر چیدمان کاربرگ شما اجازه می دهد، می توانید یک ستون کمکی اضافه کنید که فقط قسمت اول کدهای جهش را در خود دارد. از آنجایی که کدهای جهش شما در ستون A هستند، می توانید فرمول زیر را در سلول اول ستون کمکی خود وارد کنید:
=LEFT(A1, SEARCH(" ",A1,1)-1)
آن را برای هر تعداد سلولی که لازم است کپی کنید، و در نهایت به ستون کمکی می رسید که حاوی همه چیز در کدهای جهش قبل از فاصله است. سپس می توانید از فرمول SUMIF مورد نظر خود برای جمع آوری بر اساس محتویات ستون کمکی استفاده کنید.
با استفاده از SUMPRODUCT
یک رویکرد نسبتا منحصر به فرد برای حل مشکل استفاده از تابع SUMPRODUCT است. اجازه دهید بگوییم که در سلول E1، کد پیشگفتار مورد نظر خود را قرار داده اید. (بنابراین، برای مثال، ممکن است "AKT" را در سلول E1 قرار دهید.) سپس می توانید با استفاده از فرمول زیر مبلغ مورد نظر را محاسبه کنید:
=SUMPRODUCT(--(LEFT(A:A,LEN($E$1))=$E$1) * B:B)
این کار به این دلیل کار می کند که SUMPRODUCT بررسی می کند که آیا سمت چپ ترین بخش سلول در ستون A با آنچه در سلول E1 قرار داده اید مطابقت دارد یا خیر. اگر اینطور شد، مقایسه 1 را برمی گرداند. اگر نشد، 0 برمی گرداند. سپس در سلول مربوطه در ستون B ضرب شده و جمع می شود.
استفاده مستقیم از SUMIF
شاید تمیزترین روش استفاده مستقیم از SUMIF باشد. با استفاده از رویکرد ستون کمکی می دانید که می توانید از SUMIF برای مشاهده محتویات یک سلول و سپس جمع بندی انتخابی ستون دیگری استفاده کنید. شما این کار را به صورت کلی انجام می دهید:
=SUMIF(Check_Range, Criterion, Sum_Range)
بنابراین، اگر می خواهید مقادیر ستون B را بر اساس آنچه در ستون A است جمع کنید، می توانید موارد زیر را انجام دهید:
=SUMIF(A:A, "AKT", B:B)
البته این فقط با سلول هایی در ستون A که فقط حاوی AKT هستند مطابقت دارد. اگرچه این وضعیت Eszters نیست - کدهای جهش در ستون A بیش از AKT هستند. اینجاست که استفاده از حروف عام در مشخصات معیار مطرح می شود. تنها کاری که Eszter باید انجام دهد این است که یک ستاره به این صورت اضافه کند:
=SUMIF(A:A, "AKT*", B:B)
اکنون SUMIF تنها بر اساس سلول های ستون A که با حروف AKT شروع می شوند، مجموع مناسب را برمی گرداند. مهم نیست که چه چیزی در هر سلول به دنبال کاراکترهای AKT می آید، زیرا ستاره به اکسل می گوید که باید "هر چیزی را که از آن سه کاراکتر پیروی می کند بپذیرد."
شما حتی می توانید این رویکرد را ماهیت کلی تر کنید. فرض می کنیم که کد پیشگفتار مورد نظر (هیچ کدی که می خواهید جمع آوری کنید) را در سلول E1 قرار داده اید. سپس می توانید موارد زیر را در سلول E2 قرار دهید:
=SUMIF(A:A, E1 & "*", B:B)
حال، اگر E1 حاوی "AKT" باشد، در نهایت با مجموع مقادیر برای آن کد پیشگفتار مواجه می شوید. اگر E1 را به "BRAF" تغییر دهید، بدون نیاز به تغییر فرمول در E2، مبلغی برای آن کد پیشگفتار دریافت می کنید.
