חפש ערך בגליונות עבודה מרובים

כדי לחפש מספר דפי עבודה בחוברת עבודה אחר ערך ולהחזיר ספירה, תוכל להשתמש בנוסחה המבוססת על COUNTIF ו עקיף פונקציות. עם התקנה מקדימה, תוכל להשתמש בגישה זו כדי לחפש חוברת עבודה שלמה אחר ערך מסוים. בדוגמה המוצגת, הנוסחה ב- C5 היא:

= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)

הקשר - נתונים לדוגמא

חוברת העבודה כוללת 4 דפי עבודה בסך הכל. גיליון 1 , גיליון 2 , ו גיליון 3 כל אחד מכיל 1000 שמות פרטיים אקראיים שנראים כך:

הטווח B7: B9 מכיל את שמות הגיליונות שברצוננו לכלול בחיפוש. אלה הם רק מחרוזות טקסט, ועלינו לבצע כמה עבודות בכדי שהן יוכרו כהפניות לגיליונות תקפים.

להצטיין במספר אקראי בין 0 ל -1

ביטוי זה פועל מבפנים החוצה ומשמש לבניית הפניה לדף מלא:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),$C)

המרכאות היחידות מתווספות כדי לאפשר שמות גיליונות עם רווחים, וסימן הקריאה הוא תחביר סטנדרטי לטווחים הכוללים שם גיליון. הטקסט '1: 1048576' הוא טווח הכולל כל שורה בגליון העבודה.

לאחר הערכת B7 והערכים משולבים, הביטוי למעלה מחזיר:

ספירת טבלת ציר של ערכים ייחודיים

 
'''&B7&''!'&'1:1048576'

אשר נכנס לתוך פונקציה INDIRECT כטענת 'ref_text'. INDIRECT מעריך את הטקסט הזה ומחזיר הפניה סטנדרטית לכל תא גיליון 1 . זה נכנס לפונקציה COUNTIF כטווח. הקריטריונים ניתנים כ התייחסות מוחלטת ל- C4 (נעול כך שניתן להעתיק את הנוסחה מטה C).

COUNTIF מחזיר לאחר מכן ספירה של כל התאים עם ערך שווה ל- 'mary', 25 במקרה זה.

כיצד לעשות תרשים עמודות ב- Excel

הערה: COUNTIF אינו תלוי רישיות.

מכיל מול שווה

אם אתה רוצה לספור את כל התאים את זה לְהַכִיל הערך ב- C4, במקום כל התאים שווה ל- C4, אתה יכול להוסיף תווים כלליים לקריטריונים כמו זה:

 
''Sheet1'!1:1048576'

כעת COUNTIF יספור תאים עם מחרוזת המשנה 'ג'ון' בכל מקום בתא.

ביצועים

באופן כללי, אין זה מנהג טוב לציין טווח הכולל את כל תאי גליון העבודה. פעולה זו עלולה לגרום לבעיות ביצועים, שכן הטווח כולל מיליוני מיליוני תאים. בדוגמה זו הבעיה מורכבת, שכן הנוסחה משתמשת בפונקציה INDIRECT, שהיא פונקציה נדיפה . פונקציות נדיפות מחושבות מחדש בכל שינוי בגליון עבודה, כך שההשפעה על הביצועים יכולה להיות עצומה.

במידת האפשר, הגבל טווחים לגודל הגיוני. לדוגמה, אם אתה יודע שהנתונים לא יופיעו אחרי שורה 1000, תוכל לחפש רק ב -1000 השורות הראשונות כך:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),'*'&C4&'*')