Logo Hardware.com.br
Ivanovic
Ivanovic Novo Membro Registrado
9 Mensagens 4 Curtidas

Reverter matriz em Planilha de dados

#1 Por Ivanovic 12/01/2018 - 11:02
Bom dia gente,

Normalmente utilizamos a planilha de dados para criar uma matriz na tabela dinâmica. Existe alguma alguma forma de reverter uma matriz em planilha de dados?

Nota: minha matriz é imensa!(2281X382)

Planilha de dados seria desta forma...

Família Espécies Local
Fabaceae Abarema cochleata (Willd.) Barneby & J.W.Grimes DMA 22
Fabaceae Abarema cochleata (Willd.) Barneby & J.W.Grimes CAM 118
Acanthaceae Ruellia geminiflora Kunth VIG 1
Acanthaceae Ruellia geminiflora Kunth VIG 2
Acanthaceae Ruellia geminiflora Kunth VIG 3
Acanthaceae Ruellia geminiflora Kunth VIG 4
Acanthaceae Ruellia geminiflora Kunth VIG 5


A matriz é de presença (1) e ausência (0), então onde tem o número "1" iria ser registrado o local "HUM1" VIG3, CAM118 assim por diante


Família Espécie HUM1 HUM2 HUM3 HUM4 HUM5 HUM6 HUM7 HUM8 HUM9 HUM10
Fabaceae Abarema cochleata (Willd.) Barneby & J.W.Grimes 0 0 0 0 0 0 0 0 0 0
Fabaceae Abarema sp 0 0 0 0 0 0 0 0 0 0
Melastomataceae Acisanthera crassipes (Naudin) Wurdack 0 0 0 0 0 0 0 0 0 0
Melastomataceae Acisanthera uniflora (Vahl) Gleason 0 0 0 0 0 0 0 0 0 0
Bignoniaceae Adenocalymma allamandiflorum (Bureau ex K.Schum.) L.G.Lohmann 0 0 0 0 0 0 0 0 0 0
Bignoniaceae Adenocalymma magnificum Mart. ex DC. 0 0 0 0 0 0 0 0 0 0
Lamiaceae Aegiphila integrifolia (Jacq.) B.D. Jacks. 0 0 0 0 0 0 0 0 0 0
Lamiaceae Aegiphila sellowiana Cham. 0 0 0 0 0 0 0 0 0 0
Fabaceae Aeschynomene brasiliana var. brasiliana 0 0 0 0 0 0 0 0 0 0
matriz planilha-de-dados reverter-matriz dados bom reverter planilha matriz utilizamos dia
Ivanovic
Ivanovic Novo Membro Registrado
9 Mensagens 4 Curtidas
#3 Por Ivanovic
12/01/2018 - 14:56
Olá esta é a matriz original.

Queria finalizar a planilha como no modelo em anexo.

Da última vez eu fiz manualmente, filtrando "espécie" por "espécie", mas descobri hoje que a matriz que me passaram tinha erros e me repassaram a matriz correta hoje. Gastei quase uma semana e não queria retornar a fazer manualmente de novo!

Anexos

matrz completa.xlsx
matrz completa.xlsx
modelo como seria.xlsx
manualmente.jpg
cvv
cvv Veterano Registrado
603 Mensagens 75 Curtidas
#4 Por cvv
12/01/2018 - 15:26
Tá, ok. Ficou um pouco mais claro para mim.

Última pergunta: o resultado é para ser outra planilha do Excel? Nós não estamos falando de conversão de formatos para PDF, CSV, etc?

VBA não é minha praia e possivelmente outra pessoa se disponibilizará a produzir um código que faça isso. Irei tentar algo com JScript.

(...)

Depois de um tempo o código saiu! E devo dizer que não saiu tão bom quanto o esperado, apenas bom o bastante, pois o programa me pareceu muitíssimo lento.

Conforme o EdsonBR no tópico original notou, acabei implementado uma simples validação e:
(Poaceae, Axonopus anceps (Mez) Hitchc., PAC38, 10)

Linha: 33, Coluna: 760, Valor: 10


Na célula acima há o valor 10. Qual deveria ser o valor real? Eu também tratei como 1 para efeito de teste final de conversão do script.

Não baixei o trabalho de EdsonBR e não sei se ele compartilhou um código geral e muito menos sei do desempenho do código dele. Pelos testes que fiz... meu script demora por volta de 40 min para validar + converter o código. O processo de validação demora por volta de 15 minutos no meu notebook, que é uma máquina modesta com um processador de 2010, um Core i3-380M de primeira geração e clock 2.5Ghz. Se o arquivo do Excel não passar no teste de validação, o script pára e exibe uma lista de erros.

Por volta de 40 minutos é muito tempo, eu sei. Mas tenho certeza que este tempo perto do tempo manual da realização desta mesma tarefa é irrisório.

Meu script são praticamente 2 arquivos (um .cmd e um .js) que devem ser colocados de preferência dentro de uma nova pasta com uma cópia da sua tabela. Se o arquivo do Excel passar pela validação um arquivo chamado ActivEXel.csv será gerado. Ele é um arquivo de texto puro com separação por ponto e vírgula. Este arquivo apesar de absurdamente simples é aberto no Excel automaticamente com tabulação correta. Caso seja o resultado que espera, basta mandar salvar como dentro do Excel para o formato xlsx. ( este arquivo está anexado a este comentário e conta com a alteração da célula com erro de 10 para 1 )

Para executar o script deve-se fechar todas janelas do Excel abertas, clicar em ActiveEXelBatch.cmd, seguir as poucas orientações que surgem na tela e aguardar.

ActiveEXelBatch.cmd
[code=DOS]@echo off & mode 85, 50
@echo.
@echo.
@set "_ext=xlsx"
@echo. Buscando arquivos com a extensao %_ext%.
@echo.
@echo. Este script esta configurado para escolher
@echo. automaticamente um arquivo. No caso o ultimo
@echo. arquivo em ordem alfabetica no diretorio da
@echo. execucao com a extensao %_ext%.
@echo.
@echo. Prefira mover os arquivos do script e o arquivo
@echo. do Excel para uma pasta exclusiva. Este eh o
@echo. metodo mais seguro.
@echo.
@echo. Por favor, feche todas as intancias do EXCEL agora!
@echo.
@for %%f in (*.%_ext%) do set "_src=%%f"
@echo. Arquivo encontrado: %_src%
@echo.
@echo. Se o arquivo encontrado for o arquivo esperado,
@echo. apenas de enter na pergunta abaixo. Caso
@echo. contrario, digite o nome completo com extensao.
@echo.
@set /P _src=Outro Arquivo? Qual :
@echo.
@echo.
@echo. O script possui dois passos: validacao e conversao.
@echo. Cada passo demorou por volta de 15 min em meu note.
@echo. Minha CPU: Intel Core i3-380M, 2.5Ghz (1st gen).
@echo.
@echo. Caso o arquivo nao passe na validacao, o script
@echo. sera encerrado, mostrando uma lista de erros.
@echo.
@echo. A validacao eh absurdamente simples e so procura
@echo. por valores diferentes de 0 e 1 pela tabela.
@echo.
@echo. Caso o arquivo passe na validacao a conversao eh
@echo. feita para um arquivo de texto puro em formato.csv.
@echo.
@echo. Arquivos .csv podem facilmente serem importados
@echo. pelo Excel. Porem, nao sao indicados para arquivos
@echo. que contenham ";" (ponto e virgula) no interior de
@echo. suas celulas pois estes sao utilizados como
@echo. separador tabular dentro do Excel.
@echo.
@echo. Por favor, feche todas as intancias do EXCEL agora!
@echo. Por favor, aguarde. Deve demorar de 30 a 45 min.
@echo. (ou seja umas duas musicas do Pink Floyd!)
@echo.
@echo.
@cscript.exe /nologo ActivExel.js "%~dp0" "%_src%"
@echo.
@echo.
@echo. Forcando o fechamento do Excel caso nao tenha
@echo. sido fechado durante a execucao do scrpit. E,
@echo. por fim, fechando o script em 10 segundos!
@echo.
@echo. Talvez me encontre em:
@echo. https://ofernandofilo.blogspot.com
@echo.
@echo. Se me encontrar, sinta-se livre para tirar duvidas.
@echo. Ou mesmo pedir simples alteracoes no script. Nunca
@echo. fui programador. Mas para coisas pequenas e simples
@echo. talvez eu seja capaz de ajudar!
@echo.
@echo.
@taskkill /IM excel.exe /t /f
@echo.
@timeout /nobreak /t 10
@echo.
@exit /b[/code]

ActivEXel.js
[code=JavaScript]/**
* To Run:
* cscript.exe /nologo ActivEXel.js "diretorio-raiz" "arquivo.ext"
* cscript.exe /nologo ActivEXel.js "%~dp0" "%_src%"
* wscript.exe ActivEXel.js "diretorio-raiz" "arquivo.ext"
*
* Sobre:
* Infelizmente, não posso produzir um código muito inteligente.
* E portanto uma das exigências do código é que a planinha seja
* populada à partir de A1. Como já é o arquivo modelo. E também
* espera-se que a planinha esteja corretamente preenchida em todas
* as colunas com valores coerentes. Número onde é preciso ser
* número e texto onde precisa ser texto. Espero por fim que os
* números presentes sejam apenas 0 ou 1.
*
* Qualquer mudança a este padrão tenderá a produzir um comportamento
* anômalo do script.
*
* Licença:
* Não há. Pode-se fazer qualquer coisa com ele, sem consulta prévia.
*
* Tudo o que escrevo e digo não pertence
* mais a mim depois de escrito e dito.
*/

// Entrada dos argumentos
var i = 0;
var j = 0;
var args = WScript.Arguments;
var fsobjetc = new ActiveXObject("Scripting.FileSystemObject");
var excelfile = fsobjetc.GetAbsolutePathName(WScript.Arguments(0)+WScript.Arguments(1));

// Configurações de Pesquisa Padrão
var stringout = "";
var excelsheetname = "";
var excelsheetnumber = 1;
var excelsheetrange = "A1";
var excel = new ActiveXObject("Excel.Application");
var book = excel.Workbooks.Open(excelfile);
var sheetnumber = book.Sheets.Item(excelsheetnumber);
var rowCount = sheetnumber.UsedRange.Rows.Count;
var colCount = sheetnumber.UsedRange.Columns.Count;
var value = sheetnumber.Range(excelsheetrange);

// Listagem das Informações Recebidas:
WSH.echo("BREVE RESUMO DAS INFORMACOES RECEBIDAS");
WSH.echo("Num de Parametros: "+args.length + " (o esperado sao 2)");
for (i = 0; i < args.length; i++) {
WSH.echo("Parametros / Args: "+args.Item(i));
}
WSH.echo("Arquivo do Excel.: "+excelfile);
WSH.echo("Versao Excel Ins.: "+excel.Version);
WSH.echo("Celula A1 p/teste: "+value);
WSH.echo("Qtd. de Linhas...: "+rowCount);
WSH.echo("Qtd. de Colunas..: "+colCount);

// Simples Validação da Tabela
stringout = "";
for(i = 2; i <= rowCount; i++){
for(j = 3; j <= colCount; j++){
if ( (sheetnumber.Cells(i, j).Value != "0") && (sheetnumber.Cells(i, j).Value != "1")) {
stringout = stringout + "("+ sheetnumber.Cells(i,1).Value + ", ";
stringout = stringout + sheetnumber.Cells(i,2).Value + ", ";
stringout = stringout + sheetnumber.Cells(1,j).Value + ", ";
stringout = stringout + sheetnumber.Cells(i,j).Value + ")\n";
stringout = stringout + "Linha: " + i + ", Coluna: " + j;
stringout = stringout + ", Valor: " +sheetnumber.Cells(i, j).Value + "\n\n";
}
}
}

if (stringout != ""){
WSH.echo("Listagem de Erros: \n\n"+stringout);
// Se erros forem encontrados, a string estará preenchida,
// o script fechará, informando os erros.
book.Close();
excel.Application.Quit();
excel.Quit();
excel = null;
WScript.Sleep(100);
CollectGarbage();
WScript.Echo("\n Erros foram encontrados!\n");
WScript.Echo(" Por favor, corrija-os e execute o script novamente!\n");
WScript.Echo(" Tecle enter para fechar este script. ");
WScript.StdIn.ReadLine();
WScript.Quit();
}

// Início do Código
WSH.echo("Listagem de Erros: Nenhum Erro Simples!\n\n");
WSH.echo("Por favor, aguarde pelo menos mais 20 min...\n\n");
stringout = "";
stringout = "Ordem num;Família;Espécies;Local\n";
var ordem = 1;
for(i = 2; i <= rowCount; i++){
for(j = 3; j <= colCount; j++){
if ( sheetnumber.Cells(i, j).Value == "1") {
stringout = stringout + ordem + ";" + sheetnumber.Cells(i,1).Value + ";";
stringout = stringout + sheetnumber.Cells(i,2).Value + ";";
stringout = stringout + sheetnumber.Cells(1,j).Value + "\n";
ordem++;
}
}
}
var fso = new ActiveXObject("Scripting.FileSystemObject");
var filecsv = fso.CreateTextFile(WScript.Arguments(0)+"ActivEXel.csv", true);
filecsv.WriteLine(stringout);
filecsv.Close();
WSH.echo("Conversao realizada! Arquivo gerado: ActivEXel.csv\n\n");

// Rotina de Fechamento do Arquivo
book.Close();
excel.Application.Quit();
excel.Quit();
excel = null;
WScript.Sleep(100);
CollectGarbage();
WScript.Echo(" Tecle enter para fechar este script. ");
WScript.StdIn.ReadLine();
WScript.Quit();
// Nos meus testes, excel.exe só fecha após a conclusão do script.
// E nunca através de qualquer outro comando. [/code]

Caso tenha dificuldades em fechar o Excel este script poderá ajudá-lo:

force-close-excel.cmd
[code=DOS]@echo off & mode 80,25
@echo.
@echo.
@taskkill /IM excel.exe /t /f
@timeout /nobreak /t 3
@exit /b[/code]

Por fim, terminei postando o script no meu blog pessoal, lá é um ambiente mais seguro, visto que minhas mensagens não são deletadas lá. Caso queria alguma alteração no código prefira me contactar por lá ou através do discord: ofernandofilo#5303

Blog: https://ofernandofilo.blogspot.com/2018/01/batchfile-and-jscript-to-read-excel-file.html
Zip: https://drive.google.com/file/d/1if3QZATT_FJ-yR2GJTtf1SmNfEDcbPrc/view

Anexos

ActiveEXelBatchAndJS.7z
ActivEXel.csv

ze_bonitinho.png
EdsonBR
EdsonBR Veterano Registrado
273 Mensagens 280 Curtidas
#6 Por EdsonBR
16/01/2018 - 18:15
Boa tarde, Ivanovic

Veja no anexo se entendi direito

Atenção para a célula ACF33: Linha Poaceae/Axonopus anceps (Mez) Hitchc. - Coluna PAC38
O valor não é nem 0 nem 1, está 10. Deve ter sido erro de digitação, acredito.
Por conta e risco corrigi o valor para 1. Se for 0, corrija e rode a macro novamente (não esqueça de habilitar macros antes).

Anexos

matrz_completa.zip
LinkedIn
Ivanovic
Ivanovic Novo Membro Registrado
9 Mensagens 4 Curtidas
#7 Por Ivanovic
21/01/2018 - 01:24
EdsonBR disse:
Boa tarde, Ivanovic

Veja no anexo se entendi direito

Atenção para a célula ACF33: Linha Poaceae/Axonopus anceps (Mez) Hitchc. - Coluna PAC38
O valor não é nem 0 nem 1, está 10. Deve ter sido erro de digitação, acredito.
Por conta e risco corrigi o valor para 1. Se for 0, corrija e rode a macro novamente (não esqueça de habilitar macros antes).


-----------
-----------

Caro EdsonBR,

Muito obrigado mesmo, não sei como você fez, mas você me deu uma grande ajuda! Agora vou poder trabalhar os dados novamente.

Forte abraço...
EdsonBR
EdsonBR Veterano Registrado
273 Mensagens 280 Curtidas
#8 Por EdsonBR
22/01/2018 - 14:35
Ivanovic disse:
Caro EdsonBR,
Muito obrigado mesmo, não sei como você fez...


cvv disse:
Não baixei o trabalho de EdsonBR e não sei se ele compartilhou um código geral


O código está na pasta de trabalho, é só abrir o arquivo e o VBE (ALT+F11). Em todos os casos copio ele abaixo novamente.

cvv disse:
...e muito menos sei do desempenho do código dele. Pelos testes que fiz... meu script demora por volta de 40 min... no meu notebook, que é uma máquina modesta com um processador de 2010, um Core i3-380M de primeira geração e clock 2.5Ghz.

Meu notebook tá um pouco inferior: Core i3 330M 2.13GHz 4GB RAM Windows 7 64bits, Excel 2007.

Tempo processamento: 5,70 segundos

Sabe-se que o VBA não é uma linguagem rápida. Mas o bom do Excel é que dá para usar suas funções no VBA podendo acelerar bastante o código. Além disso, muitas funções do Excel retornam matrizes inteiras numa só operação, sem precisar fazer loops (no código percorri apenas as linhas, não cada linha e cada coluna). Não tenho conhecimento se isso pode ser feito no javascript.

cvv disse:
Meu script são praticamente 2 arquivos (um .cmd e um .js)

CVV, achei muito interessante essa forma que vc usou para atacar o problema. Não manjo nada de java e confesso que fazia tempo que não via alguém criar um batch file assim.

cvv disse:
Por fim, terminei postando o script no meu blog pessoal

Passei rapidamente pelo seu blog. Gostei da organização do seu código, ficou visualmente perfeito. Parabéns!


Option Explicit

Sub ReverteMatriz()

  'Dúvida de Ivanovic no Fórum Comunidade do Hardware. Resposta proposta por Edson Luiz Branco em 16/01/18

  Dim V1, tbl As Range, tít As String, F As String, rg As Range, lin As Long, k As Long

  Set tbl = Plan1.Range("A1&quot.CurrentRegion

  tít = tbl.Range(Plan1.Cells(1, 3), Plan1.Cells(1, tbl.Columns.Count)).Address(False, False)

  Set tbl = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count)

  lin = 2

  Plan2.Cells.Clear

  Application.ScreenUpdating = False

     With Application.WorksheetFunction

       For Each rg In tbl.Rows

          F = Range(rg.Cells(3), rg.Cells(rg.Columns.Count)).Address(False, False)

          F = "=IF(" & F & "=1,OFFSET(" & F & ",-(ROW(" & F & &quot-ROW(" & tít & &quot),0),"""&quot"

          V1 = .Trim(Join(Plan1.Evaluate(F)))

          If V1 <> vbNullString Then

            V1 = .Transpose(Split(V1))

            With Plan2

              k = lin + UBound(V1, 1) - 1

              .Range(.Cells(lin, 1), .Cells(k, 1)).Value = Evaluate("ROW(" & lin - 1 & ":" & k - 1 & &quot&quot

              .Range(.Cells(lin, 2), .Cells(k, 3)).Value = rg.Range("A1:B1&quot.Value

              .Range(.Cells(lin, 4), .Cells(k, 4)).Value = V1

              lin = k + 1

            End With

          Else

            With Plan2

              .Cells(lin, 1).Value = lin - 1

              .Range(.Cells(lin, 2), .Cells(lin, 3)).Value = rg.Range("A1:B1&quot.Value

              .Cells(lin, 4).Value = "-"

              lin = lin + 1

            End With

          End If

       Next rg

       Plan2.Range("A11&quot.Value = Array("Ordem num", "Família", "Espécies", "Local&quot

       With Plan2.ListObjects.Add(xlSrcRange, Plan2.Range("A1&quot.CurrentRegion, , xlYes, , "TableStyleMedium7&quot

         .Name = "tblEspécies"

         .Range.Columns.AutoFit

       End With

     End With

  Application.ScreenUpdating = False

  Plan2.Activate

  Set tbl = Nothing: Set rg = Nothing

End Sub
LinkedIn
© 1999-2024 Hardware.com.br. Todos os direitos reservados.
Imagem do Modal