הבלוג של מני

פתרון אלגנטי יותר....

ג'והן ווקנבאך הוא אחד המומחים הגדולים בעולם ל"אקסל" (לדעת רבים, הגדול מכולם).
הוא ידוע כ"מר גליון אלקטרוני" (Mr. Spreadsheet) וכתב יותר מ-50 (!) ספרי "אקסל". לדוגמא (מהאחרונים שבהם, עבור "אקסל" 2010):

Excel Dashboards and Reports

Excel 2010 Power Programming With VBA

Excel 2010 Formulas

Excel VBA Programming For Dummies

Excel 2010 Bible

חפוש שמו (John Walkenbach)  ב"גוגל" העלה למעלה מ- 700,000 תוצאות (!).

באחד מספריו האחרונים:

John Walkenbach’s Favorite Excel® 2010 Tips & Tricks

מצאתי את הטיפ הבא (מס.72) אשר מסביר כיצד להפוך מידע טבלאי (המאוחסן בעמודה אחת) לטבלה (בשורה אחת).

בעמודה A יש לנו מידע המכיל שם ופרטי כתובת (רחוב, עיר, מדינה ומיקוד). בכל "רשומה" 5 פריטי מידע ב-5 שורות: המידע על John Smith  מופיע בתאים A1:A5, המידע על Sally Jones מופיע בתאים A6:A10, המידע על Karen Richardson מופיע בתאים A11:A15 וכו'.

אנו רוצים שהנתונים המופיעים במאונך ב-5 תאים (A1:A5) - יופיעו במאוזן, בשורה אחת, ב-5 תאים: C1:G1.

הפתרון שמציע ווקנבאך בספר הוא להשתמש ב-5 נוסחאות, אחת עבור כל אחד מחמשת פרטי המידע. כדי להציג את המידע על John Smith  (תאים A1:A5) - יש להשתמש ב-5 הנוסחאות הבאות בתאים C1:G1:

C1: =INDIRECT("A" & ROW()*5-4)       ->  "Joe Smith"

D1: =INDIRECT("A" & ROW()*5-3)       ->  "505 Main St."

E1: =INDIRECT("A" & ROW()*5-2)       ->  "St. Louis"

F1: =INDIRECT("A" & ROW()*5-1)        ->  "MO"

G1: =INDIRECT("A" & ROW()*5-0)       ->  "64333"

                                                                      

                       

הסבר:

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

למשל, אם תא A1 מכיל את הערך: "C3" - אזי,

=INDIRECT(A1)

תחזיר את הערך שנמצא בתא C3 (ולא את הערך C3!)

הפונקציה ROW() - מחזירה את מספר השורה הנוכחית [הסוגריים הריקים מציינים שהפונקציה לא מקבלת שום ארגומנט]. למשל, אם הנוסחא הוקשה בתא: C1 - הפונקציה מחזירה: 1.

כעת, אפשר להבין מהי תוצאת הנוסחא שהקשנו ב: C1

=INDIRECT("A"&[1*5-4]) => "A"&1 => "A1"

אנו משרשרים (באמצעות התו: &) את המחרוזת "A" ביחד עם המספר (1*5-4) = 1 ומקבלים את כתובת התא: A1

אנו יודעים שבתא A1 מופיע: "John Smith" ולכן, בתא C1 - שבו מופיעה הנוסחא נקבל את תוכן התא A1, כלומר: "John Smith".

באופן דומה, בתא D1מופיעה הנוסחא: 

=INDIRECT("A"&[1*5-3]) => "A"&2 => "A2"

ובתא A2מופיע המידע: "505 Main St.". לכן, בתא D1נקבל מידע זה.

עד כאן, הכל בסדר.

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

=INDIRECT("A" & COLUMN()-2+ (ROW()-1)*5)

הסבר:

התא שעליו מצביעה הפונקציה INDIRECT מזוהה בעצם על-ידי שני ארגומנטים: העמודה והשורה.

הפונקציה: COLUMN() - מחזירה את מס. העמודה [גם כאן, הסוגריים הריקים מציינים שהפונקציה לא מקבלת שום ארגומנט].

למשל, אם אנו נמצאים בעמודה C, הפונקציה מחזירה: 3. אם אנו נמצאים בעמודה D, הפונקציה מחזירה: 4 וכו'.

קטע הנוסחא: COLUMN()-2 מחזיר את מס. העמודה של תא המקור. אם אנו נמצאים בעמודה C (העמודה השלישית), נקבל: 1. ואם אנו נמצאים בעמודה D (העמודה הרביעית) - נקבל: 2.

השילוב: COLUMN()-2+ (ROW()-1)*5 - נותן בעצם את מס. השורה של תא המקור. למשל,

אם אנו רוצים לקבל תוכן התא: A1בתא C1- אזי מספר השורה יהיה: 1

COLUMN()-2+ (ROW()-1)*5 = (3-2) + (1-1)*5 = 1 + 0 = 1

אם אנו רוצים לקבל תוכן התא: A2בתא D1- אזי מספר השורה יהיה: 2

COLUMN()-2+ (ROW()-1)*5 = (4-2) + (1-1)*5 = 2 + 0 = 2

והשלוב של המחרוזת "A" עם תוצאה זו נותן את כתובת התא הרצוי (A1, A2…)

על פתרון זה, כתב לי Mr. Spreadsheet בכבודו ובעצמו:

AAA