入黎免費幫你砌excel表 / 教你formula (8)

浮浪者 2022-8-23 18:32:43 先寫個filter去抽個x軸，e.g. filter晒cooimn 1係AA既 row出黎，然後xlookup 個y軸， lookup array係 cllumn 2, return column 3作為result
搞掂

PeterStrange 2022-8-23 18:35:37 版本呀

傅家俊 2022-8-23 21:10:39 無人理樓主

:^(

PeterStrange 2022-8-23 21:41:04 唔理咪唔好理
又唔係我要做

:^(

傅家俊 2022-8-23 21:50:51

:^(

迪風 2022-8-24 09:56:09 抱歉啱啱先睇到係2016

HAMHAM 2022-8-24 13:13:09

:^(

Google Apps script
請問array點樣filter between range?
例如 row[8] between 44806.5 and 44806.6?

:^(

HAMHAM 2022-8-24 13:17:16

:^(

同埋點樣getlastrow先岩?
我呢個getlastrow出1005
我想要6係lastrow

傅家俊 2022-8-24 16:50:50 好少用Google Sheet / Apps Script
啱啱睇過Sheet個getLastRow()同Range個getLastRow()係唔同

:^(

Sheet果個先係搵最後一行有content
Range果個純粹話你知最尾果行，連空白都計

:^(

但又無得好似VBA咁用End(xlUp)
如果你想check單一column，你要寫個loop逐行check

:^(

HAMHAM 2022-8-24 17:34:51 loop個performance好似好慢?

岩岩整到between range可以
filter(row => row[8] >5 && row[8] <10)

HAMHAM 2022-8-24 17:36:10 google apps script好似新手friendly
唔駛有server都可以做online野
跟住google form已經可以叫做一個見得人既front end唔駛set

HAMHAM 2022-8-25 15:00:04

:^(

google sheet
點樣set arrayforumla,可以用到max if?
我想整如果I2:L2呢4個數字是但一個大過20,H2就出"full"
唔用array好就整到
=arrayformula(if(F2:F="","",if(Max(I2:L2)<20,"Available","Full")))
應該都係呢個range出事

:^(

HAMHAM 2022-8-25 15:45:05 終於整到
但係仲複雜過if 3次
唔知邊樣個performance好D
=arrayformula(if(F2:F="","",if(IFERROR( 1 / ( 1 / DMAX( TRANSPOSE(I2:L), SEQUENCE(ROWS(I2:L)), TRANSPOSE( IFERROR( COLUMN(I2:L) / 0)) ) ) ) <20,"Available","Full")))

Patches 2022-8-25 16:19:45 想問點樣可以係2行column入面用formula去刪走duplicates?啫係好似張圖咁

:^(

PeterStrange 2022-8-25 16:41:52 點解咁想寫做array formula

PeterStrange 2022-8-25 16:51:10 Flatten 再unique?

HAMHAM 2022-8-25 16:53:04 因此左邊D data會不停增加row

PeterStrange 2022-8-25 17:20:26 excel既話subtotal + offset就做到
google個offset好似有啲唔同

:^(

蛋黃貓 2022-8-25 17:46:26

:^(

想請教下
有兩個column既日期
想知道每個column 某日子到某日子既總數有冇一條咁既formula

:^(

感謝

Patches 2022-8-25 17:55:40 剩係用excel 2016有嘅formula做唔做到？
同埋唔可以用remove duplicate個d 剩係可以用formula

:^(

諗到頭都爆

PeterStrange 2022-8-25 17:56:26 做到
不過你唔講我點知你2016呢

Patches 2022-8-25 17:58:09 同埋如果2column難搞d嘅我可以combine 左2個column先，再within一個column 咁做都ok, 但係我上網搵到嘅方法都會製造左好多行row+blank cell

:^(

Patches 2022-8-25 17:58:33 啱啱唔記得左講麻煩哂

:^(

PeterStrange 2022-8-25 18:01:03 pin埋都冇人理
唔講版本一律鳩答算

Patches 2022-8-25 18:05:31 sorry

:^(