תחומים - פורום חרדי מקצועי

@גימפל הפונקציה ADDRESS מחזירה מחרוזת (טקסט) ולא הפנייה "אמיתית" של תא, דהיינו

ADDRESS(1,1) == "A1" // מחרוזת ולא כתובת

לכן צריך להעזר בפונקציה INDIRECT שהופכת את המחרוזת לכתובת תא.

INDIRECT("A1") == A1 // כתובת

אגב אפשר לפתור את הבעיה בצורה קצת יותר אלגנטית ע"י שימוש בפונקציה INDEX
לשליפת הערך בתאים הזוגיים, נרשום בתא B1

=INDEX(A:A,ROW(B2)*2)

ובתא C1

=INDEX(A:A,(ROW(C2)-1)*2)

וכמובן נשכפל את הנוסחא למטה כנ"ל

אחרי קצת התבוננות, ראיתי שבכלל אין צורך בשימוש בOFFSET, ואפשר לקפוץ ישר לתא הרצוי:

spreadsheet.setActiveRange(spreadsheet.getRange(spreadsheet.getLastRow()+1, 1, 1, 1));

@מאסטר-באקסל
מסתבר שיש פונקציה מובנית ()getLastRow שמחזירה את מספר השורה האחרונה הריקה. הפחתתי 1 כי אתה משתמש בהיסט (offset) וצריך לזוז שורה פחות

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

אבל אפשר הסבר על השוני בנוסחאות,
ועל צורת הפעולה שלו?
(של ה- OFFSET)

שני הפרמטרים האחרונים הם אופציונאליים, ומציינים את טווח היעד הנבחר (כמה שורות וכמה עמודות)
אם משמיטים אותם, הפונקציה תחזיר טווח הזהה לטווח הפעיל.
למשל:
אם הטווח הפעיל הוא לא תא אחד, אלא קבוצת תאים, למשל A1:B2 (=4 תאים) הפונקציה מחזירה טווח של 4 תאים:

offset(1,1) // == B2:C3

אלא אם כן תציין בפירוש שברצונך לקבל רק תא אחד

offset(1,1,1,1) // == B2

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

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

 spreadsheet.getCurrentCell().offset(rows('A:A')-countblank('A:A')+1,0), 0).activate();

תנסה במקום

spreadsheet.setActiveCell(spreadsheet.getDataRange().offset(spreadsheet.getLastRow()-1, 0, 1,  1));

@מאסטר-באקסל תוכל לרשום בדיוק את כל הקוד שעשית?

@nigun יתכן שבאמת הן מיותרות (לא בדקתי בשטח).. מתקן

@dovid אמר בשדרוג מראה עורך ה-VBA לערכת נושא VS2019 Dark:

https://github.com/rubberduck-vba/Rubberduck/

אני משתמש כל הזמן ב RubberDuck וזה תוספת מבורכת, אבל לא ידוע לי שאפשר להכניס קוד .NET...

@clickone אמר בשדרוג מראה עורך ה-VBA לערכת נושא VS2019 Dark:

יש תוכנות שלהם שהם עשו את זה

איפה?

@nigun אמר בביצוע שאילתא על כל הקבוצות בטבלה:

אבל אני רוצה לקבל את הממוצע של הקבוצה פר משתמש
זאת אומרת 15.

בסדר, גם פה אפשר שתי שאילתות קיבוץ משורשרות, ברשותך אני אכתוב רק באנגלית כי זה הכי נוח לי:
א - חישוב סה"כ ניקוד לתלמיד

SELECT ClassID, StudentID, SUM(Score) AS StudentTotalScore
FROM table
GROUP BY ClassID, StudentID

ב - חישוב הממוצע של הכיתה:

SELECT T.ClassID, AVG(T.StudentTotalScore) AS ClassAverageScore
FROM (SELECT ClassID, StudentID, SUM(Score) AS StudentTotalScore
FROM table
GROUP BY ClassID, StudentID ) AS T
GROUP BY T.ClassID

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

מה רע באיך שכתבתי?

הניחוש שלי היא שבגלל אופן הפעולה שתיארת למעלה, התאים בגליון 1 שנמחקו - לא נחשבים ריקים, (ניקוי ערך לא הופך תא לריק, הוא הופך אותו לתא שמכיל מחרוזת ריקה) ואז הפונקציה COUNTBLANK מחזירה 0, מה שגורם לכך ש:

OFFSET(ROWS('A:A')-COUNTBLANK('A:A')+1,0) 
==
OFFSET(ROWS('A:A')-0+1,0) // זליגת תחום שורות

איך כותבים את זה נכון?

אפשרות א:
תשנה את הפונקציה clear כך שהתאים יימחקו לגמרי

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

function getFirstEmptyRowByColumnArray() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

עכשיו תכתוב:

OFFSET(getFirstEmptyRowByColumnArray(),0)

@nigun אמר בביצוע שאילתא על כל הקבוצות בטבלה:
ערבבת כאן כמה דברים:

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

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

תוסיף לשאילתה למעלה

SELECT כיתה, COUNT(*), SUM(ציון) AS TotalMark, AVG(ציון) AS AverageMark, SUM(זמן) AS TotalTime
FROM TABLE
GROUP BY כיתה

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

SELECT T.כיתה, MAX(T.TotalMark)
FROM (
SELECT כיתה, COUNT(*), SUM(ציון) AS TotalMark, AVG(ציון) AS AverageMark, SUM(זמן) AS TotalTime
FROM TABLE
GROUP BY כיתה) AS T
GROUP BY T.כיתה

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

SELECT T.כיתה, MAX(T.TotalMark)
FROM (
SELECT כיתה, SUM(ציון) AS TotalMark
FROM TABLE
GROUP BY כיתה) AS T
GROUP BY T.כיתה

את הממצוע של הנקודות של כל קבוצה

זה לא מה שכבר מחושב למעלה?

SELECT ..... AVG(ציון)

@גימפל
אם כך זה קל יחסית,
כדי לבחור את השורות הזוגיות, נשתמש במספר השורה הנוכחית ונכפיל ב-2. צריך רק לשים לב שאם יש שורת כותרת אז יש היסט של 1 ולכן נפחית 1 ממספר השורה. עכשיו יש לנו את מספר השורה, והעמודה היא קבועה, למשל 1, פשוט נשלוף את ערך התא על ידי שימוש ב INDIRECT ו-ADDRESS:

אתה בטח יכול לחשב מה הנוסחה בעמודה הבאה (בתא הראשון)

=INDIRECT(ADDRESS((ROW(C2)-1)*2,1))

שים לב ששם הפחתתי 1 לפני ההכפלה

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

@WWW מייבאים לתוך התוכנה מגיטהאב, זה מגדיר את פלטת הצבעים ל-VS2019 Dark

שלום לכולם,
כידוע, מיקרוסופט מזניחה כבר שנים את עורך ה-VBA ולא הכניסה בממשק שלו שום שדרוגים (אולי היא מצפה מאיתנו להזניח אותו גם?)
למרות העובדה שגם היום ישנם הרבה שמשתמשים בו כדי להוסיף פונקציונאליות קוד ליישומי אופיס - העורך עדיין תקוע בעיצוב מלפני 10 שנים+! באפשרויות של הגדרות הצבעים ישנם רק 16 צבעים לבחירה, ואין אפשרות להוסיף צבעים אחרים (אוף!)

ובתור אחד שעובד מאסיבית באקסס (רח"ל ) הדבר מפריע לי מאד...
מצאתי פרוייקט בגיטהב שמאפשר להפוך את המראה המזעזע של עורך ה-VBA באופיס למראה יותר מודרני, בדומה לערכת הנושא הכהה של VS, על ידי עריכת פלטת הצבעים המקורית שנמצאת בקובץ VBA7.1.DLL. אחרי ששיחקתי עם התוכנה קצת, יצרתי ערכת נושא שמתאימה ל-VS2019 פחות או יותר, להלן דוגמא:

וזה אכן שיפור גדול, אבל הלכתי עוד צעד קדימה והחלפתי את הפונט לפונט Consolas גודל 13 כמו ב-VS, אבל אז כל העברית הפכה לג'יבריש:

לאחר התקנת הפונט הערוך ConsolasH.ttf שמכיר בעברית כשפה מקובלת - הגעתי אל המנוחה והנחלה:

למי שרוצה לנסות, מצ"ב קובץ ערכת נושא שיצרתי: VS2019 Dark.xml
שימו לב שצריך גם לשנות קצת את הבחירות בעורך ה-VBE, מי שרוצה פתרון קל, מצ"ב קובץ Reg (על אחריותכם) שמעדכן את הבחירה של הצבעים וגם את הפונט: VBE 7.0 Color Settings.reg

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

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

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

@אוריי אמר בדיון המשך: VLOOKUP מול INDEX-MATCH באקסל:

@OdedDvir ברור שכוונתי הוא שמציינים התאמה מדוייקת.

הבעיה מתחילה כששוכחים

וזה הרבה יותר קל ופשוט משילוב נוסחאות ובמיוחד למשתמש הפשוט... מאשר לחבר 2 נוסחאות ולא להתבלבל.

אני מסכים, ודעבד כמר עבד

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

הנושא הועלה בקצרה כאן אך לא היתה הכרעה בו.
כהמשך למה ש @אוריי אמר בלוג טריוויה בימות המשיח:

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

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

גם בנושא ביצועים יש שיפור מה לשימוש ב INDEX-MATCH על פני VLOOKUP, כי אם עמודת החיפוש ועמודת התוצאה רחוקות זו מזו - VLOOKUP מעבדת את כל העמודות שבאמצע, ואילו INDEX-MATCH רק את שתי העמודות (החיפוש והתוצאה).
עיין כאן כמה מבחני השוואת מהירות מעניינים (לפעמים ההבדל בזמן החישוב הוא במאות אחוזים!!)

יש עוד כמה יתרונות, להמשך קריאה בנושא עיין כאן או חפש בגוגל...