10 הדרכים המובילות לניקוי הנתונים

מילים המאויתות באופן שגוי, רווחים נגררים שקשה להיפטר מהם, רישיות שלא רצית ותווים שאינם מיועדים להדפסה - כל אלה יוצרים רושם ראשוני לא טוב. ורשימה זו אפילו לא מהווה רשימה ממצה של הדרכים שבהן הטקסט שלך עלול "להתלכלך". אז הפשל שרוולים. הגיע הזמן לקצת "ניקיון פסח" של גליונות העבודה שלך ב- Microsoft Excel.

לא תמיד יש לך שליטה על התבנית ועל סוג הנתונים שאתה מייבא ממקור נתונים חיצוני, כגון מסד נתונים, קובץ טקסט או דף אינטרנט. לפני שניתן לנתח את הנתונים, לעתים קרובות צריך לנקות אותם. למרבה המזל, Excel כולל תכונות רבות כדי לעזור לך לקבל את הנתונים בדיוק בתבנית הרצויה. לעתים, המשימה פשוטה וישנה תכונה ספציפית העושה את העבודה עבורך. לדוגמה, באפשרותך להשתמש בקלות בבודק האיות כדי לנקות מילים המאויתות באופן שגוי בעמודות המכילות הערות או תיאורים. לחלופין, אם ברצונך להסיר שורות כפולות, באפשרותך לבצע זאת במהירות באמצעות תיבת הדו-שיח הסר כפילויות.

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

להלן השלבים הבסיסיים לניקוי נתונים:

  1. ייבא את הנתונים ממקור נתונים חיצוני.

  2. צור עותק גיבוי של הנתונים המקוריים בחוברת עבודה נפרדת.

  3. ודא שהנתונים כתובים בתבנית טבלאית של שורות ועמודות עם התנאים הבאים: נתונים דומים בכל עמודה, כל העמודות והשורות גלויות וללא שורות ריקות בטווח. לקבלת התוצאות הטובות ביותר, השתמש בטבלת Excel.

  4. בצע תחילה משימות שאינן דורשות טיפול בעמודות, כגון בדיקת איות או שימוש בתיבת הדו-שיח חיפוש והחלפה.

  5. לאחר מכן, בצע משימות הדורשות טיפול בעמודות. השלבים הכלליים לטיפול בעמודה הם:

    1. הוסף עמודה חדשה (B) לצד העמודה המקורית (A) שיש לנקותה.

    2. הוסף נוסחה שתמיר את הנתונים בחלק העליון של העמודה החדשה (B).

    3. מלא כלפי מטה את הנוסחה בעמודה החדשה (B). בטבלת Excel, עמודה מחושבת נוצרת באופן אוטומטי עם מילוי הערכים כלפי מטה.

    4. בחר את העמודה החדשה (B), העתק אותה ולאחר מכן הדבק כערכים בעמודה החדשה (B).

    5. הסר את העמודה המקורית (A). פעולה זו תמיר את העמודה החדשה מ- B ל- A.

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

מידע נוסף

תיאור

מבט כולל על חיבור (ייבוא) של נתונים

תיאור כל הדרכים לייבוא נתונים חיצוניים ל- Office Excel.

מילוי נתונים אוטומטי בתאי גליון עבודה

לימוד אופן השימוש בפקודה מילוי.

יצירה או מחיקה של טבלת Excel

הוספה או הסרה של שורות ועמודות בטבלת Excel

שימוש בעמודות מחושבות בטבלת Excel

לימוד האופן ליצירת טבלת Excel ולהוספה או מחיקה של עמודות או עמודות מחושבות.

יצירת מאקרו

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

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

מידע נוסף

תיאור

בדיקת איות ודקדוק

לימוד האופן לתיקון מילים המאויתות באופן שגוי בגליון עבודה.

שימוש במילונים מותאמים אישית כדי להוסיף מילים לבודק האיות

הסבר אופן השימוש במילונים מותאמים אישית.

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

מידע נוסף

תיאור

סינון ערכים ייחודיים או הסרת ערכים כפולים

הצגת שני הליכים קשורים: כיצד לסנן שורות ייחודיות וכיצד להסיר שורות כפולות.

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

מידע נוסף

תיאור

בדיקה אם תא מכיל טקסט (לא תלוי רישיות)

בדיקה אם תא מכיל טקסט (תלוי רישיות)

הצגת אופן השימוש בפקודה חיפוש ומספר פונקציות לחיפוש טקסט.

הסרת תווים מטקסט

הצגת אופן השימוש בפקודה החלפה ומספר פונקציות להסרת טקסט.

חיפוש או החלפה של טקסט ומספרים בגליון עבודה

חיפוש והחלפה

הצגת אופן השימוש בתיבות הדו-שיח חיפוש והחלפה.

FIND‏, FINDB

SEARCH‏, SEARCHB

REPLACE‏, REPLACEB

SUBSTITUTE

LEFT‏, LEFTB

RIGHT‏, RIGHTB

LEN‏, LENB

MID‏, MIDB

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

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

מידע נוסף

תיאור

שינוי מצב הרישיות של טקסט

הצגת אופן השימוש בשלוש הפונקציות של אותיות רישיות.

LOWER

המרת כל האותיות הרישיות במחרוזת טקסט לאותיות קטנות.

PROPER

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

UPPER

המרת טקסט לאותיות רישיות.

לעתים, ערכי טקסט מכילים תווים מובילים, נגררים או מספר תווי רווח מוטבעים (ערכים 32 ו- 160 בערכת התווים Unicode) או תווים שאינם מודפסים (ערכים 0 עד 31, 127, 129, 141, 143, 144 ו- 157 בערכת התווים Unicode). תווים אלה עלולים לעתים לגרום לתוצאות בלתי צפויות בעת מיון, סינון, או חיפוש. לדוגמה, במקור נתונים חיצוני, משתמשים עשויים לבצע שגיאות הקלדה על-ידי הוספת תווי רווח מיותרים שלא במתכוון, ונתוני טקסט מיובאים ממקורות חיצוניים עשויים להכיל תווים שאינם מודפסים המוטבעים בטקסט. מאחר שלא קל להבחין בתווים אלה, התוצאות הבלתי צפויות עשויות להיות קשות להבנה. כדי להסיר תווים בלתי רצויים אלה, באפשרותך להשתמש בשילוב של הפונקציות TRIM‏, CLEAN ו- SUBSTITUTE.

מידע נוסף

תיאור

הצגת אופן ההסרה של כל הרווחים והתווים שאינם מודפסים מערכת התווים של Unicode.

CODE

החזרת קוד נומרי עבור התו הראשון במחרוזת טקסט.

CLEAN

הסרת 32 התווים הראשונים שאינם מודפסים בקוד ה- ASCII של 7 סיביות (ערכים 0 עד 31) מטקסט.

TRIM

הסרת תו הרווח בקוד ה- ASCII של 7 סיביות (ערך 32) מטקסט.

SUBSTITUTE

באפשרותך להשתמש בפונקציה SUBSTITUTE כדי להחליף את תווי Unicode בעלי הערך הגבוה יותר (ערכים 127, 129, 141, 143, 144, 157 ו- 160) בתווי ה- ASCII של 7 סיביות שעבורם מיועדות הפונקציות TRIM ו- CLEAN.

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

מידע נוסף

תיאור

המרת מספרים המאוחסנים כטקסט למספרים

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

DOLLAR

המרת מספר לתבנית טקסט והחלת סמל מטבע.

TEXT

המרת ערך לטקסט בתבנית מספר ספציפית.

FIXED

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

VALUE

המרת מחרוזת טקסט המייצגת מספר למספר.

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

מידע נוסף

תיאור

שינוי מערכת התאריכים, תבנית התאריך או התרגום של שנה בת שתי ספרות

תיאור מערכת התאריכים ב- Office Excel.

המרת זמנים

הצגת אופן ההמרה בין יחידות זמן שונות.

המרה של תאריכים המאוחסנים כטקסט לתאריכים

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

DATE

החזרת המספר הסידורי הרציף המייצג תאריך מסוים. אם עיצוב התא היה 'כללי' לפני הזנת הפונקציה, התוצאה מעוצבת כתאריך.

DATEVALUE

המרת תאריך המיוצג על-ידי טקסט למספר סידורי.

TIME

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

TIMEVALUE

הפונקציה מחזירה את המספר העשרוני של השעה המיוצגת באמצעות מחרוזת טקסט. המספר העשרוני הוא ערך בטווח שבין 0 (אפס) ל- 0.99999999, המייצג את השעות שמ- 0:00:00 (12:00:00 בלילה) ועד 23:59:59 (11:59:59 בלילה).

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

מידע נוסף

תיאור

שילוב השם הפרטי ושם המשפחה

שילוב טקסט ומספרים

שילוב טקסט עם תאריך או שעה

שילוב שתי עמודות או יותר באמצעות פונקציה

הצגת דוגמאות אופייניות של שילוב ערכים מתוך שתי עמודות או יותר.

פיצול טקסט לעמודות שונות באמצעות אשף המרת טקסט לעמודות

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

פיצול טקסט לעמודות שונות באמצעות פונקציות

הצגת אופן השימוש בפונקציות LEFT‏, MID‏, RIGHT‏, SEARCH ו- LEN לפיצול עמודת שמות לשתי עמודות או יותר.

שילוב או פיצול התוכן של תאים

הצגת אופן השימוש בפונקציה CONCATENATE, באופרטור & (תו אמפרסנד) ובאשף המרת טקסט לעמודות.

מיזוג תאים או פיצול תאים ממוזגים

הצגת אופן השימוש בפקודות מיזוג תאים, מזג על-פני ומזג ומרכז.

CONCATENATE

צירוף שתי מחרוזות טקסט או יותר למחרוזת טקסט אחת.

רוב תכונות הניתוח והעיצוב ב- Office Excel מבוססות על ההנחה שהנתונים נמצאים בטבלה אחת, דו-ממדית ושטוחה. לעתים, ייתכן שתרצה להפוך את השורות לעמודות, ואת העמודות לשורות. במקרים אחרים, הנתונים אפילו לא מובנים בתבנית טבלאית, ועליך להמיר אותם מתבנית שאינה טבלאית לתבנית טבלאית.

מידע נוסף

תיאור

TRANSPOSE

החזרת טווח תאים אנכי כטווח אופקי או להיפך.

מדי פעם, מנהלי מסדי נתונים משתמשים ב- Office Excel כדי למצוא ולתקן שגיאות תואמות כאשר שתי טבלאות או יותר מצורפות. הדבר עשוי לכלול ביצוע התאמה עבור שתי טבלאות מגליונות עבודה שונים, לדוגמה, כדי לראות את כל הרשומות בשתי הטבלאות או כדי להשוות בין טבלאות ולחפש שורות שאינן תואמות.

מידע נוסף

תיאור

חיפוש ערכים ברשימת נתונים

הצגת הדרכים הנפוצות לבדיקת מידע בנתונים על-ידי שימוש בפונקציות בדיקת מידע.

LOOKUP

החזרת ערך מטווח של שורה אחת או עמודה אחת או ממערך. לפונקציה LOOKUP יש שתי תבניות תחביר: תבנית הווקטור ותבנית המערך.

HLOOKUP

חיפוש ערך בשורה העליונה של טבלה או מערך של ערכים, והחזרת ערך באותה עמודה משורה שציינת בטבלה או במערך.

VLOOKUP

חיפוש ערך בעמודה הראשונה של מערך טבלה והחזרת ערך באותה שורה מעמודה אחרת במערך הטבלה.

INDEX

החזרת ערך או הפניה לערך מתוך טבלה או טווח. לפונקציה INDEX יש שתי תבניות: תבנית המערך ותבנית ההפניה.

MATCH

החזרת מיקומו היחסי של פריט במערך התואם לערך שצוין בסדר שצוין. השתמש בפונקציה MATCH במקום באחת מפונקציות LOOKUP כאשר דרוש לך מיקומו של פריט בטווח ולא הפריט עצמו.

OFFSET

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

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

הערה: Microsoft אינה מספקת תמיכה עבור מוצרים של ספקים חיצוניים.

ספק

מוצר

Add-in Express Ltd.‎

Ultimate Suite for Excel‏, Merge Tables Wizard‏, Duplicate Remover‏, Consolidate Worksheets Wizard‏, Combine Rows Wizard‏, Cell Cleaner‏, Random Generator‏, Merge Cells‏, Quick Tools for Excel‏, Random Sorter‏, Advanced Find & Replace‏, Fuzzy Duplicate Finder‏, Split Names‏, Split Table Wizard‏, Workbook Manager

Add-Ins.com

Duplicate Finder

AddinTools

AddinTools Assist

J-Walk &Associates, Inc.‎

Power Utility Pak Version 7

WinPure

ListCleaner Lite
ListCleaner Pro
Clean and Match 2007

לראש הדף

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×