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

מאיר למברגר

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

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
שאלה פשוטה, משום מה שכחתי את הא'ב' של אקסל:
יש לי טור של הכנסות וטור של הוצאות
מה הנוסחה לתא אחד שיחשב את סיכום כל טור ההכנסות מינוס סיכום כל טור ההוצאות
Screenshot_20231130_223427_Sheets.jpg

בהצלחה
 

מליה

משתמש מקצוען
שלום וברכה לכל העוזרים והצופים
שאלתי היא כזאת
מעוניינת לייצר תא שימצא מתוך טבלה של מוצרים שנקנו בזמנים שונים
כל מוצר לפי הערך הכי יקר שנקנה והערך הכי זול שנקנה
לדוגמא:
תירס - 30 ש"ח
קולה - 50 ש"ח
תירס - 20 ש"ח
תירס יקר { 30 } תירס זול { 20 }

תודה מראש!
 

מינוף

משתמש מקצוען
אוטומציה עסקית
שלום וברכה לכל העוזרים והצופים
שאלתי היא כזאת
מעוניינת לייצר תא שימצא מתוך טבלה של מוצרים שנקנו בזמנים שונים
כל מוצר לפי הערך הכי יקר שנקנה והערך הכי זול שנקנה
לדוגמא:
תירס - 30 ש"ח
קולה - 50 ש"ח
תירס - 20 ש"ח
תירס יקר { 30 } תירס זול { 20 }

תודה מראש!
MAXIFS - להחזרת הסכום הגבוה
MINIFS - להחזרת הסכום הנמוך
 

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
MAXIFS - להחזרת הסכום הגבוה
MINIFS - להחזרת הסכום הנמוך
ישר כח.
אזכיר כי הפונקציות הנזכרות זמינות באקסל 365 או בשיטס בלבד.
לגירסאות אקסל יותר ישנות ניתן לבצע זאת על ידי שילוב פונקציות max או min "העוטפות" את פונקציית if
כמו בדוגמא המצורפת כאן בצילום מסך.
Screenshot_20231201_094531_Excel.jpg

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

בהצלחה רבה!
 

קבצים מצורפים

  • max-min and if functions in Excel.xlsx
    KB 51 · צפיות: 35

MOISHI408

משתמש צעיר
עיצוב גרפי
אני משתמש בקוד הזה בהצלחה כדי להוסיף אוטומטית שעה ותאריך בעמודה 'תאריך' בכל פעם שאני מקליד באותה שורה בעמודה 'שם', זה עובד לי בגליון3. אני רוצה שזה יעבוד לי בכל אחד מהגליונות בקובץ, מה צריך לשנות בקוד?
זה הקוד:
קוד:
/*checking merav asaf*/

function onEdit(event)
{
  var timezone = "GMT+3";
  var timestamp_format = "dd/MM/yyyy HH:mm"; // Timestamp Format.
  var updateColName = "שם";
  var timeStampColName = "תאריך";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("גיליון3");

  var actRng = SpreadsheetApp.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'שם' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}
 

MOISHI408

משתמש צעיר
עיצוב גרפי
אני משתמש בקוד הזה בהצלחה כדי להוסיף אוטומטית שעה ותאריך בעמודה 'תאריך' בכל פעם שאני מקליד באותה שורה בעמודה 'שם', זה עובד לי בגליון3. אני רוצה שזה יעבוד לי בכל אחד מהגליונות בקובץ, מה צריך לשנות בקוד?
זה הקוד:
קוד:
/*checking merav asaf*/

function onEdit(event)
{
  var timezone = "GMT+3";
  var timestamp_format = "dd/MM/yyyy HH:mm"; // Timestamp Format.
  var updateColName = "שם";
  var timeStampColName = "תאריך";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("גיליון3");

  var actRng = SpreadsheetApp.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'שם' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}
בינתיים בינה מלאכותית ענתה לי להחליף את getSheetByName("גיליון3") בgetActiveSheet()
זה אכן עובד, אני גם מחקתי הgetActiveSpreadsheet()

הקוד הוא:
קוד:
/*checking merav asaf*/

function onEdit(event)
{
  var timezone = "GMT+3";
  var timestamp_format = "dd/MM/yyyy HH:mm"; // Timestamp Format.
  var updateColName = "שם";
  var timeStampColName = "תאריך";
  var sheet = SpreadsheetApp.getActiveSheet();

  var actRng = SpreadsheetApp.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'שם' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}
 

שני ורנר.

משתמש סופר מדליק
מנוי פרימיום
בוגר/תלמיד פרוג
עיצוב גרפי
עריכה והפקת סרטים
עימוד ספרים
היי
אני רוצה לעשות רשימה אוטומטית של מספרים מ0001 - 1000
מה הדרך?

תודה!
 
נערך לאחרונה ב:

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
היי
אני רוצה לעשות רשימה אוטומטית של מספרים מ0001 - 1000
מה הדרך?

תודה!
לכתוב בשני תאים בהזנה ידנית 1 ו2 ככה
Screenshot_20231203_142528_Sheets.jpg

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

יהההונתן

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

יעקב ויצמן

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

doc-turn-off-auto-complete-3.png


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

3.לבצע זאת בפקודת מאקרו vba ראה כאן

בהצלחה!
 

יחיאל מ.

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

arye izerwerk

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

קבצים מצורפים

  • מדפים.xlsx
    KB 10.3 · צפיות: 28

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
לצורך בניית קטלוג ספרים ברשותי טבלה עם מספר עמודות.
בכל עמודה הכותרת היא מספר המדף והתאים הם הספרים שבתוכה.
אני מעוניין ליצור גליון נוסף מבוסס על הטבלה הנ"ל ובה יופיע כל ספר ובמקביל יופיע מספר המדף.
יש דרך לעשות את זה?
מצרף גליון דוגמא
אני רוצה בעצם גליון חדש שבו יופיע לי כל ספר עם הכותרת של העמודה שלו, לדוגמא: ש"ע או"ח, 1.
הלכות בשר בחלב, 3.
וכן הלאה.
אודה לעזרתכם.
בוקר טוב
זו באמת שאלה מעניינת ומאידך קשה ומאתגרת מאוד.
אתחיל מהסוף של הפיתרון שערכתי בס"ד.אז הנה התוצאות בשטח מילוי הכתום,כאשר שמות הספרים ממוינות ומקובצות כפי שאסביר להלן:
Screenshot_20231205_091352_Sheets.jpg


ועכשיו קצת הסבר על כל הטוב שמתרחש כאן:
אז בעמודות a.b.c מוצגת טבלת המקור כידוע.
כל ההמשך מכאן ואילך יכול להיות בגיליון נפרד כפי ששאלת אם רק תעשה זאת בזהירות רבה ובהקפדה על כללי הפניית ערכים בין גיליונות וכן שמירת הקיבועים במקומם הרצוי ובאופן מדויק!
ובכן בעמודה e כהכנה לפיתרון כל טבלת המקור ממוינת (בעזרת sort),מקובצת בעמודה אחת (בעזרת vstack) תוך כדי הסרת הרווחים הריקים בטבלת המקור(בעזרת filter)
למעשה,טכניקה זו כבר הוזכרה לעיל על ידי בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12984676
וכמעשהו התם כך מעשהו הכא...
Screenshot_20231205_090916_Sheets.jpg


בעמודות f.g.h מתבצע חיפוש והחזרת מספר המדף של כל ספר.כל עמודה מתייחסת לטווח עמודה שונה בטבלת המקור.
בעזרת פונקציות MATCH .IFERROR.IF
אני ממליץ ללמוד על פונקציית MATCH המופלאה להבנת כל החשבון כאן.
Screenshot_20231205_090927_Sheets.jpg


כל שנותר הוא לסכם בעמודה i שורה אחר שורה את הטווח הצבוע בירוק.זאת על ידי פונקציית sum פשוטה וקטנטונת.
Screenshot_20231205_090945_Sheets.jpg



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


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

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


בהצלחה רבה לך!
 

קבצים מצורפים

  • How to identify and return row and column number of cell in Excel-.xlsx
    KB 102 · צפיות: 24
נערך לאחרונה ב:

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
באגב.אזכיר בהזדמנות זו את שתי הפונקציות row ואחותה column
Screenshot_20231205_112901_Sheets.jpg

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

מאכער

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

קבצים מצורפים

  • טבלה לבדיקה.xlsx
    KB 10.6 · צפיות: 26

יעקב ויצמן

מנהל פורום מחשבים
מנהל
מנוי פרימיום
יש לי טבלה של נתוני דירות.
יש 3 טיפוסים של דירות, ולכל טיפוס יש שטח אחר כיוונים שונים וכו'
אני רוצה שהתאים בכל שורה יתמלאו אוטומטית לפי הנתונים שהגדרתי מראש לכל טיפוס דירה
איך עושים את זה?
מצורף קובץ לדוגמא
תודה רבה!
פונקציית xlookup או vlookup
הכי נוח "ופשוט"
בהצלחה
 

אולי מעניין אותך גם...

הפרק היומי

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


תהילים פרק קכו

א שִׁיר הַמַּעֲלוֹת בְּשׁוּב יי אֶת שִׁיבַת צִיּוֹן הָיִינוּ כְּחֹלְמִים:ב אָז יִמָּלֵא שְׂחוֹק פִּינוּ וּלְשׁוֹנֵנוּ רִנָּה אָז יֹאמְרוּ בַגּוֹיִם הִגְדִּיל יי לַעֲשׂוֹת עִם אֵלֶּה:ג הִגְדִּיל יי לַעֲשׂוֹת עִמָּנוּ הָיִינוּ שְׂמֵחִים:ד שׁוּבָה יי אֶת (שבותנו) שְׁבִיתֵנוּ כַּאֲפִיקִים בַּנֶּגֶב:ה הַזֹּרְעִים בְּדִמְעָה בְּרִנָּה יִקְצֹרוּ:ו הָלוֹךְ יֵלֵךְ וּבָכֹה נֹשֵׂא מֶשֶׁךְ הַזָּרַע בֹּא יָבוֹא בְרִנָּה נֹשֵׂא אֲלֻמֹּתָיו:
נקרא  106  פעמים

אתגר AI

תאומים • אתגר 145

לוח מודעות

למעלה