Excel-lifhaki עבור אלה העוסקים בדיווח ועיבוד נתונים

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

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

חלופות פשוטות של VLOOK ו- GPR, אם הערכים המבוקשים אינם בעמודה הראשונה של הטבלה: VIEW, INDEX + MATCH

פונקציות VLOOKUP ו- HLOOKUP פועלות רק אם ערכי החיפוש נמצאים בעמודה הראשונה או בשורה הראשונה של הטבלה שממנה אתה מתכנן לקבל נתונים.

במקרים אחרים קיימות שתי אפשרויות:

  1. השתמש בפונקציה LOOKUP.
    יש לו את התחביר הבא: VIEW (required_value; vector_view; תוצאה וקטור). אבל עבור הפעולה הנכונה שלה יש צורך כי ערכי טווח vector_view סווגו בסדר עולה:אקסל
  2. השתמש בשילוב של פונקציות MATCH ו- INDEX.
    הפונקציה MATCH מחזירה את המספר הסידורי של אלמנט במערך (בעזרתו תוכלו למצוא את הכניסה הרצויה בטבלה ברצף), ואת הפונקציה INDEX מחזירה את אלמנט מערך עם מספר קבוע מראש (אשר אנו לומדים באמצעות MATCH).אקסלתחביר פונקציה:
    • התאמה (required_value; מערך החיפוש; match_type) – במקרה שלנו אנחנו צריכים את סוג ההתאמה “התאמה מדויקת”, זה מתאים למספר 0.
    • INDEX (array מספר שורה; [column_number]). במקרה זה, אין לציין את מספר העמודה, שכן המערך מורכב משורה אחת.

כיצד למלא במהירות תאים ריקים ברשימה

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

אקסל

בחר בעמודה “נושא”, לחץ על קלטת קבוצת “בית”, לחץ על “מצא ובחר» → «הדגש את קבוצת תאים» → «תאים ריקים” ולהתחיל להיכנס הנוסחה (כלומר, לשים סימן שוויון) ו מתייחס לתא מלמעלה, פשוט על ידי לחיצה על חץ למעלה על המקלדת. לאחר מכן הקש Ctrl + Enter. לאחר מכן, ניתן לשמור את הנתונים כערכים, שכן הנוסחאות אינן נחוצות עוד:

  כיצד למקסם את חיי הסוללה של MacBook ולהאריך את חייו

ezgif.com-גודל

כיצד למצוא שגיאות בנוסחה

חישוב של חלק נפרד של הנוסחה

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

  1. כדי לחשב את החלק של הנוסחה ישירות בסרגל הנוסחה, בחרו בחלק זה ולחצו על F9:
    ezgif.com-גודל (1)
    בדוגמה זו, היתה בעיה עם הפונקציה SEARCH – זה היה מבולבל עם טיעונים. חשוב לזכור שאם לא תבטל את החישוב של חלק מהפונקציה ותלחצו על Enter, החלק המחושב יישאר מספר.
  2. לחץ על הלחצן “חשב נוסחה” בקבוצה “פורמולה” על הסרט:

    Excel
    בחלון שמופיע, ניתן לחשב את הנוסחה לפי צעדים ולקבוע באיזה שלב ובאיזו פונקציה מתרחשת השגיאה (אם היא קיימת):
    ezgif.com-גודל (2)

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

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

Excel

החיצים מופיעים, המציינים את התוצאה של החישוב תלוי.

אם הסמל המודגש בתמונה מוצג באדום, הנוסחה תלויה בתאים על גיליונות אחרים או בספרים אחרים:

Excel

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

Excel

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

הלחצן “הסרת חיצים” הממוקם באותו בלוק, מאפשר לך להסיר את החצים לתאים המשפיעים, את החצים לתאים התלויים או את שני סוגי החצים בו-זמנית:

Excel

כיצד למצוא את הסכום (מספר, ממוצע) של ערכי התא מספר גיליונות

נניח שיש לך כמה גיליונות מאותו סוג עם הנתונים שברצונך להוסיף, לספור או לעבד משהו אחר:

Excel Excel

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

Excel

אתה תקבל את סכום התאים עם כתובת B3 מן הסדינים “Data1”, “Data2”, “Data3”:

Excel

כתובת זו פועלת עבור גיליונות הממוקמים בעקביות. התחביר הוא: = FUNCTION (קישור לטווח).

כיצד ליצור באופן אוטומטי ביטויי תבנית – –

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

  • שלב טקסט עם סימן (אתה יכול להחליף אותו עם פונקציה CONCATENATE, אבל זה לא הגיוני).
  • הטקסט כתוב תמיד במרכאות, הפניות לתאים עם טקסט תמיד ללא.
  • כדי לקבל את סמל השירות “ציטוטים”, השתמש בפונקציה CHAR עם ארגומנט 32.

דוגמה ליצירת ביטוי תבנית באמצעות נוסחאות:

Excel

תוצאה:

Excel

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

TEXT (ערך; פורמט)

הפורמט מצוטט באותו אופן כאילו הזנת פורמט מותאם אישית בחלון תבנית תאים.

אוטומציה אתה יכול גם טקסטים מורכבים יותר. בפועל שלי הייתי למכן ארוך, אך התגובות השגרתיות על החשבונות והניהול במתכונת “נפלו / עלתה ב XX על התכנית בעיקר בשל הגידול / הקיטון של FAKTORA1 XX, עלייה / ירידת YY FAKTORA2 …» עם הרשימה משתנית גורמים. אם אתה כותב תגובות כאלה לעתים קרובות תהליך הכתיבה יכול algorithmization – שווה תמה פעם ליצור נוסחה או מאקרו שיחסוך ממך חלק לפחות של עבודה.

כיצד לשמור נתונים בכל תא לאחר שילוב

כאשר משלבים תאים, רק ערך אחד נשמר. Excel מזהיר לגבי זה כאשר מנסים למזג תאים:

Excel

לפיכך, אם יש לך נוסחה שתלויה בכל תא, זה יפסיק לעבוד לאחר שילובם (# N / A שגיאה בקווים 3-4 של הדוגמה):

Excel

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

ezgif.com-גודל (3)

כיצד לבנות מורכב של מספר מקורות נתונים

אם אתה צריך לבנות סיכום בבת אחת ממספר מקורות נתונים, יהיה עליך להוסיף את האשף “PivotTable and Chart Wizard” לסרט או לסרגל הכלים Quick Access, שבו יש אפשרות זו.

אתה יכול לעשות זאת כך: “קובץ” → “אפשרויות” → “Quick Access Toolbar” → “כל הפקודות” → “PivotTable ו תרשים אשף” → “הוסף”:

Excel

לאחר מכן, הסמל המתאים מופיע על הקלטת, לחיצה על אשר גורם לאשף אותו:

Excel

כאשר תלחץ עליו, תופיע תיבת דו-שיח:

Excel

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

Excel

בחלון הבא, הוסף את כל הטווחים שעליהם מבוסס הסיכום, ונותן להם את השמות:

ezgif.com-גודל (4)

לאחר מכן, בתיבת הדו-שיח האחרונה, ציין היכן ימוקם דוח טבלת הסיכום – בגיליון הקיים או החדש:

Excel

דוח טבלת הסיכום מוכן. במסנן ‘דף 1’, תוכל לבחור רק אחד ממקורות הנתונים, במידת הצורך:

Excel

כיצד לחשב את מספר המופעים של טקסט A בטקסט B (“MTS תעריף SuperMTS” – שני המופעים של MTS קיצור)

בדוגמה זו, בעמודה A יש מספר מחרוזות טקסט, והמשימה שלנו היא לברר כמה פעמים בכל אחד מהם יש את הטקסט לחיפוש הממוקם בתא E1:

Excel

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

  1. LEN (LEN) – מחשבת את אורך הטקסט, הארגומנט היחיד הוא טקסט. דוגמה: DLSTR (“מכונה”) = 6.
  2. SUBSTITUTE – מחליף את הטקסט במחרוזת הטקסט עם טקסט נוסף. תחביר: SUBSTRATE (טקסט; old_text; new_text). דוגמה: SUBMIT (“car”; “auto”; “”) = “נייד”.
  3. UPPER – מחליף את כל התווים במחרוזת באותיות רישיות. הארגומנט היחיד הוא טקסט. דוגמה: PROPOSE (“מכונה”) = “מכונה”. אנו זקוקים לפונקציה זו כדי לבצע חיפוש ללא רישיות. אחרי הכל, הצעה (“מכונה”) = PROPISN (“מכונה”)

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

(“MTS סופר MTS תעריף”) – DLSTR (“תעריף סופר”) = 6

ואז לחלק את ההבדל על ידי אורך של הקו שאנחנו מחפשים:

6 / DLSTR (“MTS”) = 2

פעמיים את קו “MTS” כלול המקורי.

נותר לכתוב את האלגוריתם הזה בשפה של נוסחאות (אנו מציינים בטקסט את הטקסט שבו אנו מחפשים הופעות, ו”חיפוש “הוא זה שמספר ההתרחשויות בו אנו מעוניינים):

= (DLSTR (את הטקסט) -DLSTR (שלח (הבטחה (את הטקסט); הצעה (רצה); “”))) / ДЛСТР (רצה)

בדוגמה שלנו, הנוסחה נראית כך:

($ E $ 1); “)) () / DLSTR ($ E $ 1)

Excel

  איך לגלגל שרוולים: 3 דרכים מעניינות

댓글 달기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다

Scroll to Top