Automatické označenie najvyššej hodnoty za čas. interval

IRate7 · 30. 6. 2021 14:15:49

Dobrý deň,

pri mojej aktuálnej metóde zberu dát a následného výpočtu aritmetického priemeru z nich mám problémy keďže
manuálne je to pri kvante dát časove neefektívne.
Potreboval by som zautomatizovať celý proces, čiže označenie/mark čo najväčšej hodnoty zo súboru viacerých hodnôt za daný časový úsek, a vzhľadom nato že dátum, napr. 02/07/2020 pokrýva viacero riadkov/rows a som začiatočník
neviem nájsť vhodný spôsob.

Ďakujem za prípadnú pomoc

Vid. screen:

Screen

ludviktrnka · 30. 6. 2021 14:39:16

Do dalšího sloupce (asi C) bych umístil tento vzorec =IF(MAXIFS($B$2:$B$1000;$A$2:$A$1000;A2)=B2;"X";"") (roztáhnout od řádku 2 do konce, pokud je řádků více než tisíc, tak změnit konec intervalu). Výsledkem pak bude písmeno X v místě nejvyšší hodnoty daného dne.

ludviktrnka · 30. 6. 2021 14:48:31

Případně do podmíněného formátování zapsat obdobně "vzorec je:" MAXIFS($B$2:$B$1000;$A$2:$A$1000;A2)=B2 a položit to na interval B2:B1000 a vybrat nějaký viditelný formát. Pokud se ale s daty aktivně pracuje (kopírování, vkládání atd.) je lepší používat ten prvně uvedený způsob, neboť podmíněné formátování se různými operacemi snadno rozbije.

ludviktrnka · 30. 6. 2021 15:15:57

Pokud tedy potřebujete průměr z těchto největších hodnot pak to bude ještě trochu dále: ve sloupci C bude tenhle vzorec: =IF(MAXIFS($B$2:$B$1000;$A$2:$A$1000;A2)=B2;MAXIFS($B$2:$B$1000;$A$2:$A$1000;A2);"") (roztáhnout) a někde bokem pak už bude vzorec: =AVERAGEIF(A2:A1000;"<>0";C2:C1000)

IRate7 · 30. 6. 2021 16:45:54

Ďakujem krásne za odpoveď, chcel by som sa ešte spýtať, ak tomu správne chápem tak daný vzorec mám vložiť do stĺpca
vedľa a pokiaľ je riadkov viac ako 1000 zmeniť interval napr. na B2:B1500?
Nesprávne som odfotil formát dátumu a času (vid. screen), nevadí že tam je formát UTC a sú tam aj menšie jednotky?

Screen

ludviktrnka · 30. 6. 2021 16:59:22

To vadí. Funkce počítá s tím že ve sloupci A jsou pouze datumy a že jsou tedy stejné. Rozdílné časy z toho dělají pokaždé jinou hodnotu. Dalo by se to řešit, ale navíc to vypadá, že datum s časem jsou zde ve formátu textu a nikli čísla, to je opravdu problém ...

ludviktrnka · 30. 6. 2021 17:11:20

Budete muset přidat další sloupec se vorcem: =DATE(VALUE(LEFT(A2;4));VALUE(MID(A2;6;2));VALUE(MID(A2;9;2))) tím tam dostanete hodnotu data daného dne a tento sloupec pak bude zdrojem pro srovnání dat (pvůdodně sloupec A), takže pokud data budou ve sloupci D, pak vorec pro denní maxima bude =IF(MAXIFS($B$2:$B$1000;$D$2:$D$1000;D2)=B2;MAXIFS($B$2:$B$1000;$D$2:$D$1000;D2);"")

edit: funguje to i na text takže ve sloupci D stačí vzorec =LEFT(A2;10)

Editoval ludviktrnka (30. 6. 2021 17:31:57)

kamlan · 30. 6. 2021 21:14:35

Můžete to zkusit i následujícím makrem. Makro porovnává datumy a podle toho když jde o stejné datum, tak zjistí maximum z rozsahu řádků se stejným datem. Řádek s tím maximem si přidá do rozsahu značených buněk který označí najednou až na konci.

Pro porovnání datumů se předpokládá, že datum je v buňce ve stylu YYYY/DD/MM HH:MM:SS blablabla kde datum a čas jsou odděleny mezerou, takže pro porovnání se řetězec rozdělí podle mezery a bude se porovnávat jen první část čili YYYY/DD/MM.

Předpokládá se že sloupec s čísly je hned vedle sloupce s datumy, a že od řádku kde začíná první datum (konstanta cDatumy) do posledního použitého řádku jsou jen datumy&hodnoty. Ten poslední použitý řádek si makro zjistí samo pomocí metody goToEndOfUsedArea.

Konstanta cBunkaPrumer je adresa buňky kam má makro zapsat ten průměr maxim.

Sub udelatMultivyber 'udělá multivýběr v druhém sloupci podle podmínky
	const cDatumy=0, cRadekPrvni=1 'číslo sloupce s datumem (A=0, B=1 atd.); řádek od kterého zjišťuji hodnoty (první řádek=1, druhý=2 atd.)
	const cOddel=" " 'oddělovač pro split() datumu - počítáno s formátem datumu "YYYY/DD/MM HH:MM:SS" kde datum a čas jsou odděleny mezerou, takže pro porovnání se řetězec rozdělí podle mezery a bude se brát jen první část čili datum
	const cBunkaPrumer="D1" 'buňka do které se umístí průměr maximálních hodnot

	dim oDoc as object, oSheet as object, oCur as object, i&, oRange as object, oRanges as object, oRangeAddress as new com.sun.star.table.CellRangeAddress, data(), p1(), p2(), max as double, i2&, s1$, s2$, a as double, prumer as double, oBunkaPrumer as object
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet 'aktivní list
	oRanges=oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") 'rozsahy vybraných buněk které budu označovat
	with oRangeAddress 'adresa přidávaného rozsahu
		.Sheet=oSheet.RangeAddress.Sheet
		.StartColumn=cDatumy+1
		.EndColumn=cDatumy+1
	end with
	oCur=oSheet.createCursor
	oCur.goToEndOfUsedArea(false) 'kurzor na konec oblasti použitých buněk
	oRange=oSheet.getCellRangeByPosition(cDatumy, cRadekPrvni-1, cDatumy+1, oCur.RangeAddress.EndRow) 'buňky pro dané dva sloupce
	data=oRange.getDataArray() 'pole s hodnotami z buněk
	max=data(0)(1) 'první maximální hodnota
	i2=0 'řádek ve kterém je maximální hodnota z jednoho rozsahu datumů
	for i=lbound(data) to ubound(data)-1 'procházet od prvního do předposledního řádku
		p1=split(data(i)(0), cOddel) 'první datum
		p2=split(data(i+1)(0), cOddel) 'následující datum
		s1=p1(0) : s2=p2(0) 'řetězce datumů pro porovnání
		if s1<>s2 then 'jde o řádky s jiným datumem takže přidat číslo řádku maxima z předchozího rozsahu
			with oRangeAddress 'adresa přidávaného rozsahu
				.StartRow=i2
				.EndRow=i2
			end with
			oRanges.addRangeAddress(oRangeAddress,true) 'přidat rozsah
			max=data(i+1)(1)
			i2=i+1
		else 'jde o řádky se stejným datumem, takže porovnat aktuální hodnotu s maximem
			a=data(i+1)(1) 'aktuální hodnota
			if a>max then 'je aktuální hodnota větší než maximum v rozsahu
				max=data(i+1)(1)
				i2=i+1 'zapamatovat si číslo řádku nového maxima
			end if
		end if
	next i
	i=ubound(data) 'kontrola posledního řádku
	p2=split(data(i)(0), cOddel) 'poslední datum
	s2=p2(0)
	if s1<>s2 then 'poslední řádek je samostatné jiné datum a nikoliv rozsah datumů
		with oRangeAddress 'adresa přidávaného rozsahu
			.StartRow=i
			.EndRow=i
		end with
	else 'poslední řádek je součást rozsahu předchozích datumů, přidat tedy maximum
		with oRangeAddress
			.StartRow=i2
			.EndRow=i2
		end with	
	end if
	oRanges.addRangeAddress(oRangeAddress,true) 'přidat poslední maximum
	prumer=oRanges.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE) 'spočítá průměr z rozsahu buněk
	oBunkaPrumer=oSheet.getCellRangeByName(cBunkaPrumer)
	oBunkaPrumer.String="Průměr: " & prumer 'umístit průměr do buňky
	oDoc.CurrentController.Select(oRanges) 'označit multivýběr
End Sub

Editoval kamlan (30. 6. 2021 21:15:51)

IRate7 · 5. 7. 2021 01:57:09

Makro funguje bez problémov a slúži svojmu účelu, ďakujem za váš čas a energiu ktorú ste venovali môjmu príspevku

Makro

Chcel by som sa ešte informovať, je možné odstrániť hromadným príkazom čas vo formáte HH:MM:SS zo stĺpcov alebo riadkov? Viem nahradiť označenie "UTC" (CTRL + H") ale neviem ako mám hromadne nahradiť formát kde hodnoty nie sú identické alebo ich nejakým spôsobom hromadne odstrániť. Potrebujem len dátum (Y/D/M , dodatočný formát- H:M:S mi tam prekáža.

Makro

Potrebujem to odstrániť aj z dôvodu že aj napriek tomu že konšt.: const cOddel="YYYY/DD/MM HH:MM:SS" mám v tejto forme, makro to nejako pomýli, vid. screen, preto som musel formát HH:MM:SS odstraňovať manuálne aby makro pracovalo správne.

Ďakujem za prípadnú odpoveď

neutr · 5. 7. 2021 07:03:34

K odstranění datumů s formátem času použijte toto :

 
Sub UpravDatum 
Dim oDoc
Dim Rok, Mes, Den as long
oDoc = ThisComponent
oSheet = oDoc.CurrentController.ActiveSheet
oTargetCell = oSheet.getCellByPosition(0, 0) 
If IsNull(oTargetCell) then
GetLastRow =oTargetCell.getRangeAddress.EndRow
Else 
oCursor = oSheet.createCursorByRange(oTargetCell) 
oCursor.gotoEnd 
LR = oCursor.getRangeAddress.EndRow 
End if 
For i = 0 To LR
sVal = LEFT(oSheet.getCellByPosition(0, i).String,10)
oSheet.getCellByPosition(0, i).String = sval
Rok = MID(sval,1,4)
Mes = MID(sval,6,2)
Den = MID(sval,9,2)
'Buď hodnota kterou převedeme na formát DD:MM:YYYY (= formát 37), nebo D:M:YYYY (= formát 30)
'__________________________________________________________
oSheet.getCellByPosition(0, i).value = DateSerial(Rok, Mes, Den)
oSheet.getCellByPosition(0, i).NumberFormat = 37 
'__________________________________________________________
'Nebo jako řetězec (zarovnáno doleva), který ovšem Calc čte jako číslo
'oSheet.getCellByPosition(0, i).string = DateSerial(Rok, Mes, Den)
Next i
End Sub

To co popisujete je ukázkou převodu z CSV (nebo jen TXT) a datum je v čistě textové formě kterou Calc nerozpoznal. Ani makro od kamlana to nepřečte správně. Není to jenom estetická vada a problém by s tím měly i vzorce. To vše stačilo řešit ukázkou dat - třeba jen 50 řádků.
Makro z textu vytvoří čitelný formát datumu, ale chvíli to trvá. Máte na výběr dva druhy - zarovnání doleva jako text, který už Calc přečte jako číslo, nebo přímo číslo - zarovnané doprava. Variantu která se Vám víc líbí si ponechte a tu druhou smažte. (Aktivace a deaktivace kódu je pomocí jednoduchých uvozovek.)

Kamlan by to mohl zabudovat přímo do svého kódu. Já bych jeho kód upravoval nerad - to se nedělá pokud je autor aktivní. Takže nejprve spusťíte kód výše a na něj můžete napojit před koncem makro od kamlana.

Lze to udělat i vzorci v pomocném sloupci - například jako =LEFT(A1;10), ale toto musí být ještě upraveno tak aby vzniklo čitelné číslo - to se dělá specializovanými funkcemi. Myslím, že nyní už o vzorce zájem nemáte, proto si vysvětlování detailů odpustím.

Přeji úspěch

PS : pokud byste potřeboval originál původního formátu YYYY/MM/DD, přeformátujte buňky sloupce s datumem z volby FORMÁT > BUŇKY > ČÍSLA > DATUM a v kolonce formátovací kód zadejte YYYY/MM/DD.

Editoval neutr (5. 7. 2021 07:17:28)

kamlan · 5. 7. 2021 10:55:19

Ta konstanta cOddel je pouze oddělovač (mezera) pro fci split() :-) -> je to popsáno v komentáři k ní v makru. Když do ní dáte něco jiného než mezeru, tak vám to samozřejmě řetězec nerozdělí správně. Nicméně v dalším makru už je to odstraněno.

Na ty datumy můžete použít i Najít&Nahradit. Označíte třeba sloupec A, pak dáte Ctrl+H. Do Najít dáte (\d{4}/\d{2}/\d{2}).+ (odpovídá stylu datumu YYYY/DD/MM) a do Nahradit dáte $1. K tomu zaškrtenete Pouze v současném výběru a Regulární výrazy.

Nebo se to dá udělat třeba Najít: (\d{4})/(\d{2})/(\d{2}).+ a do Nahradit: $2.$3.$1 -> to vám datum z YYYY/DD/MM blabla zapíše jako DD.MM.YYYY. Takto jsem to dal i do začátku makra. Kdyžtak budete chtít zachovat YYYY/DD/MM tak si předělejte řádky .SearchString a .ReplaceString na to co je uvedeno v odstavci výše.

Případně můžete změnit i řádek oRange.NumberFormat=37 (třeba na 30 jak má v makru neutr).

Ty datumy to nahrazuje v celém listu (je to nejrychlejší).

Editoval kamlan (5. 7. 2021 12:28:56)

kamlan · 5. 7. 2021 12:30:45

Opravené makro (to v předchozím příspěvku jsem smazal, bylo zkopírované ze starší vývojové verze)

Sub udelatMultivyber 'udělá multivýběr v druhém sloupci podle podmínky
	const cDatumy=0, cRadekPrvni=1 'číslo sloupce s datumem (A=0, B=1 atd.); řádek od kterého zjišťuji hodnoty (první řádek=1, druhý=2 atd.)
	const cBunkaPrumer="D1" 'buňka do které se umístí průměr maximálních hodnot

	dim oDoc as object, oSheet as object, oCur as object, i&, oCell as object,  oRange as object, oRanges as object, oRangeAddress as new com.sun.star.table.CellRangeAddress, data(), max as double, i2&, s1$, s2$, a as double, prumer as double, oBunkaPrumer as object
	dim oNajit as object, oNahr as object
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet 'aktivní list
	rem nahrazení datumů -> z formátu 'YYYY/DD/MM blabla' předělá do DD.MM.YYYY
	oNajit=oSheet.createReplaceDescriptor 'nahrazení datumů
	with oNajit
		.SearchString="(\d{4})/(\d{2})/(\d{2}).+" 'co najít
		.ReplaceString="$2.$3.$1" 'čím nahradit
		.SearchRegularExpression=true 'reguláry
	end with
	oSheet.replaceAll(oNajit) 'nahradit všude v listu
	rem formát buněk ve sloupci jako datum
	oCell=oSheet.getCellByPosition(cDatumy, cRadekPrvni-1) 'první buňka sloupce s datumy
	oCur=oSheet.createCursorByRange(oCell) 
	oCur.gotoEnd(false) 'na poslední buňku ve sloupci s datumy
	oRange=oSheet.getCellRangeByPosition(cDatumy, cRadekPrvni-1, cDatumy, oCur.RangeAddress.EndRow) 'buňky pro dané dva sloupce
	oRange.NumberFormat=37 'formát buněk DD.MM.YYYY
	rem nalezení maxim
	oRanges=oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") 'rozsahy vybraných buněk které budu označovat
	with oRangeAddress 'adresa přidávaného rozsahu
		.Sheet=oSheet.RangeAddress.Sheet
		.StartColumn=cDatumy+1
		.EndColumn=cDatumy+1
	end with
	oRange=oSheet.getCellRangeByPosition(cDatumy, cRadekPrvni-1, cDatumy+1, oCur.RangeAddress.EndRow) 'buňky pro dané dva sloupce
	data=oRange.getDataArray() 'pole s hodnotami z buněk
	max=data(0)(1) 'první maximální hodnota
	i2=0 'řádek ve kterém je maximální hodnota z jednoho rozsahu datumů
	for i=lbound(data) to ubound(data)-1 'procházet od prvního do předposledního řádku
		s1=data(i)(0) 'první datum
		s2=data(i+1)(0) 'následující datum
		if s1<>s2 then 'jde o řádky s jiným datumem takže přidat číslo řádku maxima z předchozího rozsahu
			with oRangeAddress 'adresa přidávaného rozsahu
				.StartRow=i2
				.EndRow=i2
			end with
			oRanges.addRangeAddress(oRangeAddress,true) 'přidat rozsah
			max=data(i+1)(1)
			i2=i+1
		else 'jde o řádky se stejným datumem, takže porovnat aktuální hodnotu s maximem
			a=data(i+1)(1) 'aktuální hodnota
			if a>max then 'je aktuální hodnota větší než maximum v rozsahu
				max=data(i+1)(1)
				i2=i+1 'zapamatovat si číslo řádku nového maxima
			end if
		end if
	next i
	i=ubound(data) 'kontrola posledního řádku
	s2=data(i)(0) 'poslední datum
	if s1<>s2 then 'poslední řádek je samostatné jiné datum a nikoliv rozsah datumů
		with oRangeAddress 'adresa přidávaného rozsahu
			.StartRow=i
			.EndRow=i
		end with
	else 'poslední řádek je součást rozsahu předchozích datumů, přidat tedy maximum
		with oRangeAddress
			.StartRow=i2
			.EndRow=i2
		end with	
	end if
	oRanges.addRangeAddress(oRangeAddress,true) 'přidat poslední maximum
	prumer=oRanges.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE) 'spočítá průměr z rozsahu buněk
	oBunkaPrumer=oSheet.getCellRangeByName(cBunkaPrumer)
	oBunkaPrumer.String="Průměr: " & prumer 'umístit průměr do buňky
	oDoc.CurrentController.Select(oRanges) 'označit multivýběr
End Sub

Editoval kamlan (5. 7. 2021 12:31:09)

IRate7 · 5. 7. 2021 16:19:45

Ďakujem pekne za všetky odpovede. Ku testovaniu nového kódu som sa ešte nedostal ale pri označení stĺpca v ktorom sa nachádza dátum som dal CTRL + H a podľa inštrukcii som použil (\d{4}/\d{2}/\d{2}).+ = $1. Dátum sa pekne upravil len do podoby YYYY/DD/MM a následne makro vedelo prečítať dátumy správne a tak vybrať len jeden maximálny priemer. Ďakujem všetkým ktorí sa zapojili do diskusie a pomohli mi, prajem pekný zvyšok dňa

IRate7 · 5. 7. 2021 16:39:29

Zdravím, ešte by som mal poslednú otázku.

Pri počítaní priemeru v mínusových hodnotách (vid. screen) za mi zaznačia automaticky najnižšie hodnoty (chápem že ich makro vníma ako najvyššie), mojim cieľom je však zozbierať čo najväčšie hodnoty aj v zápornej podobe a následne z nich vypočítať a. priemer. Je možné nejako upraviť pôvodné makro? Moja predstava je urobiť kópiu ktorú by som editoval aj pre záporné hodnoty, následne by som pre každý typ hodnôt (+-) použil inú verziu/makro. Ďakujem

Min. pr. pri zap. hod.

neutr · 5. 7. 2021 17:23:36

IRate7 napsal(a)

Zdravím, ešte by som mal poslednú otázku.
Pri počítaní priemeru v mínusových hodnotách (vid. screen) za mi zaznačia automaticky najnižšie hodnoty (chápem že ich makro vníma ako najvyššie), mojim cieľom je však zozbierať čo najväčšie hodnoty aj v zápornej podobe a následne z nich vypočítať a. priemer. Je možné nejako upraviť pôvodné makro? Moja predstava je urobiť kópiu ktorú by som editoval aj pre záporné hodnoty, následne by som pre každý typ hodnôt (+-) použil inú verziu/makro. Ďakujem

Neexistuje náhodou ještě možnost kombinovaných dat? Tedy několik kladných a několik záporných ve stejném dni. Co potom?

kamlan · 5. 7. 2021 17:25:37

Na změnu pro záporné hodnoty vám stačí změnit řádek if a>max then na if a<max then (případně si tam u toho řádku změnit i komentář: 'je aktuální hodnota menší než minimum v rozsahu).

Pokud budete mít všechny hodnoty buď kladné, nebo všechny hodnoty jen záporné, tak by šlo makro upravit aby to rozlišilo automaticky -> zjistit jestli jde o kladné či záporné hodnoty by šlo třeba tak, že by se podívalo do první buňky a dle kladného/záporného čísla by tedy zjišťovalo buď maxima nebo minima. V případě nuly v buňce by se koukalo na číslo v další buňce dokud by nenarazilo na kladnou nebo zápornou hodnotu a dle toho by to bylo.

Pokud byste to však měl pomíchané a tedy měl kladné i záporné hodnoty v buňkách, tak by se průměr měl počítat z absolutních hodnot jako kdyby šlo jen o kladná čísla? To by asi byl nesmysl. Příklad hodnoty: -10, +6; průměr (|-10|+6)/2 = 16/2 = 8.

Nebo byste chtěl označit kladná maxima a záporná minima a průměr spočítat z kladných i záporných hodnot? Příklad: -10, +6; průměr (-10+6)/2 = -2.

Otázkou i je co by mělo označit pokud by kladné i záporné hodnoty byly u jednoho datumu? Kladné maximum i záporné minimum? Nebo největší absolutní hodnotu?

Jinak zkuste i to poslední makro, to vám automaticky převede ty datumy na DD.MM.YYYY. Ušetříte si jedno Ctrl+H :-). Nebo to lze z toho makra vyřadit :-).

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 30. 6. 2021 14:15:49

Automatické označenie najvyššej hodnoty za čas. interval

#2 30. 6. 2021 14:39:16

Re: Automatické označenie najvyššej hodnoty za čas. interval

#3 30. 6. 2021 14:48:31

Re: Automatické označenie najvyššej hodnoty za čas. interval

#4 30. 6. 2021 15:15:57

Re: Automatické označenie najvyššej hodnoty za čas. interval

#5 30. 6. 2021 16:45:54

Re: Automatické označenie najvyššej hodnoty za čas. interval

#6 30. 6. 2021 16:59:22

Re: Automatické označenie najvyššej hodnoty za čas. interval

#7 30. 6. 2021 17:11:20

Re: Automatické označenie najvyššej hodnoty za čas. interval

#8 30. 6. 2021 21:14:35

Re: Automatické označenie najvyššej hodnoty za čas. interval

#9 5. 7. 2021 01:57:09

Re: Automatické označenie najvyššej hodnoty za čas. interval

#10 5. 7. 2021 07:03:34

Re: Automatické označenie najvyššej hodnoty za čas. interval

#11 5. 7. 2021 10:55:19

Re: Automatické označenie najvyššej hodnoty za čas. interval

#12 5. 7. 2021 12:30:45

Re: Automatické označenie najvyššej hodnoty za čas. interval

#13 5. 7. 2021 16:19:45

Re: Automatické označenie najvyššej hodnoty za čas. interval

#14 5. 7. 2021 16:39:29

Re: Automatické označenie najvyššej hodnoty za čas. interval

#15 5. 7. 2021 17:23:36

Re: Automatické označenie najvyššej hodnoty za čas. interval

#16 5. 7. 2021 17:25:37

Re: Automatické označenie najvyššej hodnoty za čas. interval

Zápatí