יצירת נוסחאות אקסל וניקוי נתונים

רווחים:

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

ביתו של איש המקצוע הפיננסי הוא אקסל (או Google Sheets). אבל כתיבת נוסחה מורכבת מאפס, הגדרת IFs מקוננים או ניקוי נתונים מבולגנים יכולים לקחת שעות. כאן נכנסת לתמונה הבינה המלאכותית (AI) בתור "עוזר נוסחה": אתה מסביר מה אתה רוצה בטורקית פשוטה, והיא כותבת את הנוסחה. אבל עם אזהרה: הנוסחה שנוצרה בבינה מלאכותית לעולם אינה נכנסת לקובץ המאסטר מבלי להיבדק. ביחידה זו נלמד כיצד ליצור נוסחאות ונתונים נקיים עם דיסציפלינה של אימות.

תיאור נכון של הנוסחה ל-AI

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

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

צעד אחר צעד: ייצור פורמולה בטוח

1. תאר את המבנה. עמודות, סוגי נתונים, תא שבו תגיע הנוסחה.
2. ציין את המטרה בצורה ברורה. כמו "הוסף את כמות השורות שעומדות בתנאי הבא".
3. ציין תוכנית ואזור. אקסל או גיליונות? האם המפריד העשרוני הוא פסיק?
4. בקשו את הנוסחה ואת ההסבר שלה. תן לו להסביר מה הוא עשה, חלק אחר חלק.
5. בדוק על נתונים קטנים. נסה את זה על 3-5 שורות שבהן אתה יודע את התוצאה, ואז הרחב אותה.

הנחיה חלשה / הנחיה חזקה

הנחיה חלשה: כתוב נוסחת הוספה מותנית באקסל.

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

הנחיה עוצמתית: כתוב נוסחה לאקסל (גרסה טורקית, פסיק מפריד עשרוני). טבלה: A=תאריך, B=מחלקה, C=קטגוריה, D=כמות. הנתונים הם ב-2..500 שורות. אני רוצה: בתא F2, סכום הסכומים בעמודה D עבור השורות עם מחלקה "שיווק" וקטגוריה "פרסום". פלט: 1) הנוסחה עצמה 2) הסבר קצר על מה כל חלק שלה עושה 3) נוסחה חלופית (אם זמינה) שעושה את אותה העבודה

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

כשאתה מבקש נוסחה, תכנן גם כיצד תאמת את התוצאה. השיטה הבטוחה ביותר היא להגדיר טבלת בדיקה קטנה מספיק כדי שתוכל לחשב אותה ביד: 3-5 שורות שלגביהן אתה כבר יודע את התוצאה. אתה מריץ את הנוסחה בטבלה זו ותראה אם ​​היא נותנת את המספר שאתה מצפה. אם אתה נותן את זה, אתה מפיץ את זה; אם לא, אתה יכול לתקן את ההנחיה ולשחזר אותה. השקעה קטנה זו מונעת שגיאה שקטה המתפרסת על פני 500 קווים.

משפחות נוסחאות נפוצות

צריך

אקסל טורקי

אנגלית / גיליונות

הערה

סך הכל מותנה

SUMIZE

SUMIFS

תנאים מרובים

ספירה מותנית

COUNTIFS-TOO

COUNTIFS

כמה קווים מתאימים

חפש

VLOOKUP / INDEX+MATCH

VLOOKUP / INDEX+MATCH

INDEX+MATCH גמיש יותר

ערך מותנה

IF / IFERROR

IF / IFERROR

לניהול שגיאות

חלץ טקסט

משמאל, חלק, מצא

שמאל, אמצע, מצא

בניקוי נתונים

שימו לב: AI עשוי להגיב עם שמות פונקציות באנגלית (SUMIFS, VLOOKUP). אקסל טורקית לא מזהה את אלה; נדרשים מקבילים כגון SUMIFS, VLOOKUP וכו'. הקפד לציין באיזו גרסה אתה משתמש בהנחיה, אחרת הנוסחה תיתן שגיאה.

ניקוי נתונים: מבולגן למאורגן

הנתונים הפיננסיים לרוב מלוכלכים: פורמטים של תאריכים מתערבבים, לסכומים יש אלפי מפרידים, אותו לקוח כתוב אחרת ("ABC Ltd", "ABC Limited", "abc Ltd"). בינה מלאכותית יכולה לפתור את שלבי הניקוי הללו עם נוסחאות ורשימת שלבים כאחד.

תן את תוכנית השלב-אחר-שלב הבאה ואת נוסחאות ה-Excel הנחוצות כדי לנקות את הנתונים המבולגנים: בעיות: התאריכים הם בפורמט 12.03.2025 וגם 2025-03-12; הסכומים כוללים טקסט כגון "1.234.50 TL"; לשמות הלקוחות יש אותיות רישיות/קטנות לא עקביות. יעד: תאריך בפורמט בודד, כמות במספר, שם הלקוח באותיות גדולות. הצג כל שלב עם נוסחה נפרדת, עבוד בעמודות חדשות מבלי להפריע לנתונים המקוריים.

הסבר היגיון Pivot וסיכום

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

ברצוני לסכם טבלה זו לסך ההוצאות לחודש ולמחלקה. תן לי שתי דרכים: 1) שלבי טבלת ציר (איזה שדה לשורה, עמודה, ערך) 2) ערכת נוסחאות שבונה את אותו סיכום כמו SUMIF מבלי להשתמש בציר

הבנת הנוסחה: פתיחת הקופסה השחורה

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

הסבירו לי את הנוסחה הזו שורה אחר שורה, כאילו אני רק לומד אקסל: רשמו מה כל פונקציה עושה, סדר הארגומנטים ובאילו נסיבות הנוסחה הזו תיכשל. לבסוף "איך אני בודק את הנוסחה הזו?" הצע 3 כניסות לדוגמה ויציאות צפויות עבור.=IFERROR(VLOOKUP(A2,List!A:C,3,FALSE);"לא נמצא")

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

טיפ: כאשר נוסחה נותנת תוצאות בלתי צפויות, אתה נותן את כל הנוסחה ל-AI ושואל "למה זה יכול להיות לא בסדר?" לִשְׁאוֹל. תן גם את דוגמאות התא המודל ואת התוצאה שאתה מצפה; רוב הזמן הוא מוצא את שגיאת ההפניה או הסוג (טקסט/מספר) אי התאמה באופן מיידי.

מיני מארזים

מקרה 1 - מלכודת גרסה שגויה. אנליסט הדביק את נוסחת SUMIFS שניתנה על ידי AI באקסל טורקית וכתב #AD? קיבל את השגיאה. כשעדכנתי את ההנחיה ל"גרסה טורקית", המודל נתן SUMIF והנוסחה עבדה. שיעור: ציון הגרסה הוא עבודה של חמש שניות, דילוג הוא צרות של חצי שעה.

מקרה 2 - הבדיקה תפסה שגיאה. בינה מלאכותית הפנתה לתא הלא נכון לחלוקה בנוסחת "השינוי מהחודש שעבר". האנליטיקאי בדק את הנוסחה על 4 שורות שבהן ידע את התוצאה; בשורה אחת התקבלה תוצאה אבסורדית של 900%. הפניה תוקנה. ללא בדיקה, השגיאה הייתה מתפרסת על פני 500 קווים.

מקרה 3 - שעתיים של ניקוי לתוך עשר דקות. רואה חשבון הבחין כי הסכומים בדף חשבון הבנק בן 1,200 השורות היו בפורמט טקסט ("1,234.50 TL") ולא ניתן לחברם. עם השלבים SUBSTITUTE + CONVERT שניתנו על ידי AI, הוא המיר את העמודה למספר תוך עשר דקות; אימת את התוצאה בשלוש שורות ואישר בבדיקה כוללת שכל 1,200 השורות תורגמו.

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

טעויות נפוצות

• לא מציין את גרסת Excel/Sheets. הפונקציה האנגלית נותנת שגיאה בגרסה הטורקית.
• מבקש נוסחה מבלי לתאר את הטבלה. הדגם לא יכול להתאים להפניות; תן את תוכן העמודה.
• הפצת הנוסחה מבלי לבדוק אותה. אל תכנס לקובץ הראשי מבלי לנסות נתונים קטנים עם תוצאות ידועות.
• ניקוי על ידי השחתת הנתונים המקוריים. בצע את הניקוי על עמודות חדשות; הגן על נתונים גולמיים.
• לא מציין את המפריד העשרוני/אלף. בלבול נקודות פסיק מוביל לשגיאות חישוב שקטות.

לסיכום

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

משימת יישום

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

רשימת בדיקה

• [ ] ציינתי את גרסת Excel/Sheets ואת המפריד העשרוני.
• [ ] תיארתי את תוכן העמודה ואת התא שבו תגיע הנוסחה.
• [ ] רציתי תיאור חלק ליד הנוסחה.
• [ ] בדקתי את הנוסחה על נתונים קטנים עם תוצאות ידועות.
• [ ] ניקיתי את הנתונים בעמודות חדשות מבלי לפגוע בנתונים הגולמיים.
• [ ] עשיתי לפחות בדיקת תוצאה אבסורדית אחת לפני ההפצה.