פריצות חיים לאקסל לעוסקים בדיווח ובעיבוד נתונים

2024 מְחַבֵּר: Malcolm Clapton | [email protected]. שונה לאחרונה: 2024-01-13 01:35

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

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

חלופות פשוטות ל-VLOOKUP ו-HLOOKUP, אם הערכים הרצויים אינם בעמודה הראשונה של הטבלה: LOOKUP, INDEX + SEARCH

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

אחרת, ישנן שתי אפשרויות:

1. השתמש בפונקציית LOOKUP.

   יש לו את התחביר הבא: LOOKUP (lookup_value; lookup_vector; result_vector). אבל כדי שזה יעבוד נכון, יש למיין את הערכים של טווח view_vector בסדר עולה:

   

2. השתמש בשילוב של פונקציות MATCH ו- INDEX.

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

   

   תחביר פונקציה:

   • SEARCH (search_value; search_array; match_type) - במקרה שלנו, אנחנו צריכים סוג התאמה "התאמה מדויקת", הוא מתאים למספר 0.

   • INDEX (מערך; שורה_מספר; [מספר_עמודה]). במקרה זה, אין צורך לציין את מספר העמודה, מכיוון שהמערך מורכב משורה אחת.

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

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

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

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

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

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

1. כדי לחשב חלק מנוסחה ממש בשורת הנוסחה, בחר את החלק הזה והקש F9:

   

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

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

   

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

   

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

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

מופיעים חיצים כדי לציין במה תלויה תוצאת החישוב.

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

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

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

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

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

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

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

תקבל את סכום התאים עם הכתובת B3 מהגליונות "Data1", "Data2", "Data3":

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

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

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

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

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

תוֹצָאָה:

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

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

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

ניתן לבצע אוטומטית גם טקסטים מורכבים יותר. בתרגול שלי, הייתה אוטומציה של הערות ארוכות אך שגרתיות לדיווח ההנהלה במתכונת "INDICATOR ירד / עלה ב-XX ביחס לתכנית, בעיקר עקב הצמיחה / ירידה של FACTOR1 ב-XX, צמיחה / ירידה של FACTOR2 ב- YY …" עם רשימה משתנה של גורמים. אם אתם כותבים הערות כאלה לעיתים קרובות וניתן לבצע אלגוריתם תהליך כתיבתן, כדאי פעם אחת לתמוה ליצור נוסחה או מאקרו שיחסכו לכם לפחות חלק מהעבודה.

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

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

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

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

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

אם אתה צריך לבנות ציר מכמה מקורות נתונים בו-זמנית, תצטרך להוסיף את "אשף PivotTable and Chart" לסרט או ללוח הגישה המהירה, שיש לו אפשרות כזו.

אתה יכול לעשות זאת באופן הבא: "קובץ" ← "אפשרויות" ← "סרגל הכלים של גישה מהירה" → "כל הפקודות" ← "אשף טבלאות ציר ואשף" → "הוסף":

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

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

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

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

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

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

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

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

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

1. DLSTR (LEN) - מחשב את אורך הטקסט, הארגומנט היחיד הוא הטקסט. דוגמה: DLSTR ("מכונה") = 6.
2. SUBSTITUTE - מחליף טקסט מסוים במחרוזת טקסט באחר. תחביר: SUBSTITUTE (טקסט; טקסט ישן; טקסט_חדש). דוגמה: SUBSTITUTE ("מכונית"; "אוטו"; "") = "נייד".
3. UPPER - מחליף את כל התווים במחרוזת באותיות רישיות. הטיעון היחיד הוא טקסט. דוגמה: UPPER ("מכונה") = "מכונית". אנחנו צריכים את הפונקציה הזו כדי לבצע חיפושים חסרי רישיות. אחרי הכל, UPPER ("מכונית") = UPPER ("מכונה")

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

DLSTR ("תעריף MTS Super MTS") - DLSTR ("תעריף סופר") = 6

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

6 / DLSTR ("MTS") = 2

בדיוק פעמיים השורה "MTS" נכללת בקו המקורי.

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

= (DLSTR (טקסט) -LSTR (SUBSTITUTE (UPPER (טקסט); UPPER (חיפוש), ""))) / DLSTR (חיפוש)

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

= (DLSTR (A2) -LSTR (SUBSTITUTE (UPPER (A2), UPPER ($ E $ 1), ""))) / DLSTR ($ E $ 1)