=LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), _startRow, lastRow - 27, _numRows, 28, campaign, CHOOSEROWS('Daily Report'!B:B, SEQUENCE(_numRows, 1, _startRow)), date, CHOOSEROWS('Daily Report'!C:C, SEQUENCE(_numRows, 1, _startRow)), impression,CHOOSEROWS('Daily Report'!E:E, SEQUENCE(_numRows, 1, _startRow)), click, CHOOSEROWS('Daily Report'!F:F, SEQUENCE(_numRows, 1, _startRow)), downloading,CHOOSEROWS('Daily Report'!G:G, SEQUENCE(_numRows, 1, _startRow)), downloaded,CHOOSEROWS('Daily Report'!H:H, SEQUENCE(_numRows, 1, _startRow)), install, CHOOSEROWS('Daily Report'!I:I, SEQUENCE(_numRows, 1, _startRow)), panel_cpi, ARRAYFORMULA(IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000, TRUE, 0 )), cost, MAP(install, panel_cpi, LAMBDA(ins, cpi, ins * cpi)), cvr, MAP(install, impression, LAMBDA(ins, imp, IFERROR(ins / imp, 0))), data, HSTACK(campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr), QUERY(data, "select *", 0) )
| =LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), _startRow, lastRow - 27, _numRows, 28, campaign, CHOOSEROWS('Daily Report'!B:B, SEQUENCE(_numRows, 1, _startRow)), date, CHOOSEROWS('Daily Report'!C:C, SEQUENCE(_numRows, 1, _startRow)), impression,CHOOSEROWS('Daily Report'!E:E, SEQUENCE(_numRows, 1, _startRow)), click, CHOOSEROWS('Daily Report'!F:F, SEQUENCE(_numRows, 1, _startRow)), downloading,CHOOSEROWS('Daily Report'!G:G, SEQUENCE(_numRows, 1, _startRow)), downloaded,CHOOSEROWS('Daily Report'!H:H, SEQUENCE(_numRows, 1, _startRow)), install, CHOOSEROWS('Daily Report'!I:I, SEQUENCE(_numRows, 1, _startRow)), panel_cpi, ARRAYFORMULA(IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000, TRUE, 0 )), cost, MAP(install, panel_cpi, LAMBDA(ins, cpi, ins * cpi)), cvr, MAP(install, impression, LAMBDA(ins, imp, IFERROR(ins / imp, 0))), data, HSTACK(campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr), QUERY(data, "select *", 0) ) | عالی |
| =LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), base, INDEX('Daily Report'!B:K, lastRow-27+SEQUENCE(28), ), campaign, INDEX(base,,1), date, INDEX(base,,2), impression, INDEX(base,,4), click, INDEX(base,,5), downloading, INDEX(base,,6), downloaded, INDEX(base,,7), install, INDEX(base,,8), panel_cpi, IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000 ), cost, install * panel_cpi, cpi, panel_cpi, cvr, IFERROR(install / impression, 0), data, { campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr }, QUERY(data, "select *", 0) ) |
| =SUMIF('Daily Report'!$B:$B, $B4, 'Daily Report'!O:O) | =SUMIF('Daily Report'!B:B, $B4, 'Daily Report'!O:O) |
| ستون B را هم ثابت کن | به سمت راست درگ میکنی، گوگل شیت ستونهای بدون $ را تغییر میدهد: B:B → C:C ❌ |
برای ثابت کردن سریع یک مرجع، نشانگر را روی B:B ببر و کلید F4 را بزن | |
(در بعضی لپتاپها Fn + F4) |