אקסל - שליפת נתונים אוטו' לפי תנאי מסויים לטבלה אחרת

[UA]

היי לכולם!
הרבה זמן לא הייתי כאן

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

אני רוצה לשלוף רק את השורות שבעמודה A בטבלה הערך גדול מ- 100 ולהציג בטבלה בגיליון אחר כך:

העניין שהטבלה הראשית מתעדכנת כל הזמן ואני צריך שהגליון השני (עם הערכים מעל 100 בלבד) יתעדכן באופן אוטומטי.

מה הדרך הכי פשוטה לעשות את זה (ללא VBA)?

תודה מראש, UA

[yossiz]

@UA באקסל 2021 ו-365 יש פוקנציה בשם FILTER שעושה את זה,
לפי הנראה זה לא קיים בגירסאות קודמות
עיין כאן על דרכים חילופיות
https://exceljet.net/alternatives-to-dynamic-array-functions

[UA]

@yossiz תודה רבה, עובד מצויין! (אני עובד על 365)
שאני משנה נתונים (מוסיף/מוחק) זה מתעדכן אוטומטית.

[Y.Excel.Access]

לכל מי שראה את השאלה ואין לו עדיין את הגירסה עם הפונקציות הנ"ל, אזכיר את האופציה של POWER QUERY, שם אפשר ליצור כעין שאילתת SQL, והיא ג"כ מתעדכנת מנתוני מקור.

[UA]

@yossiz תודה.
עדיין יש לי בעיה,

הפונקציה Filter נהדרת,
אבל אני לא מצליח לעשות סינון בהתקיים 2 תנאים או יותר.

למשל יש לי טבלה כזו:

אני צריך לשלוף רק שורות שהערך בעמודה A גבוה מ- 100, והערך בעמודה B הינו "UP" (בדוגמה הנ"ל מדובר בשורה הראשונה בלבד).

רשמתי את הנוסחה הבאה:

(עמודה B בגיליון זוהי עמודה A בכותרת הטבלה שלי, עמודה C בגיליון זוהי עמודה B בכותרת הטבלה שלי)

אבל אני מקבל פלט כזה:

איך אפשר לעשות את זה?
תודה ענקית!!

[OdedDvir]

@UA

התשובה הקצרה היא:

שנה את התנאי לתנאי הבא:

BITAND(B2:B6>100,C2:C6="UP")

או כך:

(B2:B6>100) * (C2:C6="UP")

התשובה הארוכה היא:

הבעיה היא בתנאי. האופרטור AND (וגם OR) מוגדר להחזיר ערך יחיד בלבד.

אתה מספק לו כקלט שני מערכים, כי כל אחת מהנוסחאות B2:B6>100 ו- C2:C6="UP" תחזיר מערך בוליאני.
בדוגמא שהבאת, הנוסחא:

B2:B6>100

תחזיר את המערך הבא:

{ TRUE, FALSE, TRUE, FALSE, FALSE }

והנוסחא

C2:C6="UP"

תחזיר את המערך:

{ TRUE, TRUE, FALSE, FALSE, TRUE }

כשמעבירים קלט של מערך לאופרטור AND, התוצאה תהיה הפעלת AND על כל האיברים שבמערך, והחזרת תוצאה יחידה, דהיינו: TRUE אם כל הערכים בקלט הם TRUE, ו-FALSE אחרת.

אבל אתה בעצם מבקש את המערך שמתקבל מהפעלת AND על כל אינדקס במערך הראשון עם האינדקס התואם במערך השני, כלומר אתה בעצם רוצה לקבל את המערך הבא:

{ TRUE, FALSE, FALSE, FALSE, FALSE }

מייקרוסופט יישמו פונקציות חדשות BITAND BITOR שמסוגלות להחזיר מערך. לכן תוכל לחליף את התנאי לתנאי הבא:

BITAND(B2:B6>100,C2:C6="UP")

לנוסחאות האלו יש מגבלה מעצבנת שהן מוכנות לקבל רק שני פרמטרים בלבד, (בניגוד ל-AND ו-OR שמסכימות לקבל מספר כלשהו של פרמטרים), כך שאם אתה צריך תנאי יותר מורכב, עליך לשרשר אותן, למשל:

BITAND(BITAND(B2:B6>100,C2:C6="UP"),D2:D6="GFS")

לחילופין אפשר להשתמש בטריק אחר, ופשוט לשנות את התנאי AND למכפלה:

(B2:B6>100) * (C2:C6="UP") * (D2:D6="GFS")

[UA]

@OdedDvir וואהו מדהים!
תודה ענקית על ההסבר המפורט!

מה שאני מבין מדבריך שאפשר בעצם תמיד להחליף את הנוסחאות AND ו- OR בכפל (במקום AND) וחיבור (במקום OR)?

כלומר TRUE זה 1 ו- FALSE זה 0 לחלוטין מבחינה חישובית?

[OdedDvir]

@UA אמר באקסל - שליפת נתונים אוטו' לפי תנאי מסויים לטבלה אחרת:

מה שאני מבין מדבריך שאפשר בעצם תמיד להחליף את הנוסחאות AND ו- OR בכפל (במקום AND) וחיבור (במקום OR)?
כלומר TRUE זה 1 ו- FALSE זה 0 לחלוטין מבחינה חישובית?

גם כאן יש תשובה קצרה וארוכה...
בקצרה:
לא.
באריכות:
כמו שרמזת בהדגשתך, זה לא תמיד בר החלפה, וזה לא 0 ו-1 לחלוטין מבחינה חישובית.
זה תלוי באופן ההמרה של ערך בוליאני למספר. בעוד שבאקסל TRUE מומר ל-1, ב-VBA למשל, TRUE מומר לערך 1- (מינוס אחת).
גם בהחלפה של OR באופרטור החיבור, התוצאות לא זהות לחלוטין.
חיבור של שני המערכים למעלה יחזיר את המערך הבא:

{ 1, 0, 1, 1, 2 }

בו האיבר האחרון הוא 2, בעוד שהפונקציה BITOR תחזיר את המערך הזה:

{ 1, 0, 1, 1, 1 }

בו האיבר האחרון הוא 1.
אכן בהמרה חזרה לבוליאני שניהם יומרו למערך זהה, כי רק 0 יומר ל-FALSE, וכל ערך אחר שאינו 0 יומר ל-TRUE.

[סקרן 0]

@OdedDvir אמר באקסל - שליפת נתונים אוטו' לפי תנאי מסויים לטבלה אחרת:

ב-VBA למשל, TRUE מומר לערך 1-

למה?
או יותר נכון מה המשמעות של זה? (מה היה רע ב 1...)

[OdedDvir]

@סקרן-0 זו כבר קצת גלישה מהנושא, ובאורח לא מפתיע גם כאן יש תשובה ארוכה וקצרה:

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

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

ערך בוליאני Boolean מיוצג באופן תיאורטי על ידי סיבית בודדת, בעוד שמספר שלם Integer מיוצג על ידי רצף של סיביות (16, 32 או 64, גם זה נושא בפני עצמו....)
מוסכם ש-0 זה FALSE. הייצוג הבינארי של הערך המספרי 0 יהיה למשל 16 סיביות שכל אחת מהן היא 0:

0000000000000000

כעת איך נייצג את TRUE כערך מספרי? אם נבחר את הערך 1, ייצוגו בסיביות הוא כך:

0000000000000001

אבל רגע, יוצא שיש כאן חוסר עקביות, שעלול לגרום לקטסטרופה, כיון שלפי הגדרה, TRUE = NOT FALSE, הערך המספרי יהיה מיוצג כך:

NOT(0000000000000000) == 1111111111111111

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