Calc, libreoffice Commands

Links

• Mudar locale do calc

Converter planilha para csv sem abrir

• Para converter todas as planilhas para vários csvs olhe aqui

libreoffice --headless --convert-to csv --outdir ./somedir *.xls

Quebrar linha automaticamente

To wrap text within a cell, or merged set of cells:
Select a cell or group of cells.
Right-click the selected area and go to Format cells..., or select Format - Cells from the main menu
Click on the Alignment tab.
Check the Wrap text automatically (in OpenOffice.org 1.1.x: Automatic line break)
Click OK.
Select 'Optimal Row Height...' from the context menu. Now the contents of the cell will be wrapped to fit the cell.

Pegar o número da linha

ROW();

Estilizar a tabela como zebrada

• Selecionar o conteudo da tabela

• Ir em FORMAT > CONDITIONAL FORMAT > CONDITION

• Criar um condição do tipo fórmula

• Colocar a seguinte fórmula

    MOD(ROW(); 2) = 0
  

Congelar uma linha

• Selecione uma linha abaixo da linha que deseja freezar
• Selecione Window > Freeze

Referenciar outra tabela

=Sheet1.B1:Sheet1.B1

Flagear duplicados

=IF(COUNTIF($A$1:$A$9999,A1)>1,"Duplicate","")

ou se precisar de ver mais de uma coluna

=IF(COUNTIFS($A$1:$A$9999,A1, $B$1:$B$9999,B1) > 1,"Duplicate","")

Calcular apenas as linhas que foram filtradas

SUBTOTAL Calculates subtotals. If a range already contains subtotals, these are not used for further calculations. Use this function with the AutoFilters to take only the filtered records into account. Syntax

SUBTOTAL(Function; Range)
Function is a number that stands for one of the following functions:
Function index
Function
1     AVERAGE
2     COUNT
3     COUNTA
4     MAX
5     MIN
6     PRODUCT
7     STDEV
8     STDEVP
9     SUM
10    VAR
11    VARP

Range is the range whose cells are included. Example You have a table in the cell range A1:B5 containing cities in column A and accompanying figures in column B. You have used an AutoFilter so that you only see rows containing the city Hamburg. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows. In this case the correct formula would be:

=SUBTOTAL(9;B2:B5)

Como fazer group by

1. Selecione as colunas que tem o identificador e o valor

• 

1. Vá em Data -> Pivot Table -> Create -> Current Selection -> OK

• 

1. Arraste da coluna Available Fields para Row Fields os campos que são os identificadores
2. Arraste para a coluna Data Fields os campos que serão somados

• 

1. Clique em OK

• 

Esconder texto com overflow

A workaround here:

1. Select the cell → format cells → alignment → Properties-> Tick Wrap text automatically, then you’ll see
2. change the height of the cell to the default value (clicando no direto do numero da linha), then you’ll see
3. and go to format cells → alignment → text alignment → vertical → set to ‘Top’
4. The long text actually wrapped into multi-lines, and we can adjust the cell’s height to “hide” below lines.

Keywords

calc commands, libre office, libreoffice, calc

Softwares OpenSource Angular demos

Comments