בהרבה מקרים, זה השימוש בתוכנה מוגבלת שיוצר את האתגרים הגדולים - שלא לומר, הצרות הצרורות - שמחייבים אתכם לשנס מותניים ולהפשיל שרוולים. הדוגמאות הקלאסיות קשורות למקרים של "עיבוד יתר", שבו המידע הגולמי עובר תהליך שהופך אותו לגיבוב בלתי שמיש.
קל לבצע מניפולציה על נתונים שמגיעים בצורה מסודרת, מופרדת ומובחנת היטב. אבל ישנם מצבים שמרגע שעיבדת את המידע הגולמי, החזרה אחורה הופכת לכאב ראש גדול.
| תמונה 1 (צילום: נט מגזין) |
לחלק מהקוראים, הגיליון שבתמונה 1 בוודאי מזכיר כמה דברים. בגיליון הזה אפשר לראות טור אחד שכולל רשומות פרטים אישיים, מהסוג שמתקבל לעיתים כאשר מייצאים רשומות כאלו לקובץ Excel מתוכנה אחרת. במקור, הרשומות הללו אוחסנו בבסיס נתונים טבלאי, שבו נשמרה הפרדה הגיונית בין השדות שהרכיבו כל רשומה.
בייצוא לגיליון Excel אוחדו השדות, ובמצב החדש יש בעיה להשתמש בנתונים - קשה למיין אותם, אי אפשר להשתמש בהם להדפסת מדבקות וכו'.
המטרה היא לפרק מחדש את הרשומות, כך שכל שדה או קבוצת שדות יופיעו בטור נפרד. בדוגמה: השם הפרטי ושם המשפחה בתא אחד, הכתובת בתא שני, המיקוד בשלישי ומספר הטלפון ברביעי - כפי שניתן לראות בתמונה 2.
| תמונה 2 (צילום: נט מגזין) |
בדוגמה הפרדנו את הרשומות לארבעה טורים: שם, כתובת, מיקוד וטלפון. בדיוק כמו בגיליון הקודם, השתמשנו בפונקציות Excel סטנדרטיות לגמרי (שנתמכות בכל הגירסאות של התוכנה). בהמשך נציג אותן, ונציג את השיטה באמצעותה ביצענו את ההפרדה הזו.
הפונקציות בהן נשתמש
LEN - פונקציה פשוטה שמחזירה את מספר התווים במחרוזת נתונה. התחביר פשוט:( LEN(text כאשר text היא המחרוזת, או הפניה לתא שבו יש מחרוזת. לדוגמה:( LEN("abc" יחזיר לנו את הערך 3.
MID - פונקציה שמחזירה מספר מוגדר של תווים מתוך מחרוזת טקסט, החל מהמיקום שנגדיר לה. תחביר הפונקציה:
(MID(text,start_num,num_chars כאשר text היא המחרוזת, או הפניה לתא שכולל מחרוזת; start_num הוא מיקום התו במחרוזת ממנו נתחיל "ללכוד" תווים ו-num_chars הוא מספר התווים שהפונקציה תחזיר. לדוגמא, הפונקציה (MID("abcde",2,3 תחזיר את המחרוזת "bcd".
LEFT - וריאציה פשוטה על MID, שמחזירה גם היא מספר מוגדר של תווים מתוך מחרוזת, אבל לא מאמצע מחרוזת אלא תמיד מתחילה. באנגלית, היא תחזיר תווים תמיד מהקצה השמאלי של המחרוזת ואילך. בעברית, למרות שמה, היא תתחיל מימין (כפי שאפשר לראות בעזרה של הפקודה בתמונה 3). התחביר:
| תמונה 3 (צילום: נט מגזין) |
(LEFT(text,start_num כאשר text היא המחרוזת או הפניה לתא שכולל מחרוזת, ו-num_chars הוא מספר התווים שיוחזרו. דוגמא: הפונקציה (3, "שלום רב")LEFT תחזיר את המחרוזת "שלו".
RIGHT - כאן באמת אין צורך להכביר מילים. זה בדיוק כמו LEFT רק מהצד השני. גם התחביר זהה: RIGHT(text,start_num).
INDIRECT - פונקציה שמחזירה ערך של תא, שכתובתו נתונה כמחרוזת. התחביר:
(INDIRECT(ref_text,A1 כאשר ref_text היא מחרוזת שכוללת כתובת של תא ו-A1 הוא אופרטור לוגי מסוג TRUE או FALSE (אפשר לייצג בתור 1 או 0) שמתייחס לסוג ההפניה. לענייננו, אפשר לקבוע שרירותית שהארגומנט המתאים יהיה תמיד 1. דוגמה: (INDIRECT("A50",1 תחזיר את הערך שבתא A50.
SEARCH - הפונקציה הזו מחפשת תו מסוים בתוך מחרוזת, החל מנקודה מסוימת, ומחזירה את המיקום שלו. התחביר:
(SEARCH(find_text,whithin_text,start_num
דוגמה: הפונקציה
(SEARCH("a"," aba ima ",4
תחזיר את הערך 7, משום שהיא מחפשת את התו "a" החל מהתו הרביעי במחרוזת. התו הזה מופיע במקום הראשון, ואז שוב בשלישי, אבל אנחנו מתחילים לחפש מהתו הרביעי, ולכן נפגוש בו רק במופע השלישי שלו - בתו השביעי והאחרון במחרוזת.
איך נעשה זאת?
בשלב הראשון, נחלק את המחרוזת שכוללת את כל הפרטים האישיים לשתי מחרוזות:
א. שם + כתובת
ב. מיקוד + טלפון.
לגישה הזו יש שני יתרונות: ראשית, עדיף לנו להפריד בין החלק של המחרוזת שמבוסס על תווים עבריים בלבד לבין זה שכולל מספרים; בגלל הדרך בה
| תמונה 4 (צילום: נט מגזין) |
התמיכה העברית של יישומי Office עובדת, ל-Excel יש נטייה להפוך את הכיוון של קטעים שכוללים אנגלית וספרות. שנית, יהיה נוח לנו להפריד בין שדות המיקוד והטלפון שיש להם אורך קבוע, לשדות השם והכתובת שיש להם אורך משתנה.
בגיליון שבתמונה 4, אפשר לראות את הרשומות שלנו אחרי ההפרדה: השם והכתובת מופרדים לטור אחד (בתוך המסגרת הכחולה), המיקוד והטלפון מופרדים לטור נוסף (המסגרת האדומה). את שני הטורים האלה נפרק לארבעה: שם, כתובת, מיקוד וטלפון. את אופן הביצוע אפשר לראות בתמונה 5.
שימו לב שכל מספרי הטלפון שברשומות הם בפורמט של 10 ספרות (שלוש ספרות קידומת, מקף, שש ספרות מספר). כל המיקודים כוללים חמש ספרות. יש מקרים בהם זה קצת פחות פשוט, ויש דרכים לטפל גם בהם, אבל הפעם בחרנו במצב שקל יותר להתמודד איתו.
השדות הטקסטואליים פחות פשוטים. לשמות של אנשים, ערים ורחובות אין
| תמונה 5 (צילום: נט מגזין) |
אורך קבוע, ולכן כל אחת מהמחרוזות בהן נטפל תהיה באורך שונה. בדוגמא שלנו, למרבה המזל, אפשר להתבסס על נתון שאנחנו יודעים כבר (מספר התווים של הטלפון והמיקוד) ועל נתון שקל לבדוק (האורך של כל מחרוזת), כדי לחשב מה אורכם הכולל בתווים של שדות הטקסט הכלולים במחרוזות. זה מה שנעשה:
בשורה 28 בקטע הגיליון שמוצג תמונה 5 ניתן לראות את המחרוזת המקורית, שכוללת את הרשומה השלמה.
בשורה 29 אפשר לראות מחרוזת שכוללת את השם והכתובת בלבד. כדי לחלץ אותם, בדקנו את אורך המחרוזת השלמה בעזרת הפונקציה LEN, חיסרנו 16 (בשביל המיקוד והטלפון) וחילצנו את השאר בעזרת הפונקציה LEFT).
בשורה 32 אפשר לראות את חלק המחרוזת שכולל את הטלפון והמיקוד בלבד (מימין) ואת הנוסחה שחילצה את שניהם מהמחרוזת השלמה (משמאל). השתמשנו בפונקציה LEFT, וחילצנו 16 תווים ( מספר ומיקוד). אחר כך, בשורות 33 ו-34, נשתמש בפונקציה MID כדי לפרק את המחרוזת הקטנה לשניים: עד התו החמישי יש לנו את המיקוד, אחריו רווח, ואז מהתו השביעי עד ה-16 ישנו מספר הטלפון.
ההפרדה בין הכתובת לשם היא החלק המסובך יותר הפעם:
בשורה 30 חילצנו את השם, שהוא תמיד בפורמט של שם פרטי ושם משפחה. כדי לעשות את זה, חיפשנו בעזרת הפונקציה את המיקום של תו הרווח הראשון במחרוזת (שמפריד בין השם הפרטי לשם המשפחה). אחר כך, חיפשנו את תו הרווח הבא (שמפריד בין שם המשפחה לכתובת). ואז, חילצנו את כל התווים שמתחילת המחרוזת ועד למיקום של הרווח השני.
בשורה 31 פשוט לקחנו את מה שנשאר - מהרווח השני ועד למיקום בו נגמרת המחרוזת (אותו מצאנו באמצעות הפונקציה LEN. בואו נתעכב רגע על השיטה שבה שתי הפעולות האחרונות נעשו:
הביטוי SEARCH(" ",A29,1) בשורה 30, בעצם מוצא את המיקום של תו הרווח הראשון במחרוזת שכתובתה A29. את המיקום הזה אנחנו מחזירים לפונקציית SEARCH נוספת, שמתחילה לחפש את הרווח הבא (היא מוסיפה 1 למיקום שנמצא בפונקציה הפנימית, כדי להתחיל לחפש ממנו והלאה). בסופו של דבר, החזרנו את המיקום של תו הרווח השני לפונקציה החיצונית, שהיא פונקציית MID (שבעצם מתפקדת במקרה הזה בדיוק כמו פונקציית LEFT).
בשורה 31 עשינו דבר דומה מאד, אבל הפעם לקחנו את המיקום של תו הרווח השני, בדקנו את המיקום של התו האחרון במחרוזת, וחילצנו את כל מה שביניהם.
