יום ראשון, 10 במרץ 2013

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

ג'והן ווקנבאך הוא אחד המומחים הגדולים בעולם ל"אקסל" (לדעת רבים, הגדול מכולם).
הוא ידוע כ"מר גליון אלקטרוני" (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


אין תגובות:

הוסף רשומת תגובה