КАТЕГОРИИ:


Астрономия- (809) Биология- (7483) Биотехнологии- (1457) Военное дело- (14632) Высокие технологии- (1363) География- (913) Геология- (1438) Государство- (451) Демография- (1065) Дом- (47672) Журналистика и СМИ- (912) Изобретательство- (14524) Иностранные языки- (4268) Информатика- (17799) Искусство- (1338) История- (13644) Компьютеры- (11121) Косметика- (55) Кулинария- (373) Культура- (8427) Лингвистика- (374) Литература- (1642) Маркетинг- (23702) Математика- (16968) Машиностроение- (1700) Медицина- (12668) Менеджмент- (24684) Механика- (15423) Науковедение- (506) Образование- (11852) Охрана труда- (3308) Педагогика- (5571) П Arhitektura- (3434) Astronomiya- (809) Biologiya- (7483) Biotehnologii- (1457) Военно дело (14632) Висока технологиите (1363) Geografiya- (913) Geologiya- (1438) на държавата (451) Demografiya- ( 1065) Къщи- (47672) журналистика и SMI- (912) Izobretatelstvo- (14524) на външните >(4268) Informatika- (17799) Iskusstvo- (1338) История- (13644) Компютри- (11121) Kosmetika- (55) Kulinariya- (373) култура (8427) Lingvistika- (374) Literatura- (1642) маркетинг-(23,702) Matematika- (16,968) инженерно (1700) медицина-(12,668) Management- (24,684) Mehanika- (15423) Naukovedenie- (506) образование-(11,852) защита truda- (3308) Pedagogika- (5571) п Политика- (7869) Право- (5454) Приборостроение- (1369) Программирование- (2801) Производство- (97182) Промышленность- (8706) Психология- (18388) Религия- (3217) Связь- (10668) Сельское хозяйство- (299) Социология- (6455) Спорт- (42831) Строительство- (4793) Торговля- (5050) Транспорт- (2929) Туризм- (1568) Физика- (3942) Философия- (17015) Финансы- (26596) Химия- (22929) Экология- (12095) Экономика- (9961) Электроника- (8441) Электротехника- (4623) Энергетика- (12629) Юриспруденция- (1492) Ядерная техника- (1748) oligrafiya- (1312) Politika- (7869) Лево- (5454) Priborostroenie- (1369) Programmirovanie- (2801) производствено (97182) от промишлеността (8706) Psihologiya- (18,388) Religiya- (3217) с комуникацията (10668) Agriculture- (299) Sotsiologiya- (6455) спортно-(42,831) Изграждане, (4793) Torgovlya- (5050) превозът (2929) Turizm- (1568) физик (3942) Filosofiya- (17015) Finansy- (26596 ) химия (22929) Ekologiya- (12095) Ekonomika- (9961) Telephones- (8441) Elektrotehnika- (4623) Мощност инженерно (12629) Yurisprudentsiya- (1492) ядрена technics- (1748)

Добавяне към списъка с междинни суми




COUNTIF () функция и SUMIF ().

Тема 2. Използване на функции към списъка с анализ на данни Microsoft Excel

1. COUNTIF () и SUMIF ().

2. Функции за работа с база данни.

3. Използване на функциите за категория Връзки и масиви за анализ на списъка.

За анализ на данните в списъка за Microsoft Excel предлага специални функции: функция на връзките към категориите, както и масиви, функцията работи с база данни, функция COUNTIF статистическа () и математическа функция SUMIF (). Всяка една от тези функции връща информация за списъка на елементи, които отговарят на определени условия.

COUNTIF () функция, а SUMIF () е по-лесно за използване от други функции за манипулиране на списъци. Тъй като те позволяват да се определят условията, директно във формулата. Недостатъкът на тези функции е възможността да се използват само прости условия.

COUNTIF () функция определя броя на непразни клетки в даден диапазон, които отговарят на критериите. Нейният синтаксис е следния:

COUNTIF (тест обхват)

където диапазон - от порядъка (при анализа на данните в списъци, обикновено, колона), в който да брои броя на ценности и критерий - стойност текст посочва състояние.

Трябва да се помни, че състоянието на стърчащата критерий броене се прилага само към обхвата, в който се произвежда броя.

Например, функцията на преброяване на броя на заетите на възраст над 25 години (.. виж фигура 1.6) е както следва:

= COUNTIF (H5: Н17; "> 25"),

SUMIF () функция събира стойностите в клетките, които отговарят на определени критерии. Функцията има следния синтаксис:

SUMIF (критерий гама; общ_сбор_диапазон)

където диапазон аргумент определя диапазона на проверяваната критерий - състояние, което трябва да бъде изпълнено във всяка клетка на този диапазон, и се прилагат към общ_сбор_диапазон - дефинира набор от стойности се сумират.

Например, функцията на преброяване на общия заплата, получена от служители на възраст над 25 години (.. виж фигура 1.6) е както следва:

= SUMIF (H5: Н17; "> 25"; G5: Г17).

2.2. Функция работи с базата данни.

Функция работи с база данни (Dfunktsii) са аналогични на "нормалните" статистически функции. Разликата е, че функциите на бази данни работят само с набор от елементи, които отговарят на определени условия. Всички Dfunktsii има следния синтаксис:

Dfunktsiya (data_base; област; тест)

където базата данни определя границите на целия списък; поле - определя колоната, на която се извършват изчисленията (сумиране, средно и др ...); третият аргумент определя критерий гама от условия.

Вторият аргумент може да се определи като текстова стойност на заглавието на колоната, ограден в кавички или сериен номер на колоната в списъка.



Третият аргумент е зададен критерий като разширен набор от условия на филтри (вж. Фиг. 1.3), обхватът на условия, при създаването на разширен филтър трябва да бъде настроен да се създаде формула, която използва функция база данни.

функции за база по-трудно да се използва от SUMIF () и COUNTIF (), тъй като те изискват предварителна настройка диапазон от условия, но, както бе споменато по-рано, Dfunktsii () ви позволяват да определите по-сложни условия.

Разполагате с няколко прости функции в тази категория.

DSUM () функция суми, отговарящи на определени условия на колона от списъка. Действия на тази функция, са подобни на COUNTIF (функция).

DMAKS функция () - връща най-голям брой в колона на списъка, който отговаря на предварително определени условия.

DMIN () функция - Връща най-малкото число в колона от списък или база данни, които съответстват на посочените условия.

DSRZNACH () функция - Връща средната стойност на списъка на избраните записи. Например, в списъка е показано на фиг. 2.1, за изчисляване на средните заплати на служителите на възраст над 25 години, се използва формулата:

= DSRZNACH (А4: Н17; "заплата" А1: A2).

Фиг. 2.1. Използване DSRZNACH () функция

BSCHETA функция () - брои не е празна клетка в списъка на колони или данни, които отговарят на определени критерии.

Следните наблюдения трябва да бъдат взети под внимание при работа с функции за база:

§ ако искате да намерите редове, които отговарят на няколко критерия за една колона, от гледна точка на обхвата, който искате да включите няколко колони с едно и също заглавие;

§ ако резултатите от изчисление на функцията са поставени на отделен лист, както и за различните условия трябва да са на една и съща листа.

2.3. Използване на функции и масиви категория Връзки
за анализ на списъка.

За да се анализират данните в списъка могат да бъдат използвани в категориите, функционални и връзки масиви, те се наричат функции и връзки смяна. Тези функции се използват за различни стойности за търсене в списъци.

Често желана функция на тази категория е функция MATCH () връща позицията на член на банда, най-подходящата желаната стойност. Тази функция има следния синтаксис:

MATCH (lookup_value prosmatrivaemyy_diapazon; match_type)

Value_to_be_located аргумент - това е оригиналната желаната стойност, която се търси в масив_търсене диапазон.

Match_type аргумент търсене уточнява принцип и може да отнеме стойностите -1, 0 или 1. Match_type уточнява как Microsoft Excel съвпада с lookup_value със стойности, като този диапазон се гледа.


Ако match_type е 1 или пропуснат, MATCH () функция намира най-близо в prosmatrivaemom_diapazone че е по-малко от или равно на желания заглавието. Така prosmatrivaemyy_diapazon трябва да бъдат подредени във възходящ ред: ... -2, -1, 0, 1, 2, ... A-Z, фалшиви, вярно (виж Фигура 2.2 ..).

Фиг. 2.2. MATCH () функция открива най-близкия елемент
диапазон на по-малко от 24

Ако match_type е 0, MATCH () открива първата стойност, която е равна точно аргумент iskmoe_znachenie на. В този случай, prosmatrivaemyy_diapazon не могат да бъдат сортирани.


Ако match_type е -1, MATCH () функция намира най-близката стойност, която е по-голяма от или равна на желаната iskomomu.Pri prosmatrivaemyy_diapazon това трябва да се подредени по низходящ: вярно, фалшиви, Z-A ... 2, 1, 0, -1, -2 ... и така нататък (вж. фиг. 2.3).

Фиг. 2.3. MATCH () функция открива най-близкия елемент
набор от повече от 24

Когато се работи с MATCH () функция трябва да се обърне внимание на редица функции, от тяхното ползване:

§ Ако MATCH () намира съответната стойност, той се връща стойност грешка # N / A ( "Неопределен данни").

§ Когато използвате функцията MATCH (), за да се определи позицията на текстови низове, отговарящи на типа, обикновено се задава с 0.

§ Ако match_type е 0 и търсена_стойност текстови, тя може да съдържа заместващи символи (* или?).

VIEW () връща стойността на ред или колона на матрицата и две от синтактични форми: вектора и масива.

Той обикновено се използва вектор форма. В тяхната векторна форма LOOKUP () - е един ред или една колона голямото сравнение стойност не повече от желаната, и връща стойността на съответната позиция на друг ред или колона. Стойностите в обхвата на търсене, за да се сортират във възходящ ред.

Вектор форма функция има следния синтаксис:

ГЛЕДАНЕ (lookup_value, lookup_vector;
vektor_rezultatov).

lookup_value Аргументът - тази стойност kotoroefunktsiya VIEW () търси в prosmatrivaemom_vektore. Value_to_be_located може да бъде число, текст, логическа стойност, името или номера.

Lookup_vector аргумент - диапазон, който съдържа само един ред или колона. Стойностите в аргумент Lookup_vector може да бъде текст, числа или логически стойности.

Стойностите в аргумент lookup_vector трябва да бъдат подредени във възходящ ред: ... -2, -1, 0, 1, 2, ..., A-Z, фалшиви, вярно; в противен случай, LOOKUP () може да се върне неправилен резултат. Текстовете в долната и горната случай се считат за еквивалентни.

Vektor_rezultatov аргумент - този интервал, съдържащ само един ред или една колона, от която ще бъде върната стойност. Този диапазон трябва да имат един и същ брой елементи като Lookup_vector.

Когато се работи с функцията за VIEW () трябва да се обърне внимание на редица функции, от тяхното ползване:

§ Ако LOOKUP () не може да намери lookup_value, е установено, че най-близката стойност, която е по-малко от желаното в lookup_vector;

§ Ако търсена_стойност е по-малка от най-малката стойност в аргумент Lookup_vector на функцията LOOKUP () връща стойността за грешка # N / A.

Вторият синтаксис форма на LOOKUP () функция, предназначена да работи с масиви, тя има следния синтаксис:

ГЛЕДАНЕ (lookup_value, масив).

Тази функция форма VIEW () изглежда в първия ред или първата колона на масива, е определена стойност и връща стойност от същата позиция на последния ред или колона на масива. За да се определи местоположението на сравняваните стойности (колона или ред) функция използва таблица измерение.

Тази форма на LOOKUP е предназначена за съвместимост с други системи, таблици. В случай на необходимост от този вид търсене е по-добре, вместо да се образува VIEW (функция), предназначен за работа с масиви, да се използва по-предсказуем HLOOKUP () или PPS ().

WRT () функция изглежда на стойността в лявата колона на таблицата и връща стойността в същия ред на споменатата маса колона (вж. Фиг. 2.4). Функцията има следния синтаксис:

CDF (lookup_value diapazon_spiska; колона_номер;
range_lookup)

Фиг. 2.4. Използване на функцията VLOOKUP (), за да търсите
позиционира Бортников

lookup_value Аргументът - е стойността, която се намира в първата колона на таблицата. Value_to_be_located може да бъде число, препратка към клетка или текстов низ, затворени в кавички.

Diapazon_spiska аргумент - списък, в който за избиране на данни.

Колона_номер аргумент - това е номера на колоната в диапазона от списък, в който да бъде намерена подходящата стойност. Ако колона_номер е 1, стойността се върна от първия аргумент колона diapazon_spiska; ако колона_номер е 2, стойността се върна от втората колона. В нашия пример (вж. Фиг. 2.4), това твърдение е 6, броят на колона от функция.

Range_lookup аргумент - това е булева стойност, която определя вида на съответствието: точна или приблизителна.

· Ако range_lookup верен, VLOOKUP () търси в първата колона на таблицата iskomomu_znacheniyu точно съвпадение. Ако не се намери такъв, той връща стойността за грешка # N / A. D не iapazon_spiska длъжен да бъдат сортирани.

· Ако range_lookup аргумент е TRUE или пропуснато, при липсата на точно съвпадение, той се връща най-близкия стойност е по-малка от lookup_value. Стойностите в първия range_lookup аргумент колона трябва да са подредени във възходящ ред, в противен случай VLOOKUP може да даде грешен резултат.

PGR () функция е идентична функция CDF (), с изключение, че работи с се използва вертикална маса ако данните не са подредени в колони и редове в (вж. Фиг. 2.5).

Фиг. 2.5. Използването на PGR () функция за търсене на плащане Бортников
произведени през април

Функцията има следния синтаксис:

HPR (lookup_value info_tablitsa; ред_номер;
range_lookup).

PGR () погледнете в горния ред на таблицата и връща стойността в същата колона на даден ред на таблицата или масив. функция PGR () се използва, когато сравняваните стойности са в горния ред на таблицата за данни и стойности за връщане са подредени в няколко под термина. По подразбиране, преди да използвате HLOOKUP (), списъкът трябва да бъдат сортирани.

Друга особеност на интерес за анализ на данните в списъците е функция INDEX (). INDEX () функция е подобна на BROWSE () функция има две форми.

Първата форма на INDEX функция (), с следния синтаксис:

INDEX (списък; ред_номер; колона_номер)

Тя връща списък позиция, предварително определен брой редове и колони номер. Например, с помощта на примера на фигура 2.4, формулата се използва за намиране стойности на четвърти ред от шестата колона:

INDEX = (А2: H7; 4; 6),

който ще върне стойност на "учител".

две функции могат да се използват за търсене на стойности в списъка в същото време: MATCH (), който определя броя (евентуално номера на колона) елемент в списъка и индекса (), която връща стойността на елемента в пресечната точка на посочения ред и колона.

Вторият синтактичен формата на функцията INDEX () връща адреса на клетката. Полезно е в случаите, когато трябва да се извършва операции върху клетките (например промяна на ширината на клетката), но не и с неговите ценности. Ако тя се използва като вградена, външната функция може да използвате някои от функциите на адреса. В допълнение, индексът позоваване форма функция () не показва резултатът като адрес, и извежда стойност (и), съдържаща се в този адрес. Важно е да се помни, че в резултат на индексната функция е точно адреса, дори и ако резултатът е различен.

Въпроси за самоконтрол.

1. Какво отличава в Microsoft Excel се използва за анализ на данните в списъка?

2. Какви са характеристиките на използването на функции на базата данни?

3. Действия, които функции на базата данни са сходни с SUMIF () и COUNTIF ()?

4. Какви са аргументите на функции за бази данни.

5. Каква е разликата между използването на функции на базата данни и подобни статистически функции?

6. Използване на изчислена и не изчислена по отношение на функции на базата данни.

7. Използване на логически оператори и и или условия в диапазона от функции за бази данни.

8. Използване на функциите за търсене и връзки масиви за търсене на данни в списъка.

Въпроси и задачи за самостоятелна работа.

1. Сравнете използването на съвременни филтри и функции на базата данни.

2. Определяне на разликата на използване BSCHET () и BSCHETA ().

3. Определяне на функцията на цел BIZVLECH ().

4. Създаване на нова Zadacha_2 книга, в която да копирате на работниците и служителите на маса (база 1 лаборатория 1.1).

5. списък Retreat по-долу и добавете клетъчна линия

По-стари от 33 години

6. Използване на функциите за COUNTIF (), за да се изчисли броят на заетите лица над 33 години.

7. На отделен лист за създаване на две таблици:

отдел номер офис номер общата работна заплата
изпълнение мениджър
захранване глава
контрол одитор
в общия в общия

8. Попълнете новосъздадената таблица:

1 етап. С COUNTIF функция (), за да се определи броят на служителите, работещи в всеки отдел (тук се използва като критерий за относителна препратката към клетка на картата име).

Етап 2. С COUNTIF функция (), за да се определи броят на заетите лица работят по всеки един от постовете (критерий аналогично дефинирани стъпка 1).

9. Изчислете обема на изплатените заплати (общо областта на възлагане заплата Таблица 4) за всички постове на организацията.

10. По-долу е даден списък с добавка клетка

По-мало от 33 години

11. Да се ​​изчисли общата стойност на изплащането на заплатите на работниците и служителите на възраст под 33.

12. *** Създаване на формула и изчисляване на средната работна заплата на персонала на възраст под 33 години, с помощта на функцията SUMIF () и COUNTIF ().

13. Тъй като втората форма на изгледа на функция () използва таблица измерение да се определи местоположението на сравняваните стойности?

14. Определя какъв цвят съответства на честотата на 4.91; 5.0; 7,66; 4.06, ако знаем по-ниските пределни стойности на цветовете:

15. Използване на списъка на масата на работниците и служителите (Zadacha_2.xls) файл, за да се определи кой апартамент Брой служители Иванов и Ivanchenko:

1 етап. По-долу е списъкът на още две клетки със съответните имена.

16. Използване на маса продажба на стоки, определяне на информацията, записана в 22 ред и втората колона.

17. Добави в книгата за продажба стоки, нов лист, върху която да се постави следната информация

Испания въздух
Италия т / г
Дания автомобили
Ирландия автомобили
Венецуела въздух
Австрия въздух
Бразилия въздух
Канада въздух
Норвегия автомобили
Полша т / г
Португалия т / г
САЩ въздух
Великобритания т / г
Финландия автомобили
Франция т / г
Швейцария т / г
Швеция т / г

18. Към списъка с лист стоки за продажба, за да добавите доставка колона.

19. Използване на функцията INDEX () и мач (), за да запълни новата информация колона за методите на доставка на поръчки.

20. Определяне на броя на възможните начини за използване на търсене формулата елементи в списъка на колони от.

21. Довежда примери на втората референтна форма функция индекс ().

Задачи на лабораторна работа 2.1.

1. Копие на отделна книга Laboratornaya_rabota_2_1 маса Служителите (референтна лаборатория 1 1.1).

2. Изчислете премията, начислена за служители на организацията.

1 етап. Добавяне на нова страница под скалата за премия за изчисляване на години трудов стаж:

Размерът на наградата дълъг стаж като% от заплатата

номер Опитът по-малко награда%

Етап 2. Към списъка на служителите, за да добавите нова колона - награда. Изчислете премията за годините трудов стаж за всеки служител, като се използва (нормален) Ако функцията логика ().

3. Добавете следните клетките на работния лист, който съдържа списъка или друг списък:

Минималната премия по отдела за покупки
Максималният бонус за отдел продажби
Средната премия за закупуване на отдел
Сумата от отдел продажби награди

4. Използване на функции за база за попълване на клетка в таблицата (условия не варира премахват, тръгват да се провери).

*** 5. Създаване на поръчка за награди за дългогодишен трудов стаж: скрият излишните колони, за да се свързват копираните колони в Excel и Word документи

номер на поръчката

За да се изплаща премията за годините трудов стаж през 2005 г.
следните лица:

6. Копирайте новия списък книгата от продажба на стоки.

7. Добавяне на лист, набор от условия и резултатите от всички следващи изчисления, за да поставите новата страница.

8. Използване на функция BSCHETA (), за да се преброят на поръчки, изпратени до Великобритания.

9. Използване на функция BSCHETA (), за да се преброят на поръчки, по-висока от средната цена, изпратено до Обединеното кралство.

10. Изчислете общия ред хлебни продукти, продавани в Съединените щати.

11. Преброяване на броя на поръчките за печени изделия и млечни продукти.

12. Да се ​​изчисли броя на поръчките на стойност над средното ниво, но не повече от 1 милион UAH.

13. *** Намерете минимална стойност за да избави от Великобритания през първото тримесечие на 1995 година.

1 етап. За да се превърне дата представени в текст, в числен формат, използвайте функцията за DATEVALUE ().

14. *** Намерете минимална стойност за да избави от Великобритания или Швеция.

Задачи на лабораторна работа 2.2.

Lab rabota_3 1. файл, съхраняван в собствената си папка под 2_2_familiya името Lab.

2. Добавяне на нов лист, на която марката следната таблица:

3. В колоната Дата, направи следните дати: 27.12.1994, 01.25.95, 13.04.95, 07.19.95.

4. Използване на препратки функцията и масиви, за да запълни останалите колони от таблицата.

5. Използване на информационния лист за отстъпка добави в размер поле и размер skidki_1 skidki_2 процент отстъпка за всяка поръчка. Използвайте два различни начина (различни функции).

6. За да се изчисли общата стойност на стойността на поръчката, за да се извади сумата на отстъпката.

7. *** С помощта на таблицата на разстояния от Харков до населени места (лист разстояние), изчисли възможно пътуване спящия, в зависимост от марката на колата (разход на гориво на 100 км), марка (стойност) на бензин и наличието на средства. Отговорът е формулиран във всяка клетка под формата на "А пътуване до ..." (вж. Фиг. 2.6).

Фиг. 2.6. Изчисление на възможността за пътуване в спален вагон, в зависимост от марката на колата (разход на гориво на 100 км), марка (стойност) на бензин и наличието на средства


Тема 3. Структуриране на таблици.
Използване на междинни суми за анализ списъци

1. Добавете към списъка с междинни резултати.

2. Работа с структурирани документи.

3. Редактиране на междинните суми.

Удобно средство за анализ на данни в списъци на Microsoft Excel е да структурира маси и изчисляване на междинни суми. Excel ви позволява да даде списък с определена структура като до осем нива на йерархията (вж. Фиг. 3.1). Структуриране на таблицата, като автоматично обобщаване улеснява движението на линии за данни и се използва за група на данните в съответствие с определени параметри за целите на анализа, снабдяване и отчитане.

Фиг. 3.1. Структуриране на списъка с помощта на група от колони отдел код, заглавие и функция на сумата от последната заплата на областта

За да се изчисли резултати на нивото на всеки списък група от елементи, може да се използва междинни суми. В получаването на междинни суми на Microsoft Excel винаги създава списък структура. Изчислете резултати на ниво група може да се основава на различни обобщаващи функции: Суми Количество средно минимум, максимум, продукт, отклонение, дисперсия.

Да предположим, че списъкът е показано на фигура 1.6, не се изисква, за да разберете от общите разходи за заплати във всеки отдел. За да добавите към списъка на междинни резултати, трябва да:

1. Определяне на параметри (колони), които ще бъдат проведени обобщаване, в този пример, колона отдел код.

2. сортирате по колоната, в която ще бъдат обобщени на междинните резултати (колона отдел Code).

3. Използвайте командата на менюто, за да отворите междинните резултати от диалоговия прозорец на данни за резултатите (вж. Фиг. 3.2).

Фиг. 3.2. Попълване диалогови междинни суми

4. Попълнете диалоговия прозорец междинните суми.

1 етап. В "При всяка промяна в:" колона съдържа име, когато стойността, която трябва да обобщим резултатите (лична карта).

Етап 2. В списъка областта свитата "Операция:" изберете крайната функция се използва за генериране на резултатите (Сума); трябва да се помни, че всички обобщаващи функции, с изключение на броя прилагат само за колони, съдържащи числови данни.

Етап 3. В "Добавяне в резултат на:" уточни колоните, в които операциите се извършват на формирането на резултатите (заплати).

Етап 4. Резултатите от квадратчето под данните, необходими, за да се определи дали обобщаващи функции трябва да бъдат поставени под всяка група, както и цялостните резултати в дъното на списъка; подразбиране общ сбор поставени в списък, както и обобщаващи функции - над всяка група.

Етап 5. Край проверка страница кутия между групите на трябва да инсталирате, ако искате да отпечатате всяка група на отделна страница.

Етап 6. Проверете кутия замества настоящите резултати, определени при създаването на нова окончателно изчисление, ако резултатите от този списък вече е създаден.

След натискане на бутона OK в документа автоматично генерирани междинни суми за групирани данни (вж. Фиг. 3.3).

Фиг. 3.3. Структуриране на списъка използване групата чрез колонна лична карта и крайната функция на сумата от Заплата поле