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

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

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

אלטרנטיבה פשוטה VLOOKUP ו HLOOKUP, אם הערכים הנדרשים אינם בעמודה הראשונה של הטבלה: VIEW, INDEX + MATCH

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

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

1. להשתמש בתצוגת פונקציה (חיפוש).
   יש לה בתחביר הבא: צפייה (lookup_value חיפוש וקטור; vektor_rezultata). אבל בשביל שזה יעבוד כראוי אתה צריך ערכי טווח וקטור חיפוש הם מוינו בסדר עולה:
2. MATCH להשתמש בשילוב של פונקציות (MATCH) ואינדקס (אינדקס).
   הפונקציה MATCH מחזירה את המספר הסידורי של אלמנט במערך (בעזרתו תוכלו למצוא, ברצף של הטבלה האלמנט הרצוי), ומדד פונקציה מחזירים אלמנט מערך עם מספר קבוע מראש (אשר אנו יודעים לתפקד MATCH).
   instagram viewer
   אפשרויות תחביר:
   • MATCH (lookup_value massiv_poiska; match_type) - במקרה שלנו, אנחנו צריכים סוג של "התאמה מדויקת" השוואה, זה מתאים למספר 0.
   • אינדקס (מערך; ROW_NUM; [Column_num]). במקרה זה, אין צורך לציין את מספר העמודה כמו המערך מורכב של שורה אחת.

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

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

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

איך למצוא טעויות בנוסחה

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

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

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

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

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

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

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

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

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

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

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

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

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

אתה תקבל את כמות תאי כתובת B3 עם גליונות "data1", "Data2", "data3":

עבודות העוסקות כזה עבור גליונות מסודרים ברציפות. התחביר הוא: = תכונה (pervyy_list: הפניה posledniy_list למגוון!).

כיצד לבנות באופן אוטומטי משפטים נוסחתית

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

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

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

תוצאה:

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

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

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

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

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

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

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

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

איך לבנות סיכום של מקורות נתונים מספר

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

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

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

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

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

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

לאחר שלב זה, תקיים בדוח PivotTable בתיבת הדו-שיח האחרון - על הקיים או עבודה חדשה בשם:

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

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

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

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

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

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

LEN ( "תעריף סופר MTS MTS") - LEN ( "שיעור סופר") = 6

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

6 / LEN ( "MTS") = 2

זהו שיעור כפול קו "MTS" הוא במקור.

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

= (LEN (טקסט) -DLSTR (חלופי (UPPER (טקסט); UPPER (מבוקש עבור); ""))) / LEN (מבוקש עבור)

בדוגמה שלנו, הנוסחה היא כדלקמן:

= (LEN (A2) -DLSTR (חלופיים (UPPER (A2); UPPER ($ E 1 $); ""))) / LEN ($ E 1 $)