Sou biólogo e frequentemente tenho que fazer análises em conjuntos de dados relativamente grandes (para o Excel). Normalmente, são necessários alguns gráficos para exibir os dados, mas criá-los parece exigir que expor os dados de uma maneira diferente da maneira como eu os faço nativamente e chegar lá geralmente apresenta um problema. O Excel tem algumas funções que quase me dão o que preciso, mas ficam aquém. Basicamente eu estou procurando a capacidade de simular uma tabela dinâmica sem algumas das coisas (cabeçalho / linhas de resumo, ordenando os dados) que parecem ser obrigatórios com elas.
Eu normalmente formato minha tabela de dados em um estilo de "banco de dados", com cada coluna representando uma única variável, a linha superior sendo os rótulos de coluna, e as outras linhas sendo pontos de dados individuais. Algumas dessas colunas são variáveis dependentes e outras são variáveis independentes. Por exemplo, digamos que estou mapeando os efeitos de um medicamento em diferentes tipos de células. Eu teria colunas "variáveis independentes" para o tipo de célula, a dose da droga, o tempo desde que a droga foi administrada, etc ... As colunas "variável dependente" seriam as coisas medindo a resposta, como quantas sobreviveram.
O que normalmente faço é criar uma nova planilha e criar uma nova tabela, com uma variável independente na parte superior e as outras à esquerda e uma variável dependente no meio. Assim, por exemplo, à esquerda, eu teria uma coluna de rótulos com linhas para cada combinação de tipo de célula e dose de droga, que seriam as diferentes séries no meu gráfico. No topo, eu teria colunas para cada ponto de tempo, que estarão no eixo X do gráfico. No meio seria uma estatística (porcentagem de células sobreviventes, talvez) que corresponde ao tipo de célula e dose para aquela linha e o ponto de tempo para aquela coluna e será o valor Y de cada ponto de dados. Tudo isso precisa ser copiado / colado em pequenos pedaços da tabela de dados original. Este é praticamente o formato que é necessário se eu quiser fazer um gráfico de linha da variável dependente vs um dos independentes.
Obviamente, isso requer muito cliques repetitivos para realizar e também é um pouco propenso a erros. Eu percebo que isso é quase exatamente o que uma tabela dinâmica é, mas a tabela dinâmica apresenta seus próprios problemas. Isso me dá o layout que eu preciso, mas há um monte de linhas / colunas extras, como linhas de resumo e cabeçalho adicionadas. Além de bagunçar a tabela, eles também atrapalham a criação de um gráfico adequado apenas selecionando a região inteira. A melhor maneira que encontrei é criar a tabela, copiar a área de dados para uma seção diferente, excluir a original, excluir as linhas e colunas extras na cópia e, em seguida, movê-la para a região de dados de uma tabela vazia na qual Eu já criei os rótulos corretos. Isso também é um problema porque também ordena os dados, quando eu tenho minha própria ordem, que não é alfabética ou numérica, mas precisa ser mantida. Ele também não mantém links para a tabela antiga, o que é irritante se algo precisa ser atualizado.
A função dget seria perfeita para preencher a região de dados da nova tabela, uma vez que eu já tenho os rótulos de linha e coluna no lugar, mas infelizmente alguns de seus argumentos precisam ser fornecidos na forma de um array na tabela. (matrizes de funções ou constantes não são permitidas), portanto, não funciona nesse formato. Quando meu laboratório usou o Openoffice calc, escrevi uma função personalizada que usava argumentos separados para a área de dados de origem, uma lista de pares de colunas / valores para selecionar a linha correta e o nome da coluna a ser obtida dessa linha. Isso simplificou muito tudo, mas infelizmente não funciona no Excel 2011.