נוסחאות ניקוי נתונים נפוצות ב- 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 כדי להפחית דואר זבל. למד כיצד הנתונים שלך מעובדים.