Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
Aula 1 - Procv e ProcH Original.xlsx Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA *Função PROCV - Procura Vertical *Função PROCH - Procura Horizontal *Conceito e exemplos iniciais www.excelexpert.com.br PROCV 01 Preencher a coluna dos nomes das Produtoras, a partir dos códigos Usar a função PROCV , pesquisando a tabela de PRODUTORAS, ao lado. PRODUTORAS Cod Nome do Filme Tipo Diretor Cod Produtora Categoria QTD Preço COD PRODUTORA A001 A Filha do General Legendado Simon West Par Policial 25 * 24.00 Col Columbia Tristar A002 AI - Inteligencia Artificial Legendado Steven Spielberg War Ficção 35 * 15.50 Fox Fox A003 Armageddon Dublado Michael Bay Tou Ação 27 * 19.90 Par Paramount A004 A Jurada Dublado Brian Gibson Col Policial 29 * 12.50 Tou Touchstone A005 A Identidade Bourne Dublado Michael Bay Fox Ação 19 * 13.50 Uni Universal C002 Chamas da Vingança Legendado Brian Gibson Par Ação 20 * 14.50 War Warner H001 Homem Aranha Dublado Roland Emmerich Uni Aventura 21 * 15.50 J001 Jurassic Park Legendado Steven Spielberg Uni Aventura 18 * 22.90 1 2 M001 Matrix Legendado Brian Gibson Par Ficção 19 * 28.80 O002 O Patriota Legendado Roland Emmerich Col Aventura 36 * 32.90 O003 O 6º dia Legendado Roger Spottiswood Col Ação 18 * 20.50 O004 O Retorno da Mumia Legendado Stephen Sommers Uni Suspense 15 * 15.50 P001 Pearl Harbor Dublado Michael Bay Tou Drama 42 * 35.00 P003 Planeta dos Macacos Dublado Tim Burton Fox Ação 22 * 27.50 S001 Star Wars I Dublado George Lucas Fox Ficção 16 * 39.00 S002 Superman Legendado Roger Spottiswood Col Aventura 17 * 35.00 T001 Tomb Raider Legendado Simon West Par Suspense 12 * 16.00 PROCV 02 Q51_Procv Preencher a coluna dias das semana por extenso, pesquisando a tabela em ingles ao lado. Data IND DIA SEMANA IND DIA SEMANA 01-Jan-17 1 1 SUNDAY 09-Jan-17 2 2 MONDAY 17-Jan-17 3 3 TUESDAY 25-Jan-17 4 4 WEDNESDAY 02-Feb-17 5 5 THURSDAY 10-Feb-17 6 6 FRIDAY 18-Feb-17 7 7 SATURDAY 26-Feb-17 1 06-Mar-17 2 14-Mar-17 3 PROCH 01 Q51_Procv Preencher a coluna dias das semana por extenso, pesquisando a tabela em ingles ao lado. Data IND DIA SEMANA IND 1 2 3 4 5 6 7 01-Jan-17 1 DIA SEMANA SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY 09-Jan-17 2 17-Jan-17 3 25-Jan-17 4 02-Feb-17 5 10-Feb-17 6 18-Feb-17 7 26-Feb-17 1 06-Mar-17 2 14-Mar-17 3 PROCV 03 Preencher a ficha do filme, a partir do código somente FICHA DE PESQUISA DE FILME Rótulo Dados Colunas Cod A001 01 Nome do Filme 02 Tipo 03 Diretor 04 Cod 05 Produtora 06 Categoria 07 QTD 08 Preço 09 Anotações PROCV OU VLOOKUP Procura em tabela VERTICAL ( por colunas) Procura um ítem numa tabela e traz como resultado uma outra coluna da mesma tabela ex.: procura o codigo e traz o nome da produtora o criterio de pesquisa fornecido ( prim parametro) vai ser procurado na primeira coluna da tabela ( 2 parametro) encontrado, devolve a n-esima coluna especificada ( 3 parametro) a procura sempre será a partir da primeira coluna dai em diante, as colunas a direita são numeradas em ordem Portanto necessita de quatro parametros 1- criterio de pesquisa 2- a tabela de pesquisa dica : pode-se usar o F3 3- o numero da coluna desejada da tabela 4- sim/não - se a pesquisa é exata ou aproximada ( não = exata) o ultimo parametro define se a procura será exata ou aproximada exata = FALSO (0) Aprox = VERDADEIRO (1 ou vazio) PROCH OU HLOOKUP Procura em tabela HORIZONTAL ( por linhas Tudo o que vale para o PROCV vale para o PROCH A difereça está no terceiro parâmetro = numero da linha ao invés de número de coluna Aula 1 - Procv e ProcH -Resolvido.xlsx Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA *Função PROCV - Procura Vertical *Função PROCH - Procura Horizontal *Conceito e exemplos iniciais www.excelexpert.com.br PROCV 01 Preencher a coluna dos nomes das Produtoras, a partir dos códigos Usar a função PROCV , pesquisando a tabela de PRODUTORAS, ao lado. PRODUTORAS Cod Nome do Filme Tipo Diretor Cod Produtora Categoria QTD Preço COD PRODUTORA A001 A Filha do General Legendado Simon West Par Paramount Policial 25 * 24.00 Col Columbia Tristar A002 AI - Inteligencia Artificial Legendado Steven Spielberg War Warner Ficção 35 * 15.50 Fox Fox A003 Armageddon Dublado Michael Bay Tou Touchstone Ação 27 * 19.90 Par Paramount A004 A Jurada Dublado Brian Gibson Col Columbia Tristar Policial 29 * 12.50 Tou Touchstone A005 A Identidade Bourne Dublado Michael Bay Fox Fox Ação 19 * 13.50 Uni Universal C002 Chamas da Vingança Legendado Brian Gibson Par Paramount Ação 20 * 14.50 War Warner H001 Homem Aranha Dublado Roland Emmerich Uni Universal Aventura 21 * 15.50 J001 Jurassic Park Legendado Steven Spielberg Uni Universal Aventura 18 * 22.90 1 2 M001 Matrix Legendado Brian Gibson Par Paramount Ficção 19 * 28.80 O002 O Patriota Legendado Roland Emmerich Col Columbia Tristar Aventura 36 * 32.90 O003 O 6º dia Legendado Roger Spottiswood Col Columbia Tristar Ação 18 * 20.50 O004 O Retorno da Mumia Legendado Stephen Sommers Uni Universal Suspense 15 * 15.50 P001 Pearl Harbor Dublado Michael Bay Tou Touchstone Drama 42 * 35.00 P003 Planeta dos Macacos Dublado Tim Burton Fox Fox Ação 22 * 27.50 S001 Star Wars I Dublado George Lucas Fox Fox Ficção 16 * 39.00 S002 Superman Legendado Roger Spottiswood Col Columbia Tristar Aventura 17 * 35.00 T001 Tomb Raider Legendado Simon West Par Paramount Suspense 12 * 16.00 PROCV 02 Q51_Procv Preencher a coluna dias das semana por extenso, pesquisando a tabela em ingles ao lado. Data IND DIA SEMANA IND DIA SEMANA 01-Jan-17 1 SUNDAY 1 SUNDAY 09-Jan-17 2 MONDAY 2 MONDAY 17-Jan-17 3 TUESDAY 3 TUESDAY 25-Jan-17 4 WEDNESDAY 4 WEDNESDAY 02-Feb-17 5 THURSDAY 5 THURSDAY 10-Feb-17 6 FRIDAY 6 FRIDAY 18-Feb-17 7 SATURDAY 7 SATURDAY 26-Feb-17 1 SUNDAY 06-Mar-17 2 MONDAY 14-Mar-17 3 TUESDAY PROCH 01 Q51_Procv Preencher a coluna dias das semana por extenso, pesquisando a tabela em ingles ao lado. Data IND DIA SEMANA IND 1 2 3 4 5 6 7 01-Jan-17 1 SUNDAY DIA SEMANA SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY 09-Jan-17 2 MONDAY 17-Jan-17 3 TUESDAY 25-Jan-17 4 WEDNESDAY 02-Feb-17 5 THURSDAY 10-Feb-17 6 FRIDAY 18-Feb-17 7 SATURDAY 26-Feb-17 1 SUNDAY 06-Mar-17 2 MONDAY 14-Mar-17 3 TUESDAY PROCV 03 Preencher a ficha do filme, a partir do código somente FICHA DE PESQUISA DE FILME Rótulo Dados Colunas Cod C002 01 Nome do Filme Chamas da Vingança 02 Tipo Legendado 03 Diretor Brian Gibson 04 Cod Par 05 Produtora Paramount 06 Categoria Ação 07 QTD 20 08 Preço R$ 14.50 09 Anotações PROCV OU VLOOKUP Procura em tabela VERTICAL ( por colunas) Procura um ítem numa tabela e traz como resultado uma outra coluna da mesma tabela ex.: procura o codigo e traz o nome da produtora o criterio de pesquisa fornecido ( prim parametro) vai ser procurado na primeira coluna da tabela ( 2 parametro) encontrado, devolve a n-esima coluna especificada ( 3 parametro) a procura sempre será a partir da primeira coluna dai em diante, as colunas a direita são numeradas em ordem Portanto necessita de quatro parametros 1- criterio de pesquisa 2- a tabela de pesquisa dica : pode-se usar o F3 3- o numero da coluna desejada da tabela 4- sim/não - se a pesquisa é exata ou aproximada ( não = exata) o ultimo parametro define se a procura será exata ou aproximada exata = FALSO (0) Aprox = VERDADEIRO (1 ou vazio) PROCH OU HLOOKUP Procura em tabela HORIZONTAL ( por linhas Tudo o que vale para o PROCV vale para o PROCH A difereça está no terceiro parâmetro = numero da linha ao invés de número de coluna Aula 2 - Tratamento de Erros Original .xlsm Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA *Função PROCV - Procura Vertical *Tratamento de Erros - combinando funções PROCV & SEERRO www.excelexpert.com.br Original Excel Expert Brasil Int_Av SP-81_Caso Pratico A Utilizar a tabela de produtos da guia Tabelas ( devidamente nomeada) B O vendedor irá preencher somente os campos em amarelo ( codigo, quantidade). C Se codigos e quantidades ficarem vazios,manter vazios os demais campos. Cod Qtd Descrição Marca Preço Sub Total TOTAL TABELAS TABELA DE PRODUTOS Cod Descrição Preço Marca Cel Celular R$799.00 LG Dvd DVD Player R$350.00 LG Fog Fogão 5B R$879.00 Consul Frz Freezer R$850.00 Consul Gel Geladeira R$1,499.00 Brastemp Not Notebook R$2,399.00 Samsung Som Conjunto Som R$450.00 Sony Tv TV 29 pol R$1,050.00 Sony Anotações DICAS PARA O MELHOR DESENVOLVIMENTO FAZER OS PROCVS PARA CADA DADO DESEJADO Para cada dados desejado, buscar o mesmo na tabela de produtos COMBINAR COM A FUNÇÃO SEERRO Quando o código estiver em branco, teremos um erro #N/D, ou seja, não disponivel Qualquer erro se propaga pela planilha, para as demais formulas que usam a célula!! Para resolver o problema, teremos de usar o PROCV combinado com a função SEERRO A função SEERRO nos permite tratar os erros das demais funções, de forma que o erro não se propaga pela planilha!! REAPROVEITAMENTO DE PROCVS Da mesma forma que foi feito para o exercício anterior ( ficha dos filmes, tentar reaproveitar as fórmulas de PROCV para a direita e para baixo. Aula 2 - Tratamento de Erros Resolvido .xlsm Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA *Função PROCV - Procura Vertical *Tratamento de Erros - combinando funções PROCV & SEERRO www.excelexpert.com.br Original Excel Expert Brasil Int_Av SP-81_Caso Pratico A Utilizar a tabela de produtos da guia Tabelas ( devidamente nomeada) B O vendedor irá preencher somente os campos em amarelo ( codigo, quantidade). C Se codigos e quantidades ficarem vazios,manter vazios os demais campos. Cod Qtd Descrição Marca Preço Sub Total R$ 0.00 R$ 0.00 R$ 0.00 R$ 0.00 R$ 0.00 R$ 0.00 R$ 0.00 R$ 0.00 TOTAL R$ 0.00 1 2 4 3 Cod Descrição Marca Preço TABELAS TABELA DE PRODUTOS Cod Descrição Preço Marca Cel Celular R$799.00 LG Dvd DVD Player R$350.00 LG Fog Fogão 5B R$879.00 Consul Frz Freezer R$850.00 Consul Gel Geladeira R$1,499.00 Brastemp Not Notebook R$2,399.00 Samsung Som Conjunto Som R$450.00 Sony Tv TV 29 pol R$1,050.00 Sony 1 2 3 4 Cod Descrição Preço Marca Anotações DICAS PARA O MELHOR DESENVOLVIMENTO FAZER OS PROCVS PARA CADA DADO DESEJADO Para cada dados desejado, buscar o mesmo na tabela de produtos COMBINAR COM A FUNÇÃO SEERRO Quando o código estiver em branco, teremos um erro #N/D, ou seja, não disponivel Qualquer erro se propaga pela planilha, para as demais formulas que usam a célula!! Para resolver o problema, teremos de usar o PROCV combinado com a função SEERRO A função SEERRO nos permite tratar os erros das demais funções, de forma que o erro não se propaga pela planilha!! REAPROVEITAMENTO DE PROCVS Da mesma forma que foi feito para o exercício anterior ( ficha dos filmes, tentar reaproveitar as fórmulas de PROCV para a direita e para baixo. Aula 3 - Procv Aproximado Original.xlsm Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES DE PESQUISA E REFERÊNCIA * PROCV APROXIMADO * Utilização do PROCV em tabelas de faixas de valores www.excelexpert.com.br PROCV APROX1 USANDO A FUNÇÃO PROCV PARA UMA PESQUISA POR APROXIMAÇÃO Recursos complementares : Nomear o intervalo da tabela de descontos Tabela de desconto Unidades Peça Preço de tabela Desconto Preço líquido Total DE ATÉ Desconto 53 A-182 R$ 9.95 R$ 9.95 R$ 527.35 0 7 4% 12 B-047 R$ 6.95 R$ 6.95 R$ 83.40 8 15 5% 62 B-047 R$ 6.95 R$ 6.95 R$ 430.90 16 23 6% 26 B-111 R$ 19.95 R$ 19.95 R$ 518.70 24 31 7% 19 C-098 R$ 2.95 R$ 2.95 R$ 56.05 32 39 8% 35 D-017 R$ 27.95 R$ 27.95 R$ 978.25 40 47 9% 5 D-178 R$ 17.95 R$ 17.95 R$ 89.75 48 100 10% 44 D-178 R$ 17.95 R$ 17.95 R$ 789.80 APROX 2 USANDO A FUNÇÃO PROCV PARA UMA PESQUISA POR APROXIMAÇÃO Recursos complementares : Usando datas na tabela de faixas Tabela de desconto Unidades Peça Preço de tabela Desconto Preço líquido DE ATÉ Desconto 9/1/16 D-178 R$ 17.95 R$ 17.95 1-Sep 7-Sep 10% 9/8/16 B-047 R$ 6.95 R$ 6.95 8-Sep 14-Sep 9% 9/15/16 C-098 R$ 2.95 R$ 2.95 15-Sep 21-Sep 8% 9/20/16 B-111 R$ 19.95 R$ 19.95 22-Sep 28-Sep 7% 9/30/16 D-017 R$ 27.95 R$ 27.95 29-Sep 5-Oct 6% 10/6/16 D-178 R$ 17.95 R$ 17.95 6-Oct 12-Oct 5% 10/13/16 A-182 R$ 9.95 R$ 9.95 13-Oct 31-Dec 4% 10/20/16 B-047 R$ 6.95 R$ 6.95 1 2 3 APROX 3 Excel Expert Brasil Int_Av SP-58_Calc DataHora3 Calcular a quantidade de horas que cada veículo permaneceu no estacionamento. Na coluna Recebido, calcular o valor a cobrar conforme tabela de tarifas. Caixa 0.0 TABELA DE TARIFAS Placas Entrada Saida Horas Recebido De Até Valor ABC-1049 12:44:25 PM 2:55:36 PM 12:00:01 AM 12:30:00 AM R$ 5.00 ABC-1442 6:18:45 AM 10:51:54 PM 12:30:01 AM 1:00:00 AM R$ 10.00 ABC-1582 2:20:52 PM 3:51:48 PM 1:00:01 AM 1:30:00 AM R$ 11.00 ABC-1733 5:26:15 AM 11:16:43 PM 1:30:01 AM 2:00:00 AM R$ 12.00 ABC-1854 2:03:54 AM 11:08:17 PM 2:00:01 AM 2:30:00 AM R$ 13.00 ABC-2109 9:35:38 AM 2:30:44 PM 2:30:01 AM 3:00:00 AM R$ 15.00 ABC-2506 3:06:33 PM 11:20:10 PM 3:00:01 AM 3:30:00 AM R$ 16.00 ABC-2965 11:57:37 AM 11:11:16 PM 3:30:01 AM 4:00:00 AM R$ 17.00 ABC-4279 2:35:38 PM 8:23:50 PM 4:00:01 AM 4:30:00 AM R$ 18.00 ABC-4700 6:30:57 PM 9:43:23 PM 4:30:01 AM 5:00:00 AM R$ 19.00 ABC-6630 6:43:26 AM 8:00:05 AM 5:00:01 AM 5:30:00 AM R$ 20.00 ABC-8353 3:28:45 AM 1:31:03 PM 5:30:01 AM 6:00:00 AM R$ 21.00 ABC-8993 7:49:47 AM 4:48:34 PM 6:00:01 AM 7:00:00 AM R$ 22.00 ABC-9334 2:32:57 AM 10:06:22 AM 7:00:01 AM 8:00:00 AM R$ 23.00 ABC-9643 2:11:11 PM 10:13:30 PM 8:00:01 AM 9:00:00 AM R$ 24.00 9:00:01 AM 10:00:00 AM R$ 25.00 10:00:01 AM 11:00:00 AM R$ 26.00 11:00:01 AM 12:00:00 PM R$ 27.00 12:00:01 PM 1:00:00 PM R$ 28.00 1:00:01 PM 2:00:00 PM R$ 29.00 2:00:01 PM 3:00:00 PM R$ 30.00 3:00:01 PM 4:00:00 PM R$ 31.00 4:00:01 PM 6:00:00 PM R$ 32.00 6:00:01 PM 8:00:00 PM R$ 33.00 8:00:01 PM 10:00:00 PM R$ 34.00 10:00:01 PM 11:59:59 PM R$ 35.00 Anot PROCV Aprox PROCV APROXIMADO ( OU POR INTERVALO) Uma procura por intervalo ocorre quando temos uma tabela numérica de faixas de valores, e o critério de pesquisa está numa determinada faixa REGRAS DA TABELA DE FAIXAS DE VALORES 1- Primeira coluna numérica 2- ordem ascendente 3-começa no zero QUARTO PARAMETRO DO PROCV procura exata = procurar intervalo = 0 procura aproximada = procurar intervalo <>0 Aula 3 - Procv Aproximado Resolvida.xlsm Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES DE PESQUISA E REFERÊNCIA * PROCV APROXIMADO * Utilização do PROCV em tabelas de faixas de valores www.excelexpert.com.br PROCV APROX1 USANDO A FUNÇÃO PROCV PARA UMA PESQUISA POR APROXIMAÇÃO Recursos complementares : Nomear o intervalo da tabela de descontos Tabela de desconto Unidades Peça Preço de tabela Desconto Preço líquido Total DE ATÉ Desconto 50 A-182 R$ 9.95 10% R$ 8.96 R$ 447.75 0 7 4% 12 B-047 R$ 6.95 5% R$ 6.60 R$ 79.23 8 15 5% 62 B-047 R$ 6.95 10% R$ 6.25 R$ 387.81 16 23 6% 26 B-111 R$ 19.95 7% R$ 18.55 R$ 482.39 24 31 7% 19 C-098 R$ 2.95 6% R$ 2.77 R$ 52.69 32 39 8% 35 D-017 R$ 27.95 8% R$ 25.71 R$ 899.99 40 47 9% 5 D-178 R$ 17.95 4% R$ 17.23 R$ 86.16 48 100 10% 44 D-178 R$ 17.95 9% R$ 16.33 R$ 718.72 APROX 2 USANDO A FUNÇÃO PROCV PARA UMA PESQUISA POR APROXIMAÇÃO Recursos complementares : Usando datas na tabela de faixas Tabela de desconto Unidades Peça Preço de tabela Desconto Preço líquido DE ATÉ Desconto 9/2/16 D-178 R$ 17.95 10% R$ 16.16 1-Sep 7-Sep 10% 9/8/16 B-047 R$ 6.95 9% R$ 6.32 8-Sep 14-Sep 9% 9/15/16 C-098 R$ 2.95 8% R$ 2.71 15-Sep 21-Sep 8% 9/20/16 B-111 R$ 19.95 8% R$ 18.35 22-Sep 28-Sep 7% 9/30/16 D-017 R$ 27.95 6% R$ 26.27 29-Sep 5-Oct 6% 10/6/16 D-178 R$ 17.95 5% R$ 17.05 6-Oct 12-Oct 5% 10/13/16 A-182 R$ 9.95 4% R$ 9.55 13-Oct 31-Dec 4% 10/20/16 B-047 R$ 6.95 4% R$ 6.67 1 2 3 APROX 3 Excel Expert Brasil Int_Av SP-58_Calc DataHora3 Calcular a quantidade de horas que cada veículo permaneceu no estacionamento. Na coluna Recebido, calcular o valor a cobrar conforme tabela de tarifas. Caixa R$ 338.00 TABELA DE TARIFAS Placas Entrada Saida Horas Recebido De Até Valor ABC-1049 12:44:25 PM 2:55:36 PM 2:11:10 AM R$ 13.00 12:00:01 AM 12:30:00 AM R$ 5.00 ABC-1442 6:18:45 AM 10:51:54 PM 4:33:09 PM R$ 32.00 12:30:01 AM 1:00:00 AM R$ 10.00 ABC-1582 2:20:52 PM 3:51:48 PM 1:30:55 AM R$ 12.00 1:00:01 AM 1:30:00 AM R$ 11.00 ABC-1733 5:26:15 AM 11:16:43 PM 5:50:28 PM R$ 32.00 1:30:01 AM 2:00:00 AM R$ 12.00 ABC-1854 2:03:54 AM 11:08:17 PM 9:04:23 PM R$ 34.00 2:00:01 AM 2:30:00 AM R$ 13.00 ABC-2109 9:35:38 AM 2:30:44 PM 4:55:05 AM R$ 19.00 2:30:01 AM 3:00:00 AM R$ 15.00 ABC-2506 3:06:33 PM 11:20:10 PM 8:13:36 AM R$ 24.00 3:00:01 AM 3:30:00 AM R$ 16.00 ABC-2965 11:57:37 AM 11:11:16 PM 11:13:39 AM R$ 27.00 3:30:01 AM 4:00:00 AM R$ 17.00 ABC-4279 2:35:38 PM 8:23:50 PM 5:48:11 AM R$ 21.00 4:00:01 AM 4:30:00 AM R$ 18.00 ABC-4700 6:30:57 PM 9:43:23 PM 3:12:26 AM R$ 16.00 4:30:01 AM 5:00:00 AM R$ 19.00 ABC-6630 6:43:26 AM 8:00:05 AM 1:16:38 AM R$ 11.00 5:00:01 AM 5:30:00 AM R$ 20.00 ABC-8353 3:28:45 AM 1:31:03 PM 10:02:18 AM R$ 26.00 5:30:01 AM 6:00:00 AM R$ 21.00 ABC-8993 7:49:47 AM 4:48:34 PM 8:58:47 AM R$ 24.00 6:00:01 AM 7:00:00 AM R$ 22.00 ABC-9334 2:32:57 AM 10:06:22 AM 7:33:25 AM R$ 23.00 7:00:01 AM 8:00:00 AM R$ 23.00 ABC-9643 2:11:11 PM 10:13:30 PM 8:02:19 AM R$ 24.00 8:00:01 AM 9:00:00 AM R$ 24.00 9:00:01 AM 10:00:00 AM R$ 25.00 10:00:01 AM 11:00:00 AM R$ 26.00 11:00:01 AM 12:00:00 PM R$ 27.00 12:00:01 PM 1:00:00 PM R$ 28.00 1:00:01 PM 2:00:00 PM R$ 29.00 2:00:01 PM 3:00:00 PM R$ 30.00 3:00:01 PM 4:00:00 PM R$ 31.00 4:00:01 PM 6:00:00 PM R$ 32.00 6:00:01 PM 8:00:00 PM R$ 33.00 8:00:01 PM 10:00:00 PM R$ 34.00 10:00:01 PM 11:59:59 PM R$ 35.00 Anot PROCV Aprox PROCV APROXIMADO ( OU POR INTERVALO) Uma procura por intervalo ocorre quando temos uma tabela numérica de faixas de valores, e o critério de pesquisa está numa determinada faixa REGRAS DA TABELA DE FAIXAS DE VALORES 1- Primeira coluna numérica 2- ordem ascendente 3-começa no zero QUARTO PARAMETRO DO PROCV procura exata = procurar intervalo = 0 procura aproximada = procurar intervalo <>0 Aula 4 - Comparando tabelas Original.xlsx Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA * A função PROCV - Pesquisa cruzada *Comparando duas tabelas usando SEERRO e PROCV *Caso prático - reforço de conceitos www.excelexpert.com.br Metodo 01 Excel Expert Brasil USANDO SEERRO Int_Av Q58_ProcvDuplo SP-54_SEERRO PROCV Procurar antigos na Nova , para identificar os filmes mantidos e excluidos Procurar novos na Antiga , para identificar os filmes que foram mantidos e incluidos No Status indicar MANTIDO, EXCLUIDO, INCLUIDO conforme o caso LISTA ANTIGA LISTA NOVA Cod Nome do Filme Procura Nova STATUS Cod Nome do Filme Procura Antiga STATUS A001 A Filha do General A003 Armageddon A005 A Identidade Bourne J001 Jurassic Park A004 A Jurada P003 Planeta dos Macacos A003 Armageddon T001 Tomb Raider H001 Homem Aranha O004 O Retorno da Mumia J001 Jurassic Park A001 A Filha do General M001 Matrix C002 Chamas da Vingança O003 O 6º dia O003 O 6º dia O002 O Patriota S003 Superman-O retorno O004 O Retorno da Mumia S002 Superman P003 Planeta dos Macacos A005 A Identidade Bourne S001 Star Wars I O002 O Patriota S002 Superman A004 A Jurada T003 Thriller - O filme M001 Matrix T001 Tomb Raider M002 Matrix II T002 Tomb Raider II S001 Star Wars I A002 AI - Inteligencia Artificial H001 Homem Aranha P001 Pearl Harbor Metodo 02 Excel Expert Brasil USANDO SE e EERROS Int_Av Q58_ProcvDuplo SP-54_SEERRO PROCV Procurar antigos na Nova , para identificar os filmes mantidos e excluidos Procurar novos na Antiga , para identificar os filmes que foram mantidos e incluidos No Status indicar MANTIDO, EXCLUIDO, INCLUIDO conforme o caso LISTA ANTIGA LISTA NOVA Cod Nome do Filme STATUS Cod Nome do Filme STATUS A001 A Filha do General A003 Armageddon A005 A Identidade Bourne J001 Jurassic Park A004 A Jurada P003 Planeta dos Macacos A003 Armageddon T001 Tomb Raider H001 Homem Aranha O004 O Retorno da Mumia J001 Jurassic Park A001 A Filha do General M001 Matrix C002 Chamas da Vingança O003 O 6º dia O003 O 6º dia O002 O Patriota S003 Superman-O retorno O004 O Retorno da Mumia S002 Superman P003 Planeta dos Macacos A005 A Identidade Bourne S001 Star Wars I O002 O Patriota S002 Superman A004 A Jurada T003 Thriller - O filme M001 Matrix T001 Tomb Raider M002 Matrix II T002 Tomb Raider II S001 Star Wars I A002 AI - Inteligencia Artificial H001 Homem Aranha P001 Pearl Harbor Anotações PESQUISA CRUZADA- COMPARANDO 2 TABELAS COM SE ERRO & PROCV ERROS QUE PODEM OCORRER EM FORMULAS Quando queremos comparar duas tabelas para identificar as diferenças temos que fazer um PROCV da tabela A na tabela B e vice-versa. CÓDIGO SIGNIFICADO Os itens não encontrados geram o erro explicado em aula anterior (#N/D) ERROR:#DIV/0! DIVISÃO POR ZERO ERROR:#NAME? NOME ERRADO DA FUNÇÃO OU DE CÉLULAS FUNÇÃO SEERRO ERROR:#REF! PERDA DE REFERÊNCIA A função SEERRO permite substituir o eventual erro por outro resultado ERROR:#VALUE! CÁLCULO COM VALOR NÃO NUMÉRICO Esta função foi criada a partir da versão 2007 do Excel. ERROR:#N/A NÃO DISPONIVEL (PROCV - PROCH) É recomendável sempre usar o PROCV dentro da função SEERRO, para tratar o #N/D METODOLOGIA DA SOLUÇÃO DESTE EXEMPLO No Método 01, usamos a função SEERRO para tratar as divergencias Em seguida, usamos a função SE nas colunas STATUS para identificar mantidos, excluidos da antiga, incluidos na nova No Método 02, usamos a função SE e a função EERROS identificando direto na coluna Status os mantidos, excluidos da antiga, incluidos na nova É importante ressaltar que outros casos podem exigir soluções diferentes.... mas este é realmente uma excelente metodologia para comparar tabelas de mesma natureza. Aula 4 - Comparando tabelas Resolvida.xlsx Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA * A função PROCV - Pesquisa cruzada *Comparando duas tabelas usando SEERRO e PROCV *Caso prático - reforço de conceitos www.excelexpert.com.br Metodo 01 Excel Expert Brasil USANDO SEERRO Int_Av Q58_ProcvDuplo SP-54_SEERRO PROCV Procurar antigos na Nova , para identificar os filmes mantidos e excluidos Procurar novos na Antiga , para identificar os filmes que foram mantidos e incluidos No Status indicar MANTIDO, EXCLUIDO, INCLUIDO conforme o caso LISTA ANTIGA LISTA NOVA Cod Nome do Filme Procura Nova STATUS Cod Nome do Filme Procura Antiga STATUS T003 Thriller - O filme sumiu EXCLUIDO C002 Chamas da Vingança apareceu INCLUIDO T002 Tomb Raider II sumiu EXCLUIDO S003 Superman-O retorno apareceu INCLUIDO A001 A Filha do General A Filha do General MANTIDO M002 Matrix II apareceu INCLUIDO A005 A Identidade Bourne A Identidade Bourne MANTIDO A002 AI - Inteligencia Artificial apareceu INCLUIDO A004 A Jurada A Jurada MANTIDO P001 Pearl Harbor apareceu INCLUIDO A003 Armageddon Armageddon MANTIDO A003 Armageddon Armageddon MANTIDO H001 Homem Aranha Homem Aranha MANTIDO J001 Jurassic Park Jurassic Park MANTIDO J001 Jurassic Park Jurassic Park MANTIDO P003 Planeta dos Macacos Planeta dos Macacos MANTIDO M001 Matrix Matrix MANTIDO T001 Tomb Raider Tomb Raider MANTIDO O003 O 6º dia O 6º dia MANTIDO O004 O Retorno da Mumia O Retorno da Mumia MANTIDO O002 O Patriota O Patriota MANTIDO A001 A Filha do General A Filha do General MANTIDO O004 O Retorno da Mumia O Retorno da Mumia MANTIDO O003 O 6º dia O 6º dia MANTIDO P003 Planeta dos Macacos Planeta dos Macacos MANTIDO S002 Superman Superman MANTIDO S001 Star Wars I Star Wars I MANTIDO A005 A Identidade Bourne A Identidade Bourne MANTIDO S002 Superman Superman MANTIDO O002 O Patriota O Patriota MANTIDO T001 Tomb Raider Tomb Raider MANTIDO A004 A Jurada A Jurada MANTIDO M001 Matrix Matrix MANTIDO S001 Star Wars I Star Wars I MANTIDO H001 Homem Aranha Homem Aranha MANTIDO Metodo 02 Excel Expert Brasil USANDO SE e EERROS Int_Av Q58_ProcvDuplo SP-54_SEERRO PROCV Procurar antigos na Nova , para identificar os filmes mantidos e excluidos Procurar novos na Antiga , para identificar os filmes que foram mantidos e incluidos No Status indicar MANTIDO, EXCLUIDO, INCLUIDO conforme o caso LISTA ANTIGA LISTA NOVA Cod Nome do Filme STATUS Cod Nome do Filme STATUS A001 A Filha do General MANTIDO A003 Armageddon MANTIDO A005 A Identidade Bourne MANTIDO J001 Jurassic Park MANTIDO A004 A Jurada MANTIDO P003 Planeta dos Macacos MANTIDO A003 Armageddon MANTIDO T001 Tomb Raider MANTIDO H001 Homem Aranha MANTIDO O004 O Retorno da Mumia MANTIDO J001 Jurassic Park MANTIDO A001 A Filha do General MANTIDO M001 Matrix MANTIDO C002 Chamas da Vingança INCLUIDO O003 O 6º dia MANTIDO O003 O 6º dia MANTIDO O002 O Patriota MANTIDO S003 Superman-O retorno INCLUIDO O004 O Retorno da Mumia MANTIDO S002 Superman MANTIDO P003 Planeta dos Macacos MANTIDO A005 A Identidade Bourne MANTIDO S001 Star Wars I MANTIDO O002 O Patriota MANTIDO S002 Superman MANTIDO A004 A Jurada MANTIDO T003 Thriller - O filme EXCLUIDO M001 Matrix MANTIDO T001 Tomb Raider MANTIDO M002 Matrix II INCLUIDO T002 Tomb Raider II EXCLUIDO S001 Star Wars I MANTIDO A002 AI - Inteligencia Artificial INCLUIDO H001 Homem Aranha MANTIDO P001 Pearl Harbor INCLUIDO Anotações PESQUISA CRUZADA- COMPARANDO 2 TABELAS COM SE ERRO & PROCV ERROS QUE PODEM OCORRER EM FORMULAS Quando queremos comparar duas tabelas para identificar as diferenças temos que fazer um PROCV da tabela A na tabela B e vice-versa. CÓDIGO SIGNIFICADO Os itens não encontrados geram o erro explicado em aula anterior (#N/D) ERROR:#DIV/0! DIVISÃO POR ZERO ERROR:#NAME? NOME ERRADO DA FUNÇÃO OU DE CÉLULAS FUNÇÃO SEERRO ERROR:#REF! PERDA DE REFERÊNCIA A função SEERRO permite substituir o eventual erro por outro resultado ERROR:#VALUE! CÁLCULO COM VALOR NÃO NUMÉRICO Esta função foi criada a partir da versão 2007 do Excel. ERROR:#N/A NÃO DISPONIVEL (PROCV - PROCH) É recomendável sempre usar o PROCV dentro da função SEERRO, para tratar o #N/D METODOLOGIA DA SOLUÇÃO DESTE EXEMPLO No Método 01, usamos a função SEERRO para tratar as divergencias Em seguida, usamos a função SE nas colunas STATUS para identificar mantidos, excluidos da antiga, incluidos na nova No Método 02, usamos a função SE e a função EERROS identificando direto na coluna Status os mantidos, excluidos da antiga, incluidos na nova É importante ressaltar que outros casos podem exigir soluções diferentes.... mas este é realmente uma excelente metodologia para comparar tabelas de mesma natureza. Aula 5 - Caso - Form E D Original .xlsm Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA * Caso - Formulário de Entrada de Dados *Uso otimizado de PROCV e SEERRO *Outras funcionalidades importantes num formulário www.excelexpert.com.br Formulario Excel Expert Brasil Int_Av SP-81_Caso Pratico A Utilizar a tabela de produtos da guia Tabelas ( devidamente nomeada) B O vendedor irá preencher somente os campos em amarelo ( codigo, quantidade). C Validar codigos e quantidades. Caso estejam vazios,manter vazios os demais campos. D Demais campos devem ser preenchidos automaticamente, reaproveitando PROCV. E Criar uma macro para limpar e proteger o formulário. Atribuir a macro ao botão Cod Qtd Descrição Marca Preço Sub Total 0 0 R$ 0.00 R$ 0.00 0 0 R$ 0.00 R$ 0.00 0 0 R$ 0.00 R$ 0.00 0 0 R$ 0.00 R$ 0.00 TOTAL R$ 0.00 2 4 3 Descrição Marca Preço TabProdutos TABELA DE PRODUTOS Cod Descrição Preço Marca Cel Celular R$799.00 LG Dvd DVD Player R$350.00 LG Fog Fogão 5B R$879.00 Consul Frz Freezer R$850.00 Consul Gel Geladeira R$1,499.00 Brastemp Not Notebook R$2,399.00 Samsung Som Conjunto Som R$450.00 Sony Tv TV 29 pol R$1,050.00 Sony Anotações DICAS PARA O MELHOR DESENVOLVIMENTO VALIDAR OS CODIGOS Fazer a validação dos codigos por uma lista de códigos válidos Validar as quantidades com base em limites permitidos USAR UM ÚNICO PROCV E REPETIR Para tanto, usar os numeros das colunas em células, de forma que se possa arrastar a primeira formula Usar PROCV dentro do SEERRO, deixando zeradas as células com erro Pois ao reaproveitar o PROCV temos céluas com textos e uma célula numerica ( Preço) OCULTAR ZEROS EM UMA PLANILHA Usar o menu Arquivo Opções e desabilitar a opção de mostrar zeros , para que os zeros não afetem a estética da planilha. PROTEGER PARCIALMENTE A PLANILHA Antes de proteger, formatar as celulas digitáveis como não bloqueadas Ocultar a linha com a numeração das colunas Proteger a planilha GRAVAR UMA MACRO Usar o comando Gravar Macros, da guia Exibição / Macros ou da guia Desenvolvedor Gravar macro que desprotege a planilha, limpa a area de digitação e protege a planilha ATRIBUIR A MACRO AO BOTÃO Usando o botão direito sobre o botão já desenhado, acionar Atribuir Macro... Aula 5 - Caso - Form E D Resolvida.xlsm Tema DICAS EXPRESS VERSÕES DO EXCEL FUNÇÕES: PROCURA E REFERÊNCIA * Caso - Formulário de Entrada de Dados *Uso otimizado de PROCV e SEERRO *Outras funcionalidades importantes num formulário www.excelexpert.com.br Formulario Excel Expert Brasil Int_Av SP-81_Caso Pratico A Utilizar a tabela de produtos da guia Tabelas ( devidamente nomeada) B O vendedor irá preencher somente os campos em amarelo ( codigo, quantidade). C Validar codigos e quantidades. Caso estejam vazios,manter vazios os demais campos. D Demais campos devem ser preenchidos automaticamente, reaproveitando PROCV. E Criar uma macro para limpar e proteger o formulário. Atribuir a macro ao botão Cod Qtd Descrição Marca Preço Sub Total 0 0 R$ 0.00 R$ 0.00 0 0 R$ 0.00 R$ 0.00 0 0 R$ 0.00 R$ 0.00 0 0 R$ 0.00 R$ 0.00 TOTAL R$ 0.00 2 4 3 Descrição Marca Preço TabProdutos TABELA DE PRODUTOS Cod Descrição Preço Marca Cel Celular R$799.00 LG Dvd DVD Player R$350.00 LG Fog Fogão 5B R$879.00 Consul Frz Freezer R$850.00 Consul Gel Geladeira R$1,499.00 Brastemp Not Notebook R$2,399.00 Samsung Som Conjunto Som R$450.00 Sony Tv TV 29 pol R$1,050.00 Sony Anotações DICAS PARA O MELHOR DESENVOLVIMENTO VALIDAR OS CODIGOS Fazer a validação dos codigos por uma lista de códigos válidos Validar as quantidades com base em limites permitidos USAR UM ÚNICO PROCV E REPETIR Para tanto, usar os numeros das colunas em células, de forma que se possa arrastar a primeira formula Usar PROCV dentro do SEERRO, deixando zeradas as células com erro Pois ao reaproveitar o PROCV temos céluas com textos e uma célula numerica ( Preço) OCULTAR ZEROS EM UMA PLANILHA Usar o menu Arquivo Opções e desabilitar a opção de mostrar zeros , para que os zeros não afetem a estética da planilha. PROTEGER PARCIALMENTE A PLANILHA Antes de proteger, formatar as celulas digitáveis como não bloqueadas Ocultar a linha com a numeração das colunas Proteger a planilha GRAVAR UMA MACRO Usar o comando Gravar Macros, da guia Exibição / Macros ou da guia Desenvolvedor Gravar macro que desprotege a planilha, limpa a area de digitação e protege a planilha ATRIBUIR A MACRO AO BOTÃO Usando o botão direito sobre o botão já desenhado, acionar Atribuir Macro...
Compartilhar