עזרה בכתיבת נוסחה בת משיכה EXCEL

[מתחילן צמרת]

עזרה בכתיבת נוסחה בת משיכה EXCEL

EXCEL 2013
WIN 7
מצ"ב קובץ המדגים את בקשתי.
מבקש עזרה בהקלה על כתיבת נוסחה. במקום לתקתק כל פעם נוסחה דומה, להסתפק ב"למשוך" מטה.
הסבר:
אני סוכם את כל ה "X" בגליון DATA, לפי טורים : E+K,F+L,G+M , טוריים, טוריים, וכותב אותם בגליון COUNTS
כתבתי את הנוסחאות.
לשלוש עמודות זה עוד סביר
אבל אם יהיו לי מעל 10 זוגות עמודות מסוג E F G זה יהיה פרך לא קטן.
אשמח אם אפשר לכתוב את נוסחת ה COUNTIF בגליון COUNTS , בצורה כזו שאכתוב נוסחה אחת וב"משיכה" למטה אקבל את הסכומים המדויקים.
נסיתי להשתמש ב OFFSET / INDIRECT ...ולא הצלחתי.
מבנה ה DATA הכללי כזה:
מספר טורי ה - /AAA1/BBB1/CCC1 / NUM/AAA/BBB/CCC יהיה קבוע - 7.
ומספר טורי ה "אב", "גד", "הו" יהיה משתנה מ6 עד 20, בזוגות .
לכן מבקש נוסחה כללית.

תודה מראש
חג שמח לכולנו

 

[מיכאל אבידן]

משהו כבתמונה שלהלן

. . .

תסתכל ותבחן, היטב, לפני שאתה מגיב...

 

[מתחילן צמרת]

תודה רבה - בדיוק

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

תודה רבה.

 

[מיכאל אבידן]

לפני שאני מציג את הנוסחאות . . .

1. אתה הצגת 3 זוגות ואני הוספתי עוד 4.
האם זה לא מה שביקשת

אתה יכול ורשאי להוסיף (מצד שמאל) כמה זוגות שתחפוץ .

2. אשאיר לך את ה"תענוג" להפנות את הנוסחאות לגיליון DATA.

3. האם אתה בטוח

שאינך רוצה לנסות עוד כמה שעות בעצמך

מדובר בנוסחאות פשוטות - לא משהו שלא נתקלת בו בפתרונות בפורום (אם דאגת להתעדכן וללמוד מהם).

 

[מתחילן צמרת]

תשובותי

1 יכול להיות שיהיו 7 , 8, 12.... וכו' .
אני מאמין שאדע להרחיב את הנוסחה למקרה של יותר מ7.
2 מקבל את האתגר, אעביר ל DATA בעצמי
3 אנסה עוד נסיונות. מרשה לעצמי לחזור אליך מחר בערב אם לא אמצא פתרון.
בכל אופן תודה רבה.

 

[מיכאל אבידן]

אין שום צורך "לדעת" להרחיבה . . .

שכן, ביקשת ותקבל, נוסחה דינאמית ש"יודעת להתאים את עצמה" לכמות זוגות העמודות.
&nbsp
*** במקרה ולא תצליח (וצריכה להיות, לך, סיבה ממש טובה) הצמד קובץ ובו 9 זוגות כדי שאוכל לבדוק - סופית - את הנוסחה.
&nbsp

 

[מתחילן צמרת]

אם לא אצליח- אצרף בשמחה.

אני מנחש שאם היא "יודעת להתאים את עצמה" - זו תהיה נוסחת מערך.
נכון?

 

[מיכאל אבידן]

אכן, 'נוסחת מערך' . . .

העושה שימוש ב- OFFSET ו- MATCH ובעוד משהו קטן...
&nbsp

 

[מיכאל אבידן]

ואין שום חובה שנוסחה, המתאימה את עצמה, תהיה 'נוסחת מערך'

דרך אגב - אני מקווה שבקובץ שתצמיד יהיו גם ניסיונותיך כדי שנבחן עד כמה (אם בכלל) היית רחוק מפיתרון...
&nbsp

 

[מתחילן צמרת]

נסיתי ולא צלחתי

מצרף את קובץ הנסיונות + גליון עם התוספת שבקשת.
תודה מראש.

 

[מיכאל אבידן]

הפיתרון שכבר הוצע לך לא נחשב כ"דינאמי" . . .

אבל "מספק את הסחורה".
&nbsp
טבלת התוצאות שלי מוצגת, עדיין, בגיליון DATA החל משורה 1

והנוסחה מתייחסת רק לטווח דינאמי שבשימוש (לא ל-1000 שורות ו-700 עמודות) ועניין ה'דינאמיות' מאריך, את הנוסחה - לה, כפי שנוכחנו לראות, אינך זקוק יותר...
&nbsp

 

[מתחילן צמרת]

תודה

אשמח לראות את הצעתך לפתרון.

 

[מיכאל אבידן]

בתמונה שלהלן . . .

תשומת לבך שבפונקציה OFFSET (בה השתמשת בגיליון COUNTS) הארגומנטים 'גובה' ו'רוחב', מעצם הגדרתם, הינם אופציונאליים ולכן - בנוסחת אחזור ה- Subject - הם מיותרים מאחר ומדובר בתא בודד ולא בטווח תאים.

 

[מתחילן צמרת]

תודה רבה

 

[iyyi]

נסה בתא C2 בגיליון COUNTS את הנוסחה הבאה...

=SUMPRODUCT((DATA!B$2:ZZ$1000="X")*(DATA!B$1:ZZ$1=A2))
וגרור כלפי מטה

 

[מתחילן צמרת]

תודה רבה- ייבדק

 

[מתחילן צמרת]

תודה רבה

עובד יפה