Fórum pro uživatele kancelářského balíku OpenOffice | LibreOffice
 

#1 12. 2. 2022 20:06:01

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 29

Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Dobrý den, mám ceník s cenami v řádcích:
Material  Jednotka Rate    Qty1  Cena1   Qty2   Cena2    Qty3   Cena3  Qty4    Cena4   ... Qty10  Cena10
mater_1   ks       1       10    16,00    30    15,00    100    14,0   1000     8,00
mater_2   ks       1000       50    335,00   500   95,00
mater_3   ks       10       100   1,00
Výsledkem by mělo být:
Material  Jednotka Rate    Qty  Cena
mater_1   ks       1       10    16,00
mater_1   ks       1       30    15,00
mater_1   ks       1       100   14,0
mater_1   ks       1       1000  8,00
atd.
Zatím to dělám ručně kopírováním neprázdných sloupců A-E do nového listu, následně mažu sloupce D-E, filtrem (sloupec D) zobrazuji neprázdné buňky a opět kopíruji A-E na poslední prázdný řádek do stejného listu.
Po zkopírování všech množstevních limitů data seřadím dle Material a Qty a je hotovo.
Časově je to cca 15 minut při 8 množstvích a cca 15 tis. řádcích.
Lze tento převod zrychlit?
Děkuji.

Editoval Eddie60 (3. 4. 2022 15:17:30)

Offline

#2 13. 2. 2022 10:57:43

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 670

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Pomocí vzorců to lze, ale nakonec té práce buda taky dost, a vzorců bude 150 000 řádků, takže pravděpodobně to bude padat. Takže podle mne je jediná rozumná cesta řešení pomocí jednoduchého makra. Nevím zda s tím mátě nějaké zkušenosti. Není problém to napsat, ale musíte jej pak umět aplikovat, spouštět atd.


LibreOffice 6.2.

Offline

#3 15. 2. 2022 10:46:12

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 29

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Určité zkušenosti s makry mám. Používal jsem k tomu jen "Zaznamenat makro" a následně jsem si k němu přiřadil kombinaci kláves.
Lze tento postup ošetřit jediným makrem?
1) zkopírovat z aktivního listu List1 do nového listu (např. "sloupce") buňky A1:E1 (záhlaví tabulky)
2) zkopírovat oblast A2:E(poslední neprázdná buňka) = Qty1 a Cena1 do listu "sloupce" na první volný řádek
3) vymazat sloupce D-E (tím se mi do sloupců D-E posune  = Qty1+1 a Cena1+1)
4) filtr na sloupci D "neprázdné"
5) opět 2) celkem 9x (po Qty10  Cena10)

Offline

#4 15. 2. 2022 12:27:43

kamlan
Člen
Registrace: 15. 9. 2016
Příspěvků: 368

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Potřebujete při kopírování dat zachovat formátování (tučnost, barvu písma atd.) nebo stačí jen zkopírovat hodnoty?


A chápu-li to správně tak výsledek v novém listu má být:

mater_1   ks       1       10    16,00
mater_1   ks       1       30    15,00
mater_1   ks       1       100   14,0
mater_1   ks       1       1000  8,00
mater_2   ks       1000       50    335,00
mater_2   ks       1000       500   95,00
mater_3   ks       10       100   1,00

Editoval kamlan (15. 2. 2022 13:38:13)

Offline

#5 15. 2. 2022 14:10:46

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 29

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Ano, ale včetně 1. řádku: Material  Jednotka Rate    Qty1  Cena1
Stačí mi zkopírovat pouze hodnoty.
Seřazení všech dat na závěr dle materiálu a množství jsem neuváděl.
To už mi nezabere tolik času.

Offline

#6 15. 2. 2022 16:54:36

kamlan
Člen
Registrace: 15. 9. 2016
Příspěvků: 368

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Tak můžete zkusit tohle makro, hlavičku to zkopíruje zformátovanou, data vkládá neformátovaně - udělá to najednou přes setDataArray -> algoritmus prostě pracuje s celým polem dat z výchozího listu.
Prázdné řádky nezapíše (resp. kontrola je na prázdné první tři slouce čili Materiál, Jednotka, Rate -> když budou tyto prázdné tak řádek vynechá). V řádku kontroluje všechny dvojice QtyX&CenaX, ale prázdné nezapíše. Na konci nastaví akorát na sloupec C typ buňky Měna, kdyžtak si změňte řádek oSloupec.NumberFormat=102.

zjednodušeno v dalším příspěvku

Editoval kamlan (15. 2. 2022 17:10:36)

Offline

#7 15. 2. 2022 17:11:00

kamlan
Člen
Registrace: 15. 9. 2016
Příspěvků: 368

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

takhle :-)

Sub kopirujCenyDoSloupcu
	const sList1="List1" 'zdrojový list
	const sList2="sloupce" 'název cílového listu
	const iParu=10 'počet párů Qty&Cena
	const iZac=3 'index sloupce kde je první Qty (indexy jsou od nuly, A=0, D=3)
	dim oDoc as object, oList1 as object, oRange1 as object, oList2 as object, sZahlavi$, iList2&, data as object, oRange2 as object, oCur1 as object, p1(), oRangeP as object, _
		iRadkuP&, i&, j&, k&, oRangeP2 as object, oSloupec as object
	oDoc=ThisComponent
	oList1=oDoc.Sheets.getByName(sList1)
	if oDoc.Sheets.hasByName(sList2) then 'když cílový list existuje
'		if 6=msgbox(sList2 & chr(13) &  "List již existuje, smazat?", 4) then 'dotaz zda-li smazat existující cílový list
			oDoc.Sheets.removeByName(sList2) 'tak jej smazat
'		else
'			exit sub
'		end if
	end if
	iList2=oList1.RangeAddress.Sheet+1 'index (pozice) cílového listu
	oDoc.Sheets.insertNewByName(sList2, iList2) 'vložit cílový list za výchozí list
	oList2=oDoc.Sheets(iList2)
	sZahlavi="A1:E1" 'adresa záhlaví tabulky
	oRange1=oList1.getCellRangeByName(sZahlavi) 'buňky záhlaví
	oDoc.CurrentController.Select(oRange1)
	data=oDoc.CurrentController.getTransferable 'kopírovat záhlaví
	oRange2=oList2.getCellRangeByName(sZahlavi) 'buňky záhlaví v cílovém listu
	oDoc.CurrentController.Select(oRange2)
	oDoc.CurrentController.insertTransferable(data) 'vložit záhlaví
	createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(oDoc.CurrentController.Frame, ".uno:Deselect", "", 0, array()) 'provést
	oCur1=oList1.createCursor()
	oCur1.goToEndOfUsedArea(false) 'v proměné je adresa posledního použitého řádku a sloupce
	oRangeP=oList1.getCellRangeByPosition(oRange1.RangeAddress.StartColumn, oRange1.RangeAddress.StartRow+1, oCur1.RangeAddress.StartColumn, oCur1.RangeAddress.StartRow)
	p1=oRangeP.getDataArray() 'data z výchozího listu
	iRadkuP=CLng((oCur1.RangeAddress.EndColumn-oRange1.RangeAddress.EndColumn+2)/2) 'maximum řádků z jednoho sloupce
	dim p2((ubound(p1)+1)*iRadkuP) 'pole pro setDataArray do cílového rozsahu
	for i=lbound(p1) to ubound(p1) 'projíždět data z výchozího listu po řádku
		if p1(i)(0)="" AND p1(i)(1)="" AND p1(i)(2)="" then 
			'sloupce Materiál, Jednotka, Rate jsou prázdné
		else 'nějaké údaje o materiálu jsou tak zapsat
			for k=iZac to iZac+iParu step 2 'další řádky dle toho jak jsou vyplněné dvojice Qty&Cena atd., projíždí všechny sloupce takže nějaké údaje mohou chybět (např. může chybět Qty5Cena5 ale je-li vyplněné Qty6Cena6 atd. tak už to zapíše)
				if p1(i)(k)<>"" OR p1(i)(k+1)<>"" then 'alespoň 1 hodnota z Qty&Cena je, tak zapsat
					p2(j)=array( p1(i)(0), p1(i)(1), p1(i)(2), p1(i)(k), p1(i)(k+1) )
					j=j+1
				end if
			next k
		end if
	next i
	redim preserve p2(j-1) 'cílové pole jen pro tolik řádků kolik bylo vytvořeno
	oRangeP2=oList2.getCellRangeByPosition(oRange2.RangeAddress.StartColumn, oRange2.RangeAddress.StartRow+1, oRange2.RangeAddress.StartColumn+ubound(p2(0)), oRange2.RangeAddress.StartRow+j ) 'cílový rozsah
	oRangeP2.setDataArray(p2) 'vložit data do cílového rozsahu
	oSloupec=oList2.getCellRangeByPosition(oRange2.RangeAddress.EndColumn, oRange2.RangeAddress.StartRow+1, oRange2.RangeAddress.EndColumn,  oRange2.RangeAddress.StartRow+j)
	oSloupec.NumberFormat=102 '102=měna 1234,50 Kč; 2=číslo typu 1234,00
	rem setřídit data
	dim oDesc as object, aSortFields(1) as new com.sun.star.table.TableSortField
	oDesc=oRangeP2.createSortDescriptor
	oDesc(1).Value=false 'rozsah je bez hlavičky
	aSortFields(0).Field=0 'sloupec A jako první vzor pro třídění
	aSortFields(0).IsAscending=true
	aSortFields(1).Field=3 'sloupec D jako druhý vzor pro třídění
	aSortFields(1).IsAscending=true
	oDesc(3).Value=aSortFields()
	oRangeP2.sort(oDesc) 'setřídit	
End Sub

Offline

#8 15. 2. 2022 18:03:38

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 29

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Tak to je skvělé. Makro je velmi rychlé.
Ještě jedna drobnost.
Nedaří se mi v nápovědě dohledat NumberFormat pro €/EUR (125,55 € / 125,55 EUR)
Sloupec.NumberFormat=102 '102=měna 1234,50 Kč

Offline

#9 15. 2. 2022 19:23:22

kamlan
Člen
Registrace: 15. 9. 2016
Příspěvků: 368

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Pro € se tam dá nastavit víc hodnot (slovenské euro, německé, italské atd.), slovenské bylo myslím 109. Nejsnažší je nastavit si požadovaný typ na jednu buňku a spustit následující makro které vypíše NumberFormat pro tu jednu buňku. V Nápovědě ty hodnoty myslím nejsou.

Sub vypisNumberFormat
	dim oDoc as object, oSel as object
	oDoc=ThisComponent
	oSel=oDoc.CurrentController.Selection
	if oSel.supportsService("com.sun.star.sheet.SheetCell") then 'vybrána 1 buňka
		msgbox("NumberFormat=" & oSel.NumberFormat)
	else
		msgbox("Vyber jen jednu buňku", 48)
	end if
End Sub

Editoval kamlan (15. 2. 2022 19:23:47)

Offline

#10 15. 2. 2022 20:28:07

Eddie60
Člen
Registrace: 5. 2. 2022
Příspěvků: 29

Re: Jak zkonvertovat ceník s cenami v řádcích do sloupců? - VYŘEŠENO

Děkuji za perfektní práci.

Offline

Zápatí