Билл је поставио овонедељно питање о сувишним Екцел подацима.
У Екцел-у правим месечну листу трансакција. На крају месеца морам да елиминишем сувишне податке и да смислим укупан број по коду рачуна. Сваки код рачуна може се појавити више пута. Билл је потом описао своју тренутну Екцел методологију која је слична методи 1 у наставку како би дошао до јединствене листе кодова рачуна, са плановима да користи матрицу ЦСЕ формула за добијање укупних износа. Пита, постоји ли лакши начин да се дође до јединствене листе кодова рачуна са укупним бројевима за сваки рачун?
Ово је савршено питање за одмор. Будући да сам Лотусов корисник већ 15 година, препознајем Биллов метод као класичну методу за „брзу и прљаву“ манипулацију подацима из добрих старих времена Лотус-овог издања 2.1. Ово је сезона за рачунање наших благослова. Када размислите о овом питању, схватите да су нам људи из Мицрософта током година заиста поклонили бројне алате. Ако користите Екцел 97, постоји најмање пет метода за обављање овог задатка, а све су далеко лакше од класичне методе коју је описао Билл. Ове недеље ћу понудити лекцију о пет метода.
Мој поједностављени скуп података садржи бројеве рачуна у колони А и износе у колони Б. Подаци се крећу од А2: Б100. Није сортирано на почетку.
Метод 1
Да бисте пронашли одговор, користите изјаве Цреативе иф у комбинацији са Пасте Специал Валуес.
С обзиром на новије алате које нуди Екцел, више не препоручујем овај метод. Некада сам ово често користио пре него што су се појавиле боље ствари, али још увек постоје ситуације када то дође у обзир. Моје алтернативно име за ово је метода „Тхе-Лотус-123-Вхен-Иоу-Ве-Нот-Ин-Тхе-Моод-То-Усе-@ ДСУМ“. Ево корака.
- Подредите податке по колони А.
- Измислите формулу у колони Ц која ће задржати текући укупан износ по рачуну. Ћелија Ц2 је
=IF(A2=A1,C1+B2,B2). - Изумите формулу у Д која ће идентификовати последњи унос за одређени рачун. Ћелија Д2 је
=IF(A2=A3,FALSE,TRUE). - Копирајте Ц2: Д2 у све ваше редове.
- Копија Ц2: Д100. Вратите Едит - ПастеСпециал - Вредности назад на Ц2: Д100 да бисте променили формуле у вредности.
- Поредај по колони Д силазно.
- За редове који имају ИСТИНУ у колони Д имате јединствену листу бројева рачуна у А, а коначни укупни укупни број у Ц.
Прос: Брзо је. Све што вам треба је оштар осећај за писање ИФ изјава.
Против: Постоје бољи начини.
Метод 2
Користите филтер података - напредни филтер да бисте добили листу јединствених рачуна.
Биллово питање је заиста било како доћи до јединствене листе бројева рачуна како би могао да користи ЦСЕ формуле за добијање укупних износа. Ово је метода за добијање листе јединствених бројева рачуна.
- Истакните А1: А100
- У менију одаберите Подаци, Филтер, Напредни филтер
- Кликните радио дугме за „Копирај на друго место“.
- Потврдите избор у пољу за потврду „Само јединствени записи“.
- Изаберите празан одељак радног листа на којем желите да се појави јединствена листа. Унесите ово у поље „Копирај у:“. (Имајте на уму да је ово поље сиво док не одаберете „Копирај на другу локацију“.
- Кликните ОК. Јединствени бројеви рачуна ће се појавити у Ф1.
- Унесите било које низводне манипулације, формуле низова итд. Да бисте добили резултате.
Прос: Бржи од метода 1. Није потребно сортирање.
Против: Након овог потребне ЦСЕ формуле завртјет ће вам се у глави.
Метод 3
Користите Дата Цонсолидате.
Мој квалитет живота се побољшао када је Екцел понудио Дата Цонсолидате. Ово је било ВЕЛИКО! Потребно је 30 секунди да га подесите, али пише смрт за ДСУМ-ове и друге методе. Број вашег рачуна мора бити лево од нумеричких поља која желите да збројите. Изнад сваке колоне морате да имате наслове. Правоугаоном блоку ћелија који укључује бројеве рачуна дуж леве колоне и наслове на врху треба да доделите име опсега. У овом случају, тај опсег је А1: Б100.
- Истакните А1: Б100
- Доделите име опсега овој области тако што ћете кликнути на поље са именом (лево од траке са формулама) и откуцати име као што је „ТоталМе“. (Алтернативно користите Инсерт - Наме).
- Ставите показивач ћелије у празан одељак радног листа.
- Изаберите податке - обједините
- У поље за референцу откуцајте име опсега (ТоталМе).
- У одељку Усе Лабелс Ин означите горњи ред и леву колону.
- Кликните ОК
Прос: Ово је моја омиљена метода. Није потребно сортирање. Пречица је алт-Д Н (назив домета) алт-Т алт-Л ентер. Лако је скалабилан. Ако ваш опсег укључује 12 месечних колона, одговор ће имати укупне вредности за сваки месец.
Против: Ако на истом листу направите још једну консолидацију података, потребно је да уклоните старо име опсега из поља Све референце помоћу дугмета Делете. Број рачуна мора бити лево од ваших нумеричких података. Нешто је спорији од пивот табела што постаје уочљиво за скупове података са преко 10 000 записа.
Метод 4
Користите међузбир података.
Ово је сјајна карактеристика. Пошто је са резултујућим подацима необично радити, користим их ређе од Дата Цонсолидате.
- Поредај по колони А узлазно.
- Изаберите било коју ћелију у опсегу података.
- Изаберите Дата - Субтоталс из менија.
- Екцел подразумевано нуди укупан збир последње колоне ваших података. Ово делује у овом примеру, али често морате да листате кроз листу „Додај укупан зброј у:“ да бисте изабрали тачна поља.
- Кликните ОК. Екцел ће уметнути нови ред при свакој промени броја рачуна са укупним бројем.
Након што унесете међузбројеве, видећете мали 123 који се појављује испод оквира са именом. Кликните на 2 да бисте видели само један ред по рачуну са укупним бројевима. Да бисте објаснили посебне кораке потребне за њихово копирање на ново место, прочитајте Копирај Екцел подзбројеве. Кликните на 3 да бисте видели све редове. Прос: Цоол Феатуре. Одлично за штампање извештаја са укупним бројевима и преломима страница након сваког одељка.
Против: Прво се морају сортирати подаци. Успорите за пуно података. Морате да користите Гото-Специал-ВисбилеЦеллсОнли да бисте добили укупан број негде другде. Морате да користите Дата-Субтоталс-РемовеАлл да бисте се вратили изворним подацима.
5. метод
Користите пивот табелу.
Пивот табеле су најсвестраније од свих. Ваши подаци не морају бити сортирани. Нумеричке колоне могу бити лево или десно од броја рачуна. Бројеви рачуна могу се лако спустити или прећи преко странице.
- Изаберите било коју ћелију у опсегу података.
- У менију одаберите Подаци - заокретна табела.
- Прихватите подразумеване вредности у кораку 1
- Уверите се да је опсег података у кораку 2 тачан (обично јесте)
- Ако користите Екцел 2000, кликните на тастер Лаиоут у кораку 3. Корисници програма Екцел 95 и 97 аутоматски прелазе на изглед као корак 3.
- У дијалогу изгледа превуците дугме Рачун са десне стране дијалога и испустите га у подручје Ред.
- Повуците дугме Количина са десне стране дијалога и испустите га у област Подаци.
- Корисници програма Екцел 2000 кликните на дугме У реду, корисници програма Екцел 95/97 кликните на дугме Даље.
- Наведите да ли желите резултате у новом листу или у одређеном одељку постојећег листа. Прочитајте више о изведеним табелама у напредним триковима програма Екцел Пивот Табле.
- Пивот табеле нуде невероватну функционалност и чине овај задатак брзим. Да бисте копирали резултате пивот табеле, потребно је да урадите Едит-ПастеСпециал-Валуес, у супротном Екцел неће дозволити уметање редова итд.
Прос: Брз, флексибилан, моћан. Брзо, чак и за пуно података.
Цонс: Донекле застрашујуће.
Билл сада има четири нове методе за уклањање сувишних података. Иако ове методе нису биле доступне од почетка времена, и Лотус и Екцел су били сјајни иноватори који су нам донели брже начине да извршимо овај свакодневни задатак.
