נוסחאות ניקוי נתונים נפוצות ב- Excel

נוסחאות אקסל

במשך שנים השתמשתי בפרסום כמשאב כדי לא רק לתאר כיצד לעשות דברים, אלא גם לשמור לעצמי תיעוד לחפש אחר כך! היום היה לנו לקוח שהעביר לנו קובץ נתוני לקוחות שהיה אסון. כמעט כל תחום עוצב בצורה שגויה; כתוצאה מכך לא הצלחנו לייבא את הנתונים. אמנם יש כמה תוספות נהדרות עבור Excel לניקוי באמצעות Visual Basic, אך אנו מריצים את Office for Mac שאינו תומך במקרו. במקום זאת, אנו מחפשים נוסחאות ישרות שיעזרו לך. חשבתי שאשתף כמה כאלה כאן רק כדי שאחרים יוכלו להשתמש בהם.

הסר תווים שאינם מספרים

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

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

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

הערך מספר שדות באמצעות OR

לעתים קרובות אנו מטהרים רשומות שלמות מייבוא. משתמשים לא מבינים שלא תמיד אתה צריך לכתוב נוסחאות היררכיות מורכבות ושאתה יכול לכתוב משפט OR במקום. בדוגמה זו להלן, אני רוצה לבדוק אם חסרים נתונים A2, B2, C2, D2 או E2. אם חסרים נתונים כלשהם, אני אחזיר 0, אחרת 1. שיאפשר לי למיין את הנתונים ולמחוק את הרשומות שאינן שלמות.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

גזום ושדות שרשור

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

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

חפש כתובת דוא"ל תקפה

נוסחה די פשוטה שמחפשת את ה- @ וגם. בכתובת דוא"ל:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

חלץ שמות פרטיים ושמות משפחה

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

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

ושם המשפחה:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

הגבל את מספר התווים והוסף ...

האם אי פעם רצית לנקות את תיאורי המטא שלך? אם רצית למשוך תוכן ל- Excel ואז לקצץ את התוכן לשימוש בשדה Meta Description (150 עד 160 תווים), תוכל לעשות זאת באמצעות נוסחה זו מ- המקום שלי. זה שובר את התיאור בחלל בצורה נקייה ואז מוסיף את ...:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

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

מה אתה חושב?

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