Função SubTotal em Excel

felipeds · Setembro 27, 2011

Pessoal...

Gostaria de pedir a ajuda de vocês em relação à impressão em Excel de um relatório.

Acredito que muitos conhecem a função "Subtotal" no Excel, onde por exemplo acumulando alguns dados em uma célula, eu posso visualizá-los ou não através do botão "+" e "-".

Gostaria de saber se alguém ja usou a função Subtotal em Progress para gerar um relatório desta forma, apresentando as informãções compactadas por nome por exemplo, exibindo algum total acumulado de qualquer dado.

Por enquanto cheguei à seguinte sintaxe:
<nome_aplicação>:SELECTION:Subtotal(GroupBy,xlSum,TotalList,,,).

*Neste caso é necessário o uso da função "xlSum" e a definição de alguns parametros.
Aguardo sugestões...

Agradeço desde ja...
Felipe.

LFBuozi · Setembro 27, 2011

Aqui eu faço um subtotal até que meio complexo:

Código:

/* colunas de somatorias */
DEF VAR colunas-somadas AS INT EXTENT 3 INIT [12, 13, 14].

/* subtotal onde temos: (indice, [soma, media, etc], colunas de somatoria, não me lembro, não me lembro, não me lembro) */
chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):Subtotal(1, 1, colunas-somadas, FALSE, FALSE, FALSE).
chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):Subtotal(2, 1, colunas-somadas, FALSE, FALSE, FALSE).
chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):Subtotal(3, 1, colunas-somadas, FALSE, FALSE, FALSE).

ChPlanilha:Outline:ShowLevels(4).

Estou fazendo o subtotal em 4 níveis, para colunas 1, 2 e 3, onde estou somando as colunas 12, 13 e 14 e a última linha é o nível que vai exibir, neste caso estou abrindo até o último nível.

dougwill · Setembro 28, 2011

Código:

/*Ordenar*/
chWorkSheet:Range("A2:K" + string(y)):Sort( chWorkSheet:Range("A2:K" + string(y)), {&xlAscending}, , , , , , {&xlGuess}, , false, {&xlSortRows}, {&xlPinYin}, , , ). 

chWorkSheet:Range("A2:K" + string(y)):Select().

/*Insere a tabela*/
chExcelApplication:ActiveSheet:ListObjects:Add(, , ,):Name = "Tabela1".
/*Mostra totais*/
chExcelApplication:ActiveSheet:ListObjects("Tabela1"):ShowTotals = True.
/*Define as colunas*/
chExcelApplication:ActiveSheet:ListObjects("Tabela1"):ListColumns("Valor Contábil"):TotalsCalculation = 1.
chExcelApplication:ActiveSheet:ListObjects("Tabela1"):ListColumns("Valor Desp."):TotalsCalculation = 1.

Qualquer duvida posta ai...

felipeds · Setembro 29, 2011

Obrigado a todos pelas dicas...

dougwill, o seu exemplo funcionou acumulando os valores fazendo um subtotal, porém, não sei por ter feito o teste na versão 2003 do excel, mas o resultado apareceu acumulado, logo abaixo das células sem a "opção" dos botões "+" e "-" na lateral da planilha, entende, tornando possível vizualizar os componentes da célula acumulada.

LFBuozi, se possível, gostaria que apresentasse um exemplo mais completo, com algumas informações de tabela por exemplo.

Grato.
Felipe

LFBuozi · Setembro 29, 2011

Um exemplo completo para ver o funcionamento:

Código:

DEFINE VARIABLE chExcelApplication   AS COM-HANDLE.
DEFINE VARIABLE chPasta              AS COM-HANDLE.
DEFINE VARIABLE chPlanilha           AS COM-HANDLE.
DEFINE VARIABLE i-cont               AS INTEGER    NO-UNDO.
DEFINE VARIABLE i-cont-coluna AS INTEGER     NO-UNDO.
DEFINE VARIABLE i-cont-linha AS INTEGER     NO-UNDO.

CREATE 'excel.application' chExcelApplication.
chPasta     = chExcelApplication:Workbooks:ADD().
chPlanilha  = chExcelApplication:Sheets:ITEM(1).
chExcelApplication:VISIBLE = NO.

ASSIGN i-cont = 1
       i-cont-coluna = 1
       i-cont-linha = 1.

ASSIGN
    i-cont-coluna = 0
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Estab"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Ano/Mês"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Data"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Item"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Descrição"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Un"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Grupo estoque"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Emitente"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Série"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Docto"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Natureza"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Valor total NF"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Impostos"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Valor estoque"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Conta DB"
    i-cont-coluna = i-cont-coluna + 1
    chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = "Conta CR".

FOR EACH docum-est NO-LOCK WHERE docum-est.dt-trans >= 04/01/2011
                             AND docum-est.dt-trans <= 04/05/2011,
    EACH item-doc-est NO-LOCK OF docum-est
    BREAK BY docum-est.cod-estabel
          BY docum-est.dt-trans:
       
    FIND FIRST ITEM NO-LOCK OF item-doc-est NO-ERROR.
    ASSIGN
        i-cont-linha = i-cont-linha + 1
        i-cont-coluna = 0
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = docum-est.cod-estabel
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = STRING(YEAR(docum-est.dt-trans), "9999") + "/" + STRING(MONTH(docum-est.dt-trans), "99")
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = STRING(MONTH(docum-est.dt-trans)) + "/" + STRING(DAY(docum-est.dt-trans)) + "/" + STRING(YEAR(docum-est.dt-trans))
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = ITEM.it-codigo
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = ITEM.desc-item
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = ITEM.un
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = ITEM.ge-codigo
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = docum-est.cod-emitente
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = docum-est.serie-docto
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = docum-est.nro-docto
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = item-doc-est.nat-operacao
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = item-doc-est.preco-total[1] + item-doc-est.valor-ipi[1] + item-doc-est.despesas[1]
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = item-doc-est.valor-icm[1] + item-doc-est.valor-ipi[1] + item-doc-est.valor-pis + item-doc-est.vl-pis-subs + item-doc-est.val-cofins
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = (item-doc-est.preco-total[1] + item-doc-est.valor-ipi[1] + item-doc-est.despesas[1]) - (item-doc-est.valor-icm[1] + item-doc-est.valor-ipi[1] + item-doc-est.valor-pis + item-doc-est.vl-pis-subs + item-doc-est.val-cofins)
        i-cont-coluna = i-cont-coluna + 1
        chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = docum-est.ct-transit.

    FIND FIRST movto-estoq NO-LOCK WHERE movto-estoq.cod-estabel = docum-est.cod-estabel
                           AND movto-estoq.cod-emitente = item-doc-est.cod-emitente
                           AND movto-estoq.it-codigo = item-doc-est.it-codigo
                           AND movto-estoq.nro-docto = item-doc-est.nro-docto
                           AND movto-estoq.serie-docto = item-doc-est.serie-docto
                           AND movto-estoq.nat-operacao = item-doc-est.nat-operacao
                           AND movto-estoq.sequen-nf = item-doc-est.sequencia
                           AND movto-estoq.tipo-trans = 1 NO-ERROR.
    IF AVAIL movto-estoq THEN DO:
        ASSIGN
            i-cont-coluna = i-cont-coluna + 1
            chPlanilha:cells(i-cont-linha, i-cont-coluna):VALUE = movto-estoq.conta-saldo.
    END.
END.

ASSIGN i-cont-coluna = 0
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "@"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "dd/mm/aaaa"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "@"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "@"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "@"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = (IF SESSION:NUMERIC-FORMAT = "American" THEN "###,###,##0.00" ELSE "###.###.##0,00" )
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = (IF SESSION:NUMERIC-FORMAT = "American" THEN "###,###,##0.00" ELSE "###.###.##0,00" )
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = (IF SESSION:NUMERIC-FORMAT = "American" THEN "###,###,##0.00" ELSE "###.###.##0,00" )
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######################0"
       i-cont-coluna = i-cont-coluna + 1
       chPlanilha:COLUMNS(i-cont-coluna):numberformat = "######################0".

DEF VAR va AS INT EXTENT 3 INIT [12, 13, 14].

chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):Subtotal(1, 1, va, FALSE, FALSE, FALSE).
chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):Subtotal(2, 1, va, FALSE, FALSE, FALSE).
chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):Subtotal(3, 1, va, FALSE, FALSE, FALSE).

ChPlanilha:Outline:ShowLevels(4).

ASSIGN chPlanilha:range(chPlanilha:cells(1, 1), chPlanilha:cells(1, i-cont-coluna)):interior:colorindex = 46.
ASSIGN chPlanilha:range(chPlanilha:cells(1, 1), chPlanilha:cells(1, i-cont-coluna)):FONT:bold = YES.
ASSIGN chPlanilha:range(chPlanilha:cells(1, 1), chPlanilha:cells(1, i-cont-coluna)):FONT:italic = YES.
chPlanilha:range(chPlanilha:COLUMNS(1),chPlanilha:COLUMNS(i-cont-coluna)):entirecolumn:autofit.

ASSIGN chExcelApplication:DisplayAlerts = FALSE.
ASSIGN chExcelApplication:VISIBLE = YES.

RELEASE OBJECT chPlanilha.
RELEASE OBJECT chPasta.
RELEASE OBJECT chExcelApplication.

fernandocampos · Janeiro 8, 2013

Pessoal, eu gostaria de calcular o total para duas colunas porem so estou conseguindo com 1.
Alguem poderia me ajugar?

excelAppl:Cells:SELECT. /* precisa selecionar todas primeiro */
excelAppl:SELECTION:Subtotal(2, 1, 10, FALSE, FALSE, TRUE).

Eu sei q o parametro 3 indica qual coluna calcula o total, porem eu gostaria de calcular o total para as colunas 10, 11 e 12.

Tentei utilizar o codigo q esta nessa pagina porem esta dando erro.

Logar ou Criar uma Conta

Função SubTotal em Excel

felipeds Sem Pontuação

LFBuozi Membro Participativo

dougwill Sem Pontuação

felipeds Sem Pontuação

LFBuozi Membro Participativo

fernandocampos Sem Pontuação

Compartilhe esta Página