Separar valores em uma planilha cont.se e somase

Boa noite pessoal tudo bem com vocês, espero que sim, que Deus os abençoe com muita saúde e paz

hoje vou postar um exemplo de como usar a função CONT.SE e SOMASE lembrando que você pode usa-las separadamente e não necessariamente neste tipo de planilha ok

exemplo feito em EXCEL 2003

1º construa sua planilha como o exemplo da imagem abaixo:

contse img1

2º passo CONT.SE  agora é separar os itens de acordo com o que você digitar na pesquisa e a formula utilizada foi essa:

=CONT.SE($B$5:$H$17,J5) -> entenda o seguinte você mostra ao excel que  esta é uma fórmula usando o sinal de = na sequencia insere a função CONT.SE que irá buscar e contar os valores em um determinado local que você indicar usando os critérios que você digitar na célula J5

sintaxe: =CONT.SE(intervalo;critério)

muitos me perguntam porque os cifrões entre o nome da célula $B$5:$F$5 isso é uma referência absoluta e faz com que o excel não desça as células todas ao se arrastar uma fórmula gerando muitos erros, e para se fazer isso ao digitar a o nome da célula exemplo B5 e apertar F4 ai é só arrastar a fórmula para baixo e pronto.

contse img2

3º passo SOMASE agora vamos separar os valores que foi indicado na pesquisa e somar os totais para obtermos quanto que foi gasto em um departamento mas comprando itens diferentes

=SOMASE($B$5:$F$17,J5,$H$5:$H$17) -> entenda o seguinte você mostra ao excel que esta é uma fórmula usando o sinal de = na sequencia insere a função SOMASE ela ira buscar e somar os valores dentro do intervalo indicado usando o critério digitado e somando os totais de acordo com o critério.

sintaxe: =SOMASE(intervalo,critérios,intervalos_soma)  e leia o segundo passo para entender o porque dos cifrões ok

contse img3

muito bem agora é só digitar o que você quer e automaticamente o excel executa o que você quer veja que os campos de aplicação destas duas funções são muito grandes abaixo imagem da planilha funcionando.

contse img5

Até o próximo post com mais funções do excel e outros programas.

aqui você tem o exemplo acima para download do excel 2003 pra frente. contse e somase

Grt
DavidGois

 

Spread the word. Share this post!

132 Comments

  1. Keila

    Responder

    Olá td bem?
    Gostaria de saber se vc tem formula para cotação?
    Tipo, que ele me de o menor valor da linha, em amarelo ou negrito…
    Obrigada

  2. jose costa

    Responder

    =CONTAR.SE(E8:E40;”<E6") como posso fazer esta formula funcionar pois ela devolve sempre 0 se tirar " não funciona

  3. jose costa

    Responder

    alguem me diz como poso resolver este problema =CONTAR.SE(E8:E40;”<E6") como posso fazer esta formula funcionar pois ela devolve sempre 0

  4. Binha

    Responder

    Muito bom mesmo.
    Poderia me ajudar com uma formula?
    tenho 03 lojas – loja 01, loja 02, loja 03 , duas me oferecem o produto pelo mesmo valor, como faço para que apareça o nome das duas lojas vencedoras:
    Loja 01 Loja 02 Loja 03 Menor Preço Lojas Vencedoras
    R$ 10,00 R$ 10,00 R$ 20,00 R$ 10,00
    R$ 20,00 R$ 20,00 R$ 30,00 R$ 20,00
    aceito sugestões.

    Obrigada.

  5. Ismadsan

    Responder

    Quero fazer a seguinte condição, o aluno aprova se todas classicafições finais forem positivas.
    E se o aluno obtiver apenas duas negativas não importa o valor das negativas. Mas não podem serem em simultâneo Língua portuguesa e Matemática.

    De 5 à 10 positivas, menor que 5 negativas.

    OBS: reprova se obter mas de duas negativas, se forem em simultâneo Língua portuguesa e Matemática.

  6. Laís

    Responder

    Boa noite, existe alguma função no excel para dividir os dados de uma planilha em duas, estipulando uma definição? Por exemplo, coloco valores na primeira planilha e quero que automaticamente o excel divida os valores como acima de 100 e abaixo de 100 em planilhas separadas… caso sim, tem algum post sobre isso?

    Obrigada!!

  7. GISA

    Responder

    OIE, preciso de ajuda tomara encontrar aqui, preciso num diário de professor fazer o excel somar o F de falta pra mim como numero 1, me dando o total de faltas do aluno…gostaria muito de ajuda, por favor, obg.

  8. Responder

    I simply want to say I am very new to blogging and honestly savored your blog site. Likely I’m likely to bookmark your site . You definitely have impressive posts. Thanks a bunch for sharing with us your website page.

  9. Responder

    MetroClick specializes in building completely interactive products like Photo Booth for rental or sale, Touch Screen Kiosks, Large Touch Screen Displays , Monitors, Digital Signages and experiences. With our own hardware production facility and in-house software development teams, we are able to achieve the highest level of customization and versatility for Photo Booths, Touch Screen Kiosks, Touch Screen Monitors and Digital Signage. Visit MetroClick at http://www.metroclick.com/ or , 121 Varick St, New York, NY 10013, +1 646-843-0888

  10. Responder

    Faytech North America is a touch screen Manufacturer of both monitors and pcs. They specialize in the design, development, manufacturing and marketing of Capacitive touch screen, Resistive touch screen, Industrial touch screen, IP65 touch screen, touchscreen monitors and integrated touchscreen PCs. Contact them at http://www.faytech.us, 121 Varick Street, New York, NY 10013, +1 646 205 3214

  11. Responder

    I have been surfing on-line over a couple of a long time currently, on the other hand in no way observed almost any attention-grabbing write-up just like your own property. It is really wonderful price enough personally. For me personally, if perhaps virtually all website entrepreneurs and also web owners built exceptional content when you have, the internet are going to be much more very helpful than previously.. koleksi youtube indonesia

  12. Responder

    Your blog on Separar valores em uma planilha cont.se e somase – Informática gratuita is great. I hope u can continue delivering many lot article in the future. Long live informaticagratis.com.br

  13. Responder

    I’ve been having issues with my Windows hosting. It has set me back quite a bit while making the next list. This is the current list that I have. I should add another list in less than a week. I’ll let you all know when the next list is ready. Thank you for your patience.

  14. Responder

    Cool! Interesting info over this website. It is pretty worth enough for me. In my view, if all website owners and bloggers made good content as you did, the internet will be a lot more useful than ever before. I could not refrain from commenting. I’ve spent some time trying to find such informations. I will also share it with some friends interested in it. I have just bookmarked this website. Finished with the work done, I will watch some online gay cams. Thanks!! Greetings from Florida!

  15. Responder

    Cool one! Interesting tips over here. It is pretty worth enough for me. In my view, if all website owners and bloggers made good content as you did, the web will be a lot more useful than ever before. I couldn’t resist commenting. I have spent some time searching for such tips. I’ll also share it with some friends interested in it. I have just bookmarked this website. Finished with the job done, I will find some online hot gay cams. Gracias!!! Greetings from Los Angeles!

  16. Responder

    Very Nice post . Yes this this the right way for blog commenting . We should not do spamming through blog commenting . We should address the right information .

  17. Responder

    It’s really a nice and useful piece of information. I’m glad that you shared this helpful info with us. Please keep us up to date like this. Thank you for sharing.

  18. Responder

    obviously like your web site but you need to check the spelling on quite a few of your posts. A number of them are rife with spelling issues and I find it very troublesome to tell the truth nevertheless I will certainly come back again.

  19. Responder

    certainly like your website but you need to check the spelling on quite a few of your posts. A number of them are rife with spelling problems and I in finding it very bothersome to tell the truth on the other hand I’ll definitely come again again.

  20. Responder

    Paykasa, 16 haneli pin kodundan oluşan, daha önce çok yaygın olarak kullanılan bir ön ödemeli kart türüne benzeyen, içerisinde euro bakiyesi olan bir ön ödemeli sanal karttır. İçerisindeki bakiyeler özel olarak üretilemediği için standart kart bakiyeleri 10, 20, 50, 100, 150, 250 tek kart bakiyesi olarak satılmaktadırlar. Tek kullanımlık bir kart olan paykasa kart, kullanılan sitede kartın bakiyesini girmenize gerek kalmadan sadece 16 haneli tek kullanımlık koduyla işlem yapmanıza olanak sağlamaktadır.

  21. Responder

    Hello there, I discovered your blog by way of Google while searching for a comparable subject, your website came up, it appears great. I’ve bookmarked it in my google bookmarks.

  22. Responder

    Thanks for the sensible critique. Me and my neighbor were just preparing to do some research about this. We got a grab a book from our local library but I think I learned more from this post. I’m very glad to see such great info being shared freely out there.

  23. Responder

    This design is spectacular! You certainly know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job. I really loved what you had to say, and more than that, how you presented it. Too cool!

  24. Responder

    Hey! I know this is kinda off topic but I’d figured I’d ask. Would you be interested in exchanging links or maybe guest writing a blog article or vice-versa? My site covers a lot of the same topics as yours and I believe we could greatly benefit from each other. If you happen to be interested feel free to send me an email. I look forward to hearing from you! Superb blog by the way!

  25. Responder

    My partner and I stumbled over here coming from a different web page and thought I should check things out.
    I like what I see so i amm just following you. Look forward
    to looking at your web pag repeatedly.

  26. Responder

    My spouse and i got ecstatic Peter managed to finish off his survey from the ideas he received in your web page. It is now and again perplexing just to happen to be giving out tips and hints men and women might have been selling. So we know we have you to appreciate because of that. The entire illustrations you’ve made, the easy site menu, the friendships you make it possible to engender – it’s got most astonishing, and it’s assisting our son and our family do think that topic is pleasurable, which is unbelievably fundamental. Thanks for all the pieces!

  27. Responder

    Hi there, You’ve done an incredible job. I will definitely digg it and personally recommend to my friends. I am sure they’ll be benefited from this web site.

  28. Responder

    Great goods from you, man. I have understand your stuff previous to and you’re just too magnificent. I actually like what you’ve acquired here, really like what you’re stating and the way in which you say it. You make it entertaining and you still care for to keep it wise. I can’t wait to read much more from you. This is actually a wonderful web site.

  29. Responder

    You actually make it seem so easy with your presentation but I in finding this topic to be actually something which I feel I would by no means understand. It kind of feels too complex and extremely large for me. I am taking a look forward for your subsequent put up, I will try to get the hang of it!

  30. Responder

    You actually make it appear really easy together with your presentation however I to find this topic to be actually something which I feel I’d by no means understand. It sort of feels too complicated and very huge for me. I’m taking a look ahead to your next submit, I¡¦ll try to get the dangle of it!

  31. Responder

    I really like your blog.. very nice colors & theme. Didd you make this website yourself or did you hire someokne to
    do it foor you? Plz respond as I’m looking to construct my own blog and would like to
    find out where u goot this from. thank you

  32. Responder

    I would like to thqnk youu for the eforts you have put in writing this blog.
    I really hope to check out the same high-grade content by you later on as
    well. In truth, your creative writing abilities has inspired me tto get
    my very own wesite noow 😉

  33. Responder

    Enjoyed examining this, very good stuff, appreciate it. “It is well to remember that the entire universe, with one trifling exception, is composed of others.” by John Andrew Holmes.

  34. JAV

    Responder

    Hello, Neat post. There is a problem with your web site in web explorer, could check this… IE still is the market chief and a big component to people will omit your magnificent writing due to this problem.

  35. Responder

    I’m really impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you modify it yourself? Anyway keep up the nice quality writing, it’s rare to see a nice blog like this one today..

  36. Responder

    It’s truly a great and helpful piece of info.
    I’m satisfied that yoou just shared thius helpful info withh
    us. Please stayy us informed like this. Thank you for sharing.

  37. Responder

    certainly like your web site however you need to check the spelling on quite a few of your posts. A number of them are rife with spelling problems and I in finding it very bothersome to inform the reality nevertheless I will definitely come again again.

  38. Responder

    You actually make it seem so easy with your presentation but I find this matter to be actually something which I think I would never understand. It seems too complex and extremely broad for me. I am looking forward for your next post, I will try to get the hang of it!

  39. Responder

    Thanks for your personal marvelous posting! I certainly enjoyed reading it,
    you may be a great author. I will remeember to bookmark your blog and definitely will come back at
    some point. I wwant to encourage continue your great posts, have a
    nice afternoon!

  40. Responder

    Hello very nice site!! Man .. Beautiful .. Amazing .. I will bookmark your web site and take the feeds also…I am happy to find numerous helpful information right here in the put up, we’d like develop more strategies in this regard, thank you for sharing.

  41. Responder

    I have not checked in here for a while since I thought it was getting boring, but the last few posts are good quality so I guess I’ll add you back to my everyday bloglist. You deserve it my friend 🙂

  42. Responder

    I have recently started a blog, the info you offer on this website has helped me greatly. Thank you for all of your time & work. “The man who fights for his fellow-man is a better man than the one who fights for himself.” by Clarence Darrow.

  43. Responder

    It’s the best time to make some plans for the future and it is time to be happy. I have read this post and if I could I wish to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I wish to read even more things about it!

  44. sa

    Responder

    I’ve just added a fresh new list. This is by far the biggest list to date. I hope you all are having a great week. Take care and happy link building.

  45. Responder

    Undeniably consider that which you stated. Your favorite justification appeared to be on the web the easiest thing to understand of. I say to you, I certainly get annoyed while other folks think about issues that they just don’t realize about. You managed to hit the nail upon the top and also outlined out the entire thing without having side effect , other folks could take a signal. Will likely be back to get more. Thanks

  46. Responder

    Good day! I know this is kinda off topic however , I’d figured I’d ask. Would you be interested in trading links or maybe guest authoring a blog post or vice-versa? My website addresses a lot of the same subjects as yours and I feel we could greatly benefit from each other. If you happen to be interested feel free to shoot me an email. I look forward to hearing from you! Terrific blog by the way!

  47. Responder

    I’ll immediately clutch your rss feed as I can’t find your email subscription link or newsletter service. Do you’ve any? Please let me know so that I may subscribe. Thanks.

  48. Responder

    Wonderful work! This is the type of information that should be shared around the web. Shame on Google for not positioning this post higher! Come on over and visit my site . Thanks =)

  49. Responder

    Pretty nice post. I just stumbled upon your blog and wished to say that I’ve truly enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again very soon!

  50. Responder

    Cool info! Interesting informations over here. It is pretty worth enough for me. In my view, if all web owners and bloggers made good content as you did, the web will be much more useful than ever before. I could not refrain from commenting. I ‘ve spent some time searching for such infos. I will also share it with a couple of friends interested in it. I’ve just bookmarked this website. Right now with the task done, I going to enjoy some live gay cams. Thank you very much!! Greetings from Phoenix!

  51. Responder

    Thank you for another informative web site. Where else could I get that type of info written in such a perfect way? I have a project that I am just now working on, and I have been on the look out for such info.

  52. Responder

    I found your weblog site on google and verify just a few of your early posts. Continue to maintain up the superb operate. I simply extra up your RSS feed to my MSN Information Reader. Seeking forward to reading extra from you afterward!…

  53. Responder

    Someone essentially help to make seriously posts I would state. This is the very first time I frequented your website page and thus far? I amazed with the research you made to create this particular publish extraordinary. Magnificent job!

  54. Responder

    I do trust all the ideas you’ve presented in your post. They’re very convincing and will definitely work. Still, the posts are very brief for newbies. May just you please lengthen them a little from subsequent time? Thank you for the post.

  55. Responder

    Now you can buy free viagra and custom printed adult tapes online. We are the UK’s leading adult toy manufacturer. UKPRINTEDTAPE.CO.UK adult chat and dating .

  56. Responder

    Cool! Interesting information over here. It is pretty worth enough for me. Personally, if all web owners and bloggers made good content as you did, the internet will be a lot more useful than ever before. I couldn’t resist commenting. I have spent 1 hour searching for such tips. I’ll also share it with some friends interested in it. I have just bookmarked this website. Now with the search done, I going to find some online shemale cams. Gracias!!! Greetings from Boston!

  57. Responder

    I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you? Plz answer back as I’m looking to construct my own blog and would like to find out where u got this from. thank you

  58. Responder

    I just like the helpful info you supply in your articles. I will bookmark your blog and test again right here regularly. I’m quite certain I will learn a lot of new stuff right right here! Good luck for the next!

  59. Responder

    you are in point of fact a good webmaster. The site loading velocity is incredible. It sort of feels that you are doing any distinctive trick. Furthermore, The contents are masterpiece. you’ve done a magnificent task on this subject!

  60. Responder

    Can I just say what a reduction to seek out somebody who truly is aware of what theyre speaking about on the internet. You undoubtedly know learn how to convey an issue to mild and make it important. More people need to read this and perceive this aspect of the story. I cant believe youre no more well-liked since you undoubtedly have the gift.

  61. Responder

    Hello There. I found your blog using msn. This is a really well written article. I’ll be sure to bookmark it and return to read more of your useful info. Thanks for the post. I will certainly return.

  62. Responder

    Hello would you mind stating which blog platform you’re using? I’m going to start my own blog soon but I’m having a difficult time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I’m looking for something completely unique. P.S Apologies for being off-topic but I had to ask!

  63. Responder

    My spouse and I totally like your blog and find a lot of your articles to be precisely what I am interested in. Do you offer people to write material for you? I wouldn’t mind composing an article relating to luck patcher apk or on some of the things you write about on this site. Awesome information site!

  64. Responder

    I keep listening to the newscast talk about receiving free online grant applications so I have been looking around for the top site to get one. Could you tell me please, where could i get some?

  65. Responder

    seo uzmanı olarak şirketimiz php seo hizmeti vermektedir günümüz ortamında site sahiplerinin php bilgisi nekadar iyi olsada php seo metotları arama motorlarında optimize edilmesi gereken hususlardan bir tanesi web siteniz nekadar iyi olsada arama motorlarına duyarlı olmasına özen gösterin. php seo uzmanlarımızdan destek alabilirsiniz sitenizi aramotorlarına duyarlı hale geçirelim.

  66. Responder

    You are my intake , I have few blogs and often run out from to brand.I conceive this internet site contains some rattling wonderful information for everyone. “Variety is the soul of pleasure.” by Aphra Behn.

  67. Responder

    I’ve been browsing online more than three hours lately, yet I never discovered any interesting article like yours. It is lovely price sufficient for me. In my view, if all webmasters and bloggers made good content as you did, the internet will probably be much more helpful than ever before.

  68. Responder

    Great post. I was checking continuously this blog and I am impressed! Extremely helpful information specifically the last part 🙂 I care for such information a lot. I was looking for this particular info for a long time. Thank you and good luck.

  69. Responder

    Excellent post. I was checking constantly this blog and I’m impressed! Very useful information specifically the last part 🙂 I care for such info much. I was seeking this certain information for a very long time. Thank you and best of luck.

  70. Responder

    Pretty nice post. I just stumbled upon your blog and wanted to say that I have truly enjoyed surfing around your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

  71. Responder

    Hi there, simply become alert to your weblog through Google, and located that it’s really informative. I’m going to watch out for brussels. I’ll appreciate should you proceed this in future. Lots of people will be benefited out of your writing. Cheers!

  72. Responder

    I simply couldn’t depart your site before suggesting that I really loved the usual information an individual provide for your visitors? Is gonna be again often to check out new posts.

  73. Responder

    magnificent put up, very informative. I’m wondering why the opposite experts of this sector don’t realize this. You must proceed your writing. I am sure, you’ve a huge readers’ base already!

  74. Responder

    I have been browsing online more than three hours lately, but I never found any attention-grabbing article like yours. It is beautiful price enough for me. In my view, if all site owners and bloggers made excellent content as you did, the internet might be a lot more useful than ever before.

  75. Responder

    You actually make it seem so easy with your presentation but I find this matter to be really something which I think I would never understand. It seems too complicated and extremely broad for me. I am looking forward for your next post, I will try to get the hang of it!

  76. Responder

    You really make it seem so easy along with your presentation but I to find this topic to be really something that I believe I would never understand. It seems too complex and very large for me. I’m looking forward to your next put up, I will try to get the cling of it!

  77. Responder

    hello!,I like your writing so so much! share we keep in touch more about your article on AOL? I require a specialist in this space to solve my problem. May be that’s you! Looking ahead to look you.

  78. Responder

    It’s a shame you don’t have a donate button! I’d certainly donate to this outstanding blog! I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to fresh updates and will share this website with my Facebook group. Chat soon!

Leave Comment

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *