רנאט אינה הפעם הראשונה בה הוא כותב אורח על לייפהאקר. מוקדם יותר פרסמנו חומר מעולה ממנו על איך לעשות תוכנית אימונים: ספרים בסיסיים ומשאבים מקוונים, כמו גם צעד אחר צעד אלגוריתם ליצירת תוכנית אימונים.
מאמר זה מכיל טכניקות פשוטות המקלות על העבודה ב- Excel. במיוחד הם יהיו שימושיים לאלה העוסקים בדיווח וניהול, מכין מגוון של דוחות אנליטיים המבוססים על פריקות מ 1C ודוחות אחרים, מייצר מצגות ודיאגרמות לניהול מהם. אני לא טוען על חידוש מוחלט – בצורה זו או אחרת טכניקות אלה יש מסוימים נדונה בפורומים או המוזכרים במאמרים.
חלופות פשוטות של VLOOK ו- GPR, אם הערכים המבוקשים אינם בעמודה הראשונה של הטבלה: VIEW, INDEX + MATCH
פונקציות VLOOKUP ו- HLOOKUP פועלות רק אם ערכי החיפוש נמצאים בעמודה הראשונה או בשורה הראשונה של הטבלה שממנה אתה מתכנן לקבל נתונים.
במקרים אחרים קיימות שתי אפשרויות:
- השתמש בפונקציה LOOKUP.
יש לו את התחביר הבא: VIEW (required_value; vector_view; תוצאה וקטור). אבל עבור הפעולה הנכונה שלה יש צורך כי ערכי טווח vector_view סווגו בסדר עולה:
- השתמש בשילוב של פונקציות MATCH ו- INDEX.
הפונקציה MATCH מחזירה את המספר הסידורי של אלמנט במערך (בעזרתו תוכלו למצוא את הכניסה הרצויה בטבלה ברצף), ואת הפונקציה INDEX מחזירה את אלמנט מערך עם מספר קבוע מראש (אשר אנו לומדים באמצעות MATCH).
תחביר פונקציה:
• התאמה (required_value; מערך החיפוש; match_type) – במקרה שלנו אנחנו צריכים את סוג ההתאמה “התאמה מדויקת”, זה מתאים למספר 0.
• INDEX (array מספר שורה; [column_number]). במקרה זה, אין לציין את מספר העמודה, שכן המערך מורכב משורה אחת.
כיצד למלא במהירות תאים ריקים ברשימה
המשימה היא למלא את התאים בעמודה עם הערכים מלמעלה (כך שהנושא נמצא בכל שורה של הטבלה, ולא רק בשורה הראשונה של גוש הספרים בנושא):
בחר בעמודה “נושא”, לחץ על קלטת קבוצת “בית”, לחץ על “מצא ובחר» → «הדגש את קבוצת תאים» → «תאים ריקים” ולהתחיל להיכנס הנוסחה (כלומר, לשים סימן שוויון) ו מתייחס לתא מלמעלה, פשוט על ידי לחיצה על חץ למעלה על המקלדת. לאחר מכן הקש Ctrl + Enter. לאחר מכן, ניתן לשמור את הנתונים כערכים, שכן הנוסחאות אינן נחוצות עוד:
כיצד למצוא שגיאות בנוסחה
חישוב של חלק נפרד של הנוסחה
כדי להבין נוסחה מורכבת (שבה פונקציות אחרות משמשות טיעונים לפונקציה, כלומר, פונקציות מסוימות מקוננות אצל אחרים) או למצוא את מקור השגיאות בו, לעתים קרובות אתה צריך לחשב את חלקו. ישנן שתי דרכים פשוטות:
- כדי לחשב את החלק של הנוסחה ישירות בסרגל הנוסחה, בחרו בחלק זה ולחצו על F9:
בדוגמה זו, היתה בעיה עם הפונקציה SEARCH – זה היה מבולבל עם טיעונים. חשוב לזכור שאם לא תבטל את החישוב של חלק מהפונקציה ותלחצו על Enter, החלק המחושב יישאר מספר. - לחץ על הלחצן “חשב נוסחה” בקבוצה “פורמולה” על הסרט:
בחלון שמופיע, ניתן לחשב את הנוסחה לפי צעדים ולקבוע באיזה שלב ובאיזו פונקציה מתרחשת השגיאה (אם היא קיימת):
כיצד לקבוע מה הנוסחה תלויה או מה זה מתייחס
כדי לקבוע מאיזה תאים הנוסחה תלויה, בקבוצה “נוסחאות” על הסרט, לחץ על הלחצן “השפעת תאים”:
החיצים מופיעים, המציינים את התוצאה של החישוב תלוי.
אם הסמל המודגש בתמונה מוצג באדום, הנוסחה תלויה בתאים על גיליונות אחרים או בספרים אחרים:
לחיצה על זה, נוכל לראות בדיוק היכן נמצאים תאים או טווחים להשפיע:
ליד כפתור “השפעת תאים” הוא כפתור “תאים תלויים”, אשר פועל באופן דומה: הוא מציג את החצים מהתא הפעיל עם הנוסחה לתאים התלויים בו.
הלחצן “הסרת חיצים” הממוקם באותו בלוק, מאפשר לך להסיר את החצים לתאים המשפיעים, את החצים לתאים התלויים או את שני סוגי החצים בו-זמנית:
כיצד למצוא את הסכום (מספר, ממוצע) של ערכי התא מספר גיליונות
נניח שיש לך כמה גיליונות מאותו סוג עם הנתונים שברצונך להוסיף, לספור או לעבד משהו אחר:
כדי לעשות זאת, התא שבו אתה רוצה לראות את התוצאה, הזן את הנוסחה הסטנדרטית, כגון SUM (SUM), והזן את הטענה ואחריו נקודתיים ואת שם המשפחה של הגיליון הראשון מהרשימה של יריעות אלה, אתה צריך להתמודד:
אתה תקבל את סכום התאים עם כתובת B3 מן הסדינים “Data1”, “Data2”, “Data3”:
כתובת זו פועלת עבור גיליונות הממוקמים בעקביות. התחביר הוא: = FUNCTION (קישור לטווח).
כיצד ליצור באופן אוטומטי ביטויי תבנית – –
באמצעות עקרונות בסיסיים של עבודה עם טקסט ב- Excel ו כמה פונקציות פשוטות, אתה יכול להכין משפטים תבנית עבור דוחות. כמה עקרונות של עבודה עם הטקסט:
- שלב טקסט עם סימן (אתה יכול להחליף אותו עם פונקציה CONCATENATE, אבל זה לא הגיוני).
- הטקסט כתוב תמיד במרכאות, הפניות לתאים עם טקסט תמיד ללא.
- כדי לקבל את סמל השירות “ציטוטים”, השתמש בפונקציה CHAR עם ארגומנט 32.
דוגמה ליצירת ביטוי תבנית באמצעות נוסחאות:
תוצאה:
במקרה זה, מלבד סמל הפונקציה (CHAR) (כדי להציג את הציטוטים) באמצעות הפונקציה IF (IF), מאפשר לך לשנות את הטקסט, תלוי אם את הדינמיקה החיובית של מכירות שם, והתפקוד של הטקסט (טקסט), המאפשר להציג מספר בכל פורמט. התחביר שלה מתואר להלן:
TEXT (ערך; פורמט)
הפורמט מצוטט באותו אופן כאילו הזנת פורמט מותאם אישית בחלון תבנית תאים.
אוטומציה אתה יכול גם טקסטים מורכבים יותר. בפועל שלי הייתי למכן ארוך, אך התגובות השגרתיות על החשבונות והניהול במתכונת “נפלו / עלתה ב XX על התכנית בעיקר בשל הגידול / הקיטון של FAKTORA1 XX, עלייה / ירידת YY FAKTORA2 …» עם הרשימה משתנית גורמים. אם אתה כותב תגובות כאלה לעתים קרובות תהליך הכתיבה יכול algorithmization – שווה תמה פעם ליצור נוסחה או מאקרו שיחסוך ממך חלק לפחות של עבודה.
כיצד לשמור נתונים בכל תא לאחר שילוב
כאשר משלבים תאים, רק ערך אחד נשמר. Excel מזהיר לגבי זה כאשר מנסים למזג תאים:
לפיכך, אם יש לך נוסחה שתלויה בכל תא, זה יפסיק לעבוד לאחר שילובם (# N / A שגיאה בקווים 3-4 של הדוגמה):
כדי למזג תאים, תוך שמירה על נתונים בכל אחד מהם (אולי יש לך נוסחה, כמו בדוגמה מופשטת זו, ייתכן שתרצה למזג תאים, אבל לשמור את כל הנתונים בעתיד, או כדי להסתיר את כוונותיהם), למזג את התאים כל בגליון , בחר אותם ולאחר מכן השתמש בפקודה “תבנית לפי דפוס”, העבר את העיצוב לתאים שברצונך לשלב:
כיצד לבנות מורכב של מספר מקורות נתונים
אם אתה צריך לבנות סיכום בבת אחת ממספר מקורות נתונים, יהיה עליך להוסיף את האשף “PivotTable and Chart Wizard” לסרט או לסרגל הכלים Quick Access, שבו יש אפשרות זו.
אתה יכול לעשות זאת כך: “קובץ” → “אפשרויות” → “Quick Access Toolbar” → “כל הפקודות” → “PivotTable ו תרשים אשף” → “הוסף”:
לאחר מכן, הסמל המתאים מופיע על הקלטת, לחיצה על אשר גורם לאשף אותו:
כאשר תלחץ עליו, תופיע תיבת דו-שיח:
בתוך זה, אתה צריך לבחור “בכמה טווחי קונסולידציה” ולחץ על “הבא”. בפסקה הבאה, תוכל לבחור באפשרות “יצירת שולי עמוד אחד” או “יצירת שולי דפים”. אם אתה רוצה להמציא שם עבור כל אחד ממקורות הנתונים בעצמך, בחר את הפריט השני:
בחלון הבא, הוסף את כל הטווחים שעליהם מבוסס הסיכום, ונותן להם את השמות:
לאחר מכן, בתיבת הדו-שיח האחרונה, ציין היכן ימוקם דוח טבלת הסיכום – בגיליון הקיים או החדש:
דוח טבלת הסיכום מוכן. במסנן ‘דף 1’, תוכל לבחור רק אחד ממקורות הנתונים, במידת הצורך:
כיצד לחשב את מספר המופעים של טקסט A בטקסט B (“MTS תעריף SuperMTS” – שני המופעים של MTS קיצור)
בדוגמה זו, בעמודה A יש מספר מחרוזות טקסט, והמשימה שלנו היא לברר כמה פעמים בכל אחד מהם יש את הטקסט לחיפוש הממוקם בתא E1:
כדי לפתור בעיה זו, ניתן להשתמש בנוסחה מורכבת המורכבת מהפונקציות הבאות:
- LEN (LEN) – מחשבת את אורך הטקסט, הארגומנט היחיד הוא טקסט. דוגמה: DLSTR (“מכונה”) = 6.
- SUBSTITUTE – מחליף את הטקסט במחרוזת הטקסט עם טקסט נוסף. תחביר: SUBSTRATE (טקסט; old_text; new_text). דוגמה: SUBMIT (“car”; “auto”; “”) = “נייד”.
- UPPER – מחליף את כל התווים במחרוזת באותיות רישיות. הארגומנט היחיד הוא טקסט. דוגמה: PROPOSE (“מכונה”) = “מכונה”. אנו זקוקים לפונקציה זו כדי לבצע חיפוש ללא רישיות. אחרי הכל, הצעה (“מכונה”) = PROPISN (“מכונה”)
כדי למצוא את המופע של מחרוזת טקסט מסוימת באחרת, עליך למחוק את כל המופעים שלה במחרוזת המקור ולהשוות את אורך המחרוזת שהתקבלה עם המקור:
(“MTS סופר MTS תעריף”) – DLSTR (“תעריף סופר”) = 6
ואז לחלק את ההבדל על ידי אורך של הקו שאנחנו מחפשים:
6 / DLSTR (“MTS”) = 2
פעמיים את קו “MTS” כלול המקורי.
נותר לכתוב את האלגוריתם הזה בשפה של נוסחאות (אנו מציינים בטקסט את הטקסט שבו אנו מחפשים הופעות, ו”חיפוש “הוא זה שמספר ההתרחשויות בו אנו מעוניינים):
= (DLSTR (את הטקסט) -DLSTR (שלח (הבטחה (את הטקסט); הצעה (רצה); “”))) / ДЛСТР (רצה)
בדוגמה שלנו, הנוסחה נראית כך:
($ E $ 1); “)) () / DLSTR ($ E $ 1)
