אקסל האשכול המרכזי לשאלות באקסל/גוגל שיטס

[יעקב ויצמן]

ראי דוגמא
צפה בקובץ המצורף 1565091
יש להקפיד לכתוב בטווח הירוק את השעות בצורה תקנית
דהיינו 03:30
ולא 3:30
הטווח הירוק צריך להיות מוגדר בפורמט שעה.
חישוב הסך הכללי בעמודה הצבוע כאן בצהוב ניתן לביצוע עם פונקציית sum פשוטה.אלא שיש להקפיד שתא זה יהיה בפורמט מספר.
ולבצע מכפלה של ההחזר ב24 כמו שמופיע בצילום מסך הנל.
בהצלחה

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

מעתה ואילך.אין צורך בטווח החישובים שסומן באיקס אדום
כפי שציינתי במאמר לעיל.הפונקצייה מבצעת פעולת כפל בין טווחים כברירת מחדל.ובכפל אין משמעות לסדר האיברים (זוכרים את חוק החילוף בכפל.נכון?..)
בפעולת חיסור הסדר הוא חשוב והכרחי.וכאן יש לזכור לכתוב בארגומנט הפונקצייה .קודם כל את טווח "המחוסר" ולאחר מכן את טווח "המחסר".ולא להיפך...
הפונקצייה כמובן מחברת את כל סך "ההפרשים/יתרות" בפעולת חיבור כדרכה תמיד..
לאחר מכן ההכפלה ב24 וכללי עיצוב התא כמספר נותרים כדלעיל ללא שינוי.
ככל שלומדים יותר על פונקצייה זו, מבינים כמה היא גאונית וסופר שימושית בחיי היום יום...
בהצלחה!

 

[רק שאלה!]

צריכה שוב את עזרת המבינים
יש לי עמודה ורשום בה סך שעות עבודה יומית.
רוצה לדעת איך אני מחשבת את כל העמודה יחד עמ''נ שתצא לי תשובה של שעות
ז''א, נניח 5:45 שעות + 3:30 שעות = 9:15 שעות
איך אני עושה את זה?
תודה מראש!

=sum(לבחור את כל הקוביות)
בעיצוב תאים לבחור שעה.
אם זה יותר מ-24 שעות צריך לחור מותאם אישית [h]:mm
בהצלחה!

 

[רק שאלה!]

טוב. זה אומר לנו שהפורמט לא השתנה למספר.
תקלה כעין זו נשאלה כאן ממש אתמול, וכתבתי עליה בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13226633
תנסי פשוט להוריד את המכפלה של ×24.שיהיה רק sum בלי פעולת כפל.
האם זה הועיל?
אם זה עדיין לא הסתדר נא לשלוח דוגמית מהגיליון לכאן או למייל yv0528174820 בגימייל

@יעקב ויצמן האלוף!!
שים לב שבמקום להכפיל ב- 24 ניתן לבחור בעיצוב הזה [h]:mm כלומר שהשעות הם בסוגריים מרובעות וכך אפשר להגיע למספרים גם יותר מעשרים וארבע.
ואבאר: אקסל הוא חכם וטיפש כאחד ולכן כשבוחרים בעיצוב שעות הוא יודע שאין יותר מ-24 שעות ביום אז שלושים שעות הוא מעצב 06:00 02/01/1900 (כי היום הראשון של אקסל זה 01/01/1900) אז הוא מוסיף יום ושש שעות.
נוסחה שידועה אצל מהנדסי מחשבים שסוגריים מרובעות הוא ממשיך לחשב גם כאשר מתקבלת שגיאה.

 

[יעקב ויצמן]

@יעקב ויצמן האלוף!!
שים לב שבמקום להכפיל ב- 24 ניתן לבחור בעיצוב הזה [h]:mm כלומר שהשעות הם בסוגריים מרובעות וכך אפשר להגיע למספרים גם יותר מעשרים וארבע.
ואבאר: אקסל הוא חכם וטיפש כאחד ולכן כשבוחרים בעיצוב שעות הוא יודע שאין יותר מ-24 שעות ביום אז שלושים שעות הוא מעצב 06:00 02/01/1900 (כי היום הראשון של אקסל זה 01/01/1900) אז הוא מוסיף יום ושש שעות.
נוסחה שידועה אצל מהנדסי מחשבים שסוגריים מרובעות הוא ממשיך לחשב גם כאשר מתקבלת שגיאה.

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

 

[מינוף]

@יעקב ויצמן
לילה טוב
האם יש דרך להריץ נוסחאות INDEX ו-MATCH
דרך נוסחת מערך - ARRAYFORMULA
בגוגל שיטס ?

 

[יעקב ויצמן]

@יעקב ויצמן
לילה טוב
האם יש דרך להריץ נוסחאות INDEX ו-MATCH
דרך נוסחת מערך - ARRAYFORMULA
בגוגל שיטס ?

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

 

[שמעון יוחאי]

בעבר הסברתי את הלוגיקה בשאלה מעין שאלתך למה זה לא אפשרי...תראה בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13081029
אם אקח כדוגמא ואקרא לטבלת המקור -טבלה 1 ולטבלה המיובאת -טבלה 2.
האקסל וכן השיטס "מכבדים את רצונו" של בעל הגיליון .ולא יזיזו /יוסיפו שורה/ות(שנכתב בה משהו) סתם ככה (וגם שיש "מצוקת מקום/מרחב") בגיליון 2,(ובמקרה כזה יתקבל איתות של שגיאת ref בשיטס או spill באקסל - האיתות אומר כאן לבעל הגיליון ."תפנה את הדרך בבקשה..")
אא"כ זו פעולה יזומה של משתמש הגיליון ויש כאן אישור "וגילוי דעת" מפורש.

הזכרת את המילה קיבוע.ובכן זה פיתרון אחד כאופציה.והוא לקבע את טווח היבוא בפונקציית importrange על ידי תיחום הטווח עד לשורה ספציפית בגיליון 1.
ואני יודע שלא לפיתרון כזה התכוונת בשאלתך..
אז מה שניתן לעשות הוא להקצות שטח מסוים בגיליון 2 ושם לכתוב באותו מבנה של עמודות טקסט חופשי
ובגיליון 3 להערים (מלשון ערימה אחד על השני) את טבלה 2 מעל הטווח של הטקסט החופשי.על ידי פונקציית vstack בשילוב עם filter כדי "לצמצם" ולסנן את התאים הריקים כפי שהסברתי בעבר בלינק כאן (בפיתרון השלישי)
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13093177
ככה תוכל לכתוב כרצונך טקסט חופשי בטווח המיועד וכן בגיליון 1.
ובגיליון 3 תקבל את כל הנתונים בצורה מסודרת טווח מעל טווח.
בהצלחה רבה!

שוב ייש"כ עצום על התשובה המפורטת.
עברתי על הפתרונות שהצעת, אך אני חושש שמא לא הובנתי נכון.
אני מצרף כאן הסבר מצולם של הבעיה.
(גילוי נאות, העיסוק שלי הוא לא בניהול אוצה"ס..)
בעמודה A מופיעים הנתונים המיובאים (שמות הספרים), ובעמודה B טקסט רגיל (הסיווג).
בתמונה 1 כתבתי בעמודה B לכל ספר את הסיווג המתאים.
ובתמונה 2 רואים שלאחר שמיינתי את שמות הספרים בקובץ המיובא, אין התאמה בין העמודות.
השאלה היא האם יש פתרון לזה?
תודה רבה.

 

[רק שאלה!]

שוב ייש"כ עצום על התשובה המפורטת.
עברתי על הפתרונות שהצעת, אך אני חושש שמא לא הובנתי נכון.
אני מצרף כאן הסבר מצולם של הבעיה.
(גילוי נאות, העיסוק שלי הוא לא בניהול אוצה"ס..)
בעמודה A מופיעים הנתונים המיובאים (שמות הספרים), ובעמודה B טקסט רגיל (הסיווג).
בתמונה 1 כתבתי בעמודה B לכל ספר את הסיווג המתאים.
ובתמונה 2 רואים שלאחר שמיינתי את שמות הספרים בקובץ המיובא, אין התאמה בין העמודות.
השאלה היא האם יש פתרון לזה?
תודה רבה.

צפה בקובץ המצורף 1566567צפה בקובץ המצורף 1566568

תבחר את כל העמודים ותבחר 'מיין' [בכרטיסיית נתונים] ותבחר עמודה A

 

[יעקב ויצמן]

שוב ייש"כ עצום על התשובה המפורטת.
עברתי על הפתרונות שהצעת, אך אני חושש שמא לא הובנתי נכון.
אני מצרף כאן הסבר מצולם של הבעיה.
(גילוי נאות, העיסוק שלי הוא לא בניהול אוצה"ס..)
בעמודה A מופיעים הנתונים המיובאים (שמות הספרים), ובעמודה B טקסט רגיל (הסיווג).
בתמונה 1 כתבתי בעמודה B לכל ספר את הסיווג המתאים.
ובתמונה 2 רואים שלאחר שמיינתי את שמות הספרים בקובץ המיובא, אין התאמה בין העמודות.
השאלה היא האם יש פתרון לזה?
תודה רבה.

צפה בקובץ המצורף 1566567צפה בקובץ המצורף 1566568

עד כמה שהבנתי את שאלתך.
אז ככה:
א.בטווח שמתואר בתמונות ששלחת .אל תזין ידנית שום דבר.
ב.תבדוק שבפונקציית importrange כלולים עמודות שמות הספרים +סיווג.
ג.אם אתה מבצע מיון(רגיל בסרגל הכלים) תעשה זאת בטבלת המקור בלבד
ד.אם תרצה שיתבצע מיון אוטומטי בטבלה המיובאת(ללא מיון בטבלת המקור) תעטוף את importrange בפונקציית sort.
לאחר הפסיק תעבור לארגומנט השני ותבחר 1 או 2.
אם עדיין לא הסתדר לך .נא שלח לינקים של הגיליונות למייל ואטפל בזה בשמחה ובל"נ עד תום.
yv0528174820 בגימייל

 

[שמעון יוחאי]

עד כמה שהבנתי את שאלתך.
אז ככה:
א.בטווח שמתואר בתמונות ששלחת .אל תזין ידנית שום דבר.
ב.תבדוק שבפונקציית importrange כלולים עמודות שמות הספרים +סיווג.
ג.אם אתה מבצע מיון(רגיל בסרגל הכלים) תעשה זאת בטבלת המקור בלבד
ד.אם תרצה שיתבצע מיון אוטומטי בטבלה המיובאת(ללא מיון בטבלת המקור) תעטוף את importrange בפונקציית sort.
לאחר הפסיק תעבור לארגומנט השני ותבחר 1 או 2.
אם עדיין לא הסתדר לך .נא שלח לינקים של הגיליונות למייל ואטפל בזה בשמחה ובל"נ עד תום.
yv0528174820 בגימייל

עמודה A היא מיובאת, ועמודה B קיימת רק בקובץ הנוכחי (לא בקובץ המיובא).
ואני רוצה שהנתונים בעמודה B לא יושפעו משינוי סדר השורות שמתבצע בקובץ המקור המיובא.
(כלומר שאם מוסיפים (בקובץ המקור בעמודה A) שורה למעלה, או שממיינים את העמודה, הנתונים בעמודה B יישארו תואמים לנתונים בעמודה A).
תודה ושוב תודה.

 

[יעקב ויצמן]

עמודה A היא מיובאת, ועמודה B קיימת רק בקובץ הנוכחי (לא בקובץ המיובא).
ואני רוצה שהנתונים בעמודה B לא יושפעו משינוי סדר השורות שמתבצע בקובץ המקור המיובא.
(כלומר שאם מוסיפים (בקובץ המקור בעמודה A) שורה למעלה, או שממיינים את העמודה, הנתונים בעמודה B יישארו תואמים לנתונים בעמודה A).
תודה ושוב תודה.

המשימה כאן היא לא אפשרית מסיבה ולוגיקה מאוד פשוטה.
עמודות a וb,כל אחת מתנהגת באופן עצמאי לחלוטין.אין שום קשר נסיבתי בינהן!
בעמודה a "נשפכים" נתונים כפי הקובץ המיובא.
ומנגד אתה מזין נתונים ידנית בעמודה b כרצונך.
שתי עמודות אלו "יקומים מקבילים" ללא שום נקודת השקה /מפגש בינהן.
מי שדינאמי פה היא עמודה A ולא B .כלומר גם אם לא תוסיף לכתוב שום דבר בעמודה b עמודה a תמשיך לזוז ולנוע כפי טבלת המקור.ואת זה חשוב להבין ...
הפיתרון הכי הגיוני פה זה להזין את עמודת הסיווג בקובץ המיובא .ולהכליל את העמודה בפונקציה.ואז כבר ניתן לדבר על מיון ידני/על ידי sort.

 

[שמעון יוחאי]

המשימה כאן היא לא אפשרית מסיבה ולוגיקה מאוד פשוטה.
עמודות a וb,כל אחת מתנהגת באופן עצמאי לחלוטין.אין שום קשר נסיבתי בינהן!
בעמודה a "נשפכים" נתונים כפי הקובץ המיובא.
ומנגד אתה מזין נתונים ידנית בעמודה b כרצונך.
שתי עמודות אלו "יקומים מקבילים" ללא שום נקודת השקה /מפגש בינהן.
מי שדינאמי פה היא עמודה A ולא B .כלומר גם אם לא תוסיף לכתוב שום דבר בעמודה b עמודה a תמשיך לזוז ולנוע כפי טבלת המקור.ואת זה חשוב להבין ...

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

 

[יעקב ויצמן]

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

המושג יחסיות/התייחסות זה חייב להיות תמיד כלפי משהו אחר מוגדר.
אם תעשה כפי שהצעתי תהיה חפיפה "ויחס" בין עמודת ספרים לעמודת הסיווג.
אם עמודת הסיווג תהיה איפה שהוא בגיליון המקור.גם אם לא תכלול אותה בimportrange הנוכחי .יהיה ניתן לייבא אותה בimportange נפרד.ולבצע התאמות עם xlookup או filter ועוד.
אך כרגע אין שום קשר בינהן.לכן המושג יחסיות לא קיים/רלוונטי כאן

 

[שמעון יוחאי]

המושג יחסיות/התייחסות זה חייב להיות תמיד כלפי משהו אחר מוגדר.
אם תעשה כפי שהצעתי תהיה חפיפה "ויחס" בין עמודת ספרים לעמודת הסיווג.
אם עמודת הסיווג תהיה איפה שהוא בגיליון המקור.גם אם לא תכלול אותה בimportrange הנוכחי .יהיה ניתן לייבא אותה בimportange נפרד.ולבצע התאמות עם xlookup או filter ועוד.
אך כרגע אין שום קשר בינהן.לכן המושג יחסיות לא קיים/רלוונטי כאן

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

 

[יעקב ויצמן]

התכוונתי יחסית לנתונים אחרים הקיימים בקובץ הנוכחי.
ולגוף הדבר, בנוסח אחר השאלה היא האם הקובץ החדש בכלל יודע איזה נתונים הוא מייבא?

בודאי שהשיטס יודע אלו ערכים כתובים כעת בעמודה a.

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

אפשרי בהחלט.זה לא שונה מכל filter /vlookup/xlookupועוד קלאסים שאנו מבצעים.
אבל זו לא היתה שאלתך לעיל.ואם לכך התכוונת מלכתחילה אז התשובה היא כן.

 

[מינוף]

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

לא משהו מורכב
הנוסחאות האלו בעצם מחליפות את VLOOKUP
השאלה אם אפשר להריץ אותם בנוסחת מערך

 

[יעקב ויצמן]

לא משהו מורכב
הנוסחאות האלו בעצם מחליפות את VLOOKUP
השאלה אם אפשר להריץ אותם בנוסחת מערך

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

 

[שמעון יוחאי]

בודאי שהשיטס יודע אלו ערכים כתובים כעת בעמודה a.

אפשרי בהחלט.זה לא שונה מכל filter /vlookup/xlookupועוד קלאסים שאנו מבצעים.
אבל זו לא היתה שאלתך לעיל. ואם לכך התכוונת מלכתחילה אז התשובה היא כן.

כנראה שהסברה היא לא התחום החזק שלי...
יש לך איזה הצעה לנוסחה בשביל מה שכתבתי?

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

או הצעה של פתרון אחר לבעיה?
שוב תודה.

 

[יעקב ויצמן]

כנראה שהסברה היא לא התחום החזק שלי...
יש לך איזה הצעה לנוסחה בשביל מה שכתבתי?

או הצעה של פתרון אחר לבעיה?
שוב תודה.

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

 

[מסתודד]

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