{"id":777,"date":"2021-01-31T11:33:53","date_gmt":"2021-01-31T11:33:53","guid":{"rendered":"http:\/\/jesquina.com.br\/site\/?p=777"},"modified":"2021-07-24T18:50:35","modified_gmt":"2021-07-24T18:50:35","slug":"dicas-no-excel","status":"publish","type":"post","link":"https:\/\/jesquina.com.br\/site\/dicas-no-excel\/","title":{"rendered":"DICAS NO EXCEL"},"content":{"rendered":"<p><iframe loading=\"lazy\" title=\"EXCEL - C\u00c2MERA AO VIVO NA PLANILHA\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/Opj6TY-ay4Q?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO -  SORTEADOR DUPLO\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/c2MDKhhD-i0?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>Fun\u00e7\u00f5es utilizadas nesta planilha<\/p>\n<p>=ALEAT\u00d3RIOENTRE(1;F5)<\/p>\n<p>=ALEAT\u00d3RIOENTRE(1;F7)<\/p>\n<p>=\u00cdNDICE(C:C;CORRESP(J5;B:B;0))<\/p>\n<p>=\u00cdNDICE(D:D;CORRESP(J12;B:B;0))<\/p>\n<p>&nbsp;<\/p>\n<p>Fun\u00e7\u00e3o para fazer o la\u00e7o de repeti\u00e7\u00e3o<\/p>\n<p>Sub REPETIR()<br \/>\nDim v_ciclo As Integer<br \/>\nv_ciclo1 = ThisWorkbook.Worksheets(&#8220;SORTEADOR&#8221;).Range(&#8220;F11&#8221;)<br \/>\nFor I = 1 To v_ciclo1<br \/>\nThisWorkbook.Worksheets(&#8220;SORTEADOR&#8221;).Range(&#8220;H4&#8221;) = I<br \/>\nNext<br \/>\nEnd Sub<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - DICAS E TRUQUES NO  EXCEL - IMPORTA\u00c7\u00c3O\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/CJBwJbIa1bI?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO PARA LIMPEZA E PESQUISA\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/glWkqQX_9YQ?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p><strong>F\u00d3RMULAS USADAS NESTE V\u00cdDEO<\/strong><\/p>\n<p>Criar uma sequ\u00eancia num\u00e9rica na coluna<\/p>\n<h3>=L1+1<\/h3>\n<p>Copiar valores nas c\u00e9lulas vazias<\/p>\n<h4>SE(J6=&#8221;&#8221;;M5;J6)<\/h4>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MONTANDO PAINEL DE AN\u00c1LISE PARTE 1\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/Kmi2VAygrzQ?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>F\u00f3rmulas usadas neste v\u00eddeo<\/p>\n<p>Escrever o nome do m\u00eas \u2013 SE()<\/p>\n<p><strong>COLOCAR NOME DO M\u00caS<\/strong><\/p>\n<p>=SE(E5=1;&#8221;JANEIRO&#8221;;SE(E5=2;&#8221;FEVEREIRO&#8221;;SE(E5=3;&#8221;MAR\u00c7O&#8221;;SE(E5=4;&#8221;ABRIL&#8221;;SE(E5=5;&#8221;MAIO&#8221;;SE(E5=6;&#8221;JUNHO&#8221;;SE(E5=7;&#8221;JULHO&#8221;;SE(E5=8;&#8221;AGOSTO&#8221;;SE(E5=9;&#8221;SETEMBRO&#8221;;SE(E5=10;&#8221;OUTUBRO&#8221;;SE(E5=11;&#8221;NOVEMBRO&#8221;;SE(E5=12;&#8221;DEZEMBRO&#8221;;&#8221;&#8221;))))))))))))<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>COLOCAR NOME DO DIA DA SEMANA<\/strong><\/p>\n<p>=SE(AQ18=&#8221;&#8221;;&#8221;&#8221;;SE(DIA.DA.SEMANA(AQ18)=7;&#8221;S\u00c1BADO&#8221;;SE(DIA.DA.SEMANA(AQ18)=6;&#8221;SEXTA&#8221;;SE(DIA.DA.SEMANA(AQ18)=5;&#8221;QUINTA&#8221;;SE(DIA.DA.SEMANA(AQ18)=4;&#8221;QUARTA&#8221;;SE(DIA.DA.SEMANA(AQ18)=3;&#8221;TER\u00c7A&#8221;;SE(DIA.DA.SEMANA(AQ18)=2;&#8221;SEGUNDA&#8221;;SE(DIA.DA.SEMANA(AQ18)=1;&#8221;DOMINGO&#8221;;&#8221;DIA)&#8221;))))))))<\/p>\n<p>&nbsp;<\/p>\n<p>Somar vendas com crit\u00e9rios \u2013 SOMASES()<\/p>\n<p>=SOMASES(IMPORTA\u00c7\u00c3O!I:I;IMPORTA\u00c7\u00c3O!J:J;ANALISE!$C$5;IMPORTA\u00c7\u00c3O!N:N;ANALISE!B6;IMPORTA\u00c7\u00c3O!O:O;ANALISE!$C$1)<\/p>\n<p>&nbsp;<\/p>\n<p>Somat\u00f3ria da coluna \u2013 SOMA()<\/p>\n<p>=SOMA(C6:C36)<\/p>\n<p>&nbsp;<\/p>\n<p>Percentual de participa\u00e7\u00e3o = DIVIS\u00c3O PURA<\/p>\n<p>=D4\/$N$4<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - CRIANDO UM PAINEL DE AN\u00c1LISE INDIVIDUAL POR VENDEDOR\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/fFxmiKuHgOo?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>F\u00d3RMULAS USADAS NESTE V\u00cdDEO<\/p>\n<p>1- Conta quantos pedidos foram emitidos pelo vendedor por dia dentro do m\u00eas<\/p>\n<p>=CONT.SES(IMPORTA\u00c7\u00c3O!J:J;AULA!$D$3;IMPORTA\u00c7\u00c3O!N:N;AULA!B5;IMPORTA\u00c7\u00c3O!O:O;AULA!$D$2)<\/p>\n<p>&nbsp;<\/p>\n<p>2-Soma as vendas do vendedor por dia dentro do m\u00eas.<\/p>\n<p>=SOMASES(IMPORTA\u00c7\u00c3O!I:I;IMPORTA\u00c7\u00c3O!J:J;AULA!$D$3;IMPORTA\u00c7\u00c3O!N:N;AULA!B5;IMPORTA\u00c7\u00c3O!O:O;AULA!$D$2)<\/p>\n<p>&nbsp;<\/p>\n<p>3-Calcula valor m\u00e9dio dos pedidos emitidos por dia.<\/p>\n<p>op\u00e7\u00e3o 1 -&gt; =SE(C5=0;0;D5\/C5)<\/p>\n<p>op\u00e7\u00e3o 2 -&gt; =SEERRO(D5\/C5;0)<\/p>\n<p>&nbsp;<\/p>\n<p>4-Soma as vendas do vendedor no m\u00eas<\/p>\n<p>=SOMA(D5:D35)<\/p>\n<p>&nbsp;<\/p>\n<p>5- Soma quantidade de pedidos emitidos pelo vendedor no m\u00eas<\/p>\n<p>=SOMA(C5:C35)<\/p>\n<p>&nbsp;<\/p>\n<p>6-Calcula valor m\u00e9dio dos pedidos do vendedor<\/p>\n<p>=SOMA(C5:C35)<\/p>\n<p>&nbsp;<\/p>\n<p>7- Calcula m\u00e9dia di\u00e1ria de vendas usando apenas dias trabalhados.<\/p>\n<p>=E39\/CONT.SE(C5:C35;&#8221;&gt;0&#8243;)<\/p>\n<p>&nbsp;<\/p>\n<p>8- Calcula participa\u00e7\u00e3o do vendedor dentro das vendas do m\u00eas<\/p>\n<p>=E39\/E47<\/p>\n<p>&nbsp;<\/p>\n<p>9- Pesquisa e mostra nome do vendedor<\/p>\n<p>=\u00cdNDICE(ANALISE!C2:M2;CORRESP(PESQUISA!D3;ANALISE!C5:M5;0))<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - EXPORTAR PLANILHA COMO IMAGEM\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/Wbz8rv0DEFw?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO PARA COPIAR DADOS DE UMA PASTA PARA OUTRA\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/4XpwmkFCW2A?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>SCRIPT DA MACRO PARA COPIAR DADOS DE UMA PASTA PARA OUTRA<\/p>\n<p>Sub COPIAR()<br \/>\nSheets(&#8220;IMPORTA\u00c7\u00c3O&#8221;).Select<br \/>\nRange(&#8220;A2:L203&#8221;).Select<br \/>\nSelection.Copy<br \/>\nSheets(&#8220;PESQUISA&#8221;).Select<br \/>\nRange(&#8220;C5&#8221;).Select<br \/>\nSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br \/>\n:=False, Transpose:=False<br \/>\nRange(&#8220;C5&#8221;).Select<br \/>\nEnd Sub<\/p>\n<p>&nbsp;<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO LIMPAR DADOS DE UMA PASTA\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/IQdyEPC52qQ?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>SCRIPT DA MACRO PARA LIMPAR DADOS DE UMA \u00c1REA DA PLANILHA<\/p>\n<p>Sub LIMPAR()<br \/>\nRange(&#8220;C5:N208&#8221;).Select<br \/>\nSelection.ClearContents<br \/>\nRange(&#8220;L5:Q208&#8221;).Select<br \/>\nSelection.ClearContents<br \/>\nRange(&#8220;C5&#8221;).Select<br \/>\nEnd Sub<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO PARA IMPRESS\u00c3O\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/FCGeXg5wdlY?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>SCRIPT DA MACRO PARA IMPRIMIR\u00a0 (COM AJUSTE DE LAYOUT)<\/p>\n<p>Sub IMPRESSAO()<\/p>\n<p>Range(&#8220;B1:W36&#8221;).Select<br \/>\nActiveWindow.SmallScroll Down:=-27<br \/>\nActiveSheet.PageSetup.PrintArea = &#8220;$B$1:$W$36&#8221;<br \/>\nActiveWindow.SmallScroll Down:=-9<br \/>\nRange(&#8220;F11&#8221;).Select<br \/>\nApplication.PrintCommunication = False<br \/>\nWith ActiveSheet.PageSetup<br \/>\n.PrintTitleRows = &#8220;&#8221;<br \/>\n.PrintTitleColumns = &#8220;&#8221;<br \/>\nEnd With<br \/>\nApplication.PrintCommunication = True<br \/>\nActiveSheet.PageSetup.PrintArea = &#8220;$B$1:$W$36&#8221;<br \/>\nApplication.PrintCommunication = False<\/p>\n<p>&#8216;AJUSTA LAYOUT DA IMPRESS\u00c3O<br \/>\nWith ActiveSheet.PageSetup<br \/>\n.LeftHeader = &#8220;&#8221;<br \/>\n.CenterHeader = &#8220;&#8221;<br \/>\n.RightHeader = &#8220;&#8221;<br \/>\n.LeftFooter = &#8220;&#8221;<br \/>\n.CenterFooter = &#8220;&#8221;<br \/>\n.RightFooter = &#8220;&#8221;<br \/>\n.LeftMargin = Application.InchesToPoints(0.511811024)<br \/>\n.RightMargin = Application.InchesToPoints(0.511811024)<br \/>\n.TopMargin = Application.InchesToPoints(0.787401575)<br \/>\n.BottomMargin = Application.InchesToPoints(0.787401575)<br \/>\n.HeaderMargin = Application.InchesToPoints(0.31496062)<br \/>\n.FooterMargin = Application.InchesToPoints(0.31496062)<br \/>\n.PrintHeadings = False<br \/>\n.PrintGridlines = False<br \/>\n.PrintComments = xlPrintNoComments<br \/>\n.PrintQuality = 300<br \/>\n.CenterHorizontally = False<br \/>\n.CenterVertically = False<br \/>\n.Orientation = xlPortrait<br \/>\n.Draft = False<br \/>\n.PaperSize = xlPaperLetter<br \/>\n.FirstPageNumber = xlAutomatic<br \/>\n.Order = xlDownThenOver<br \/>\n.BlackAndWhite = False<br \/>\n.Zoom = False<br \/>\n.FitToPagesWide = 1<br \/>\n.FitToPagesTall = 0<br \/>\n.PrintErrors = xlPrintErrorsDisplayed<br \/>\n.OddAndEvenPagesHeaderFooter = False<br \/>\n.DifferentFirstPageHeaderFooter = False<br \/>\n.ScaleWithDocHeaderFooter = True<br \/>\n.AlignMarginsHeaderFooter = True<br \/>\n.EvenPage.LeftHeader.Text = &#8220;&#8221;<br \/>\n.EvenPage.CenterHeader.Text = &#8220;&#8221;<br \/>\n.EvenPage.RightHeader.Text = &#8220;&#8221;<br \/>\n.EvenPage.LeftFooter.Text = &#8220;&#8221;<br \/>\n.EvenPage.CenterFooter.Text = &#8220;&#8221;<br \/>\n.EvenPage.RightFooter.Text = &#8220;&#8221;<br \/>\n.FirstPage.LeftHeader.Text = &#8220;&#8221;<br \/>\n.FirstPage.CenterHeader.Text = &#8220;&#8221;<br \/>\n.FirstPage.RightHeader.Text = &#8220;&#8221;<br \/>\n.FirstPage.LeftFooter.Text = &#8220;&#8221;<br \/>\n.FirstPage.CenterFooter.Text = &#8220;&#8221;<br \/>\n.FirstPage.RightFooter.Text = &#8220;&#8221;<br \/>\nEnd With<\/p>\n<p>&#8216; IMPRES\u00c3O<br \/>\nApplication.PrintCommunication = True<br \/>\nActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _<br \/>\nIgnorePrintAreas:=False<br \/>\nEnd Sub<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO FALAR\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/fxm10mVRRDM?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>Comando, usado dentro de uma macro,\u00a0 para fazer o excel falar um texto escrito dentro de uma c\u00e9lula.<\/p>\n<p>Range(&#8220;P2&#8221;).speak<\/p>\n<p><iframe loading=\"lazy\" title=\"EXCEL - MACRO - GERADOR DE SENHA\" width=\"605\" height=\"340\" src=\"https:\/\/www.youtube.com\/embed\/EkD3GB3yg24?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>F\u00d3RMULAS USADAS NESTA PLANILHA<\/p>\n<p>=ALEAT\u00d3RIOENTRE(1;$E$5)<\/p>\n<p>=\u00cdNDICE(C:C;CORRESP(E7;B:B;0))<\/p>\n<p>=CONCATENAR(F7;F8;F9;F10;F11;F12;F13;F14;F15;F16;F17;F18;F19;F20;F21;F22;F23;F24;F25)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Fun\u00e7\u00f5es utilizadas nesta planilha =ALEAT\u00d3RIOENTRE(1;F5) =ALEAT\u00d3RIOENTRE(1;F7) =\u00cdNDICE(C:C;CORRESP(J5;B:B;0)) =\u00cdNDICE(D:D;CORRESP(J12;B:B;0)) &nbsp; Fun\u00e7\u00e3o para fazer o la\u00e7o de repeti\u00e7\u00e3o Sub REPETIR() Dim v_ciclo As Integer v_ciclo1 = ThisWorkbook.Worksheets(&#8220;SORTEADOR&#8221;).Range(&#8220;F11&#8221;) For I = 1 To v_ciclo1 ThisWorkbook.Worksheets(&#8220;SORTEADOR&#8221;).Range(&#8220;H4&#8221;) = I Next End Sub &nbsp; &nbsp; &nbsp; &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"more-link\" href=\"https:\/\/jesquina.com.br\/site\/dicas-no-excel\/\"> <span class=\"screen-reader-text\">DICAS NO EXCEL<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-777","post","type-post","status-publish","format-standard","hentry","category-abertura"],"_links":{"self":[{"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/posts\/777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/comments?post=777"}],"version-history":[{"count":8,"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/posts\/777\/revisions"}],"predecessor-version":[{"id":790,"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/posts\/777\/revisions\/790"}],"wp:attachment":[{"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/media?parent=777"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/categories?post=777"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jesquina.com.br\/site\/wp-json\/wp\/v2\/tags?post=777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}