Помощ за Ексел

Здравейте от много време си мисля една задача, но не мога да я измисля. Моля ако можете да ми помогнете пишете:
Имаме таблица с ЕГН, имена, професии. - 1000 човека
Трябва да се установи колко от тях са до 50 год., от 50 до 40 год, и т.н
Е не можах да го измисля.

Ако ЕГН-то е в колона "A", то тази формула може да се използва да получиш датата на раждане в допълнителна колона, да речем "D":

=IF(VALUE(MID(A1;3;1))>1;DATEVALUE(MID(A1;5;2)&"."&VALUE(MID(A1;3;1))-4&MID(A1;4;1)&"."&"20"&MID(A1;1;2));DATEVALUE(MID(A1;5;2)&"."&MID(A1;3;2)&"."&MID(A1;1;2)))

С формулта =DATEDIF(D1;TODAY();"y") пък получаваш навършените години в следващата колона ("E").

От там нататък в ексел 2007 има функция COUNTIFS() за преброяване с повече от едно условие напр:

=COUNTIFS($E$1:$E$5;">=40";$E$1:$E$5;"<50") - брои тези, които са на 40 или повече, но са по-млади от 50 г.

За ексел 2003 може би ще трябва да се вкарват допълнителни колони, в които да се смята дали конкретният човек попада в интервала, който ни интересува и после да се ползва COUNTIF() или SUM(). Например в колона "F":

=AND(E1>=40;E1<50)

и после:

=COUNTIF(F1:F5;TRUE) - и така за всеки интервал, който те интересува.

Сигурно има по-добри варианти, който има идеи да помага.

Елементарно е:
Да кажем че в клетка "C5" имаш ЕГН-то. Тогава в клетка "E5" пишеш тази формула:

=CONCATENATE(19,LEFT(C5,2)) така ще получиш през коя година е роден човекът.

В клетка "F5" пишеш тази формула:
=MID(C5,3,2) така ще получиш през кой месец е роден човекът.

В клетка "H5" пишеш сегашната година/това ако искаш да го гледаш към днешна дата ако не напиши годината която те интерсува/ - 2009

В клетка "I5" пишеш текущия месец или към, който месец искаш да си правиш анализа- 03

В клетка "K5" пишеш тази формула:

=IF(F5-I5>0,(H5-E5)-1,H5-E5)

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

Не е ли прекалено сложно така ?
Като има годините с един елементарен филтър може да види всички възрасти, които я интересуват.

Съгласен, особено за варианта за ексел 2003, но така резултатът може да се ползва и за други изчисления, пък и на филтъра също трябва да му се зададат две условия, за да се определи дали възрастта попада в даден интервал.
Като ще си търсим кусури пък, твоят вариант не отчита, че в ЕГН-то на родените след 2000г към месеца се прибавя 40 - примерно 7552010005 за дата на раждане 01.12.2075 г.

Пробвах всичко което сте ми написали.
При варианта на t9000 не се получава образуването на годината, образува до 3 знака, а не до 4 и променя сметките. Не знам как да го оправя.
Ексела е 2003 г.
Само да отбележа, че тази задача ми е дадоха на интервю за работа и трябваше да реша за 10 - тина минути. Е не успях.
Благодаря на отзовалите се.

Съгласен съм, няма да работи за хора с новите ЕГН-та, честно казано бях забравил за новият формат.

Не виждам как ти образува до трети знак:
=CONCATENATE(19,LEFT(C5,2))
Тази формула взима цифрата 19 и към нея прибавя първите два символа от ляво на дясно от зададената клетка.
В случай, че в клетка "C5" имаш примерно 7308186410 ще получиш:
1973.

Просто трябваше да сложа ; вместо , и се получи

А в другия вариант кое не работи?

Другият вариант работи нормално. благодаря.
Сега обмислям как най лесно да ги сортирам и да стигна до отговора при варианта на T900.
Ще ги използвам благодаря

А ти за каква работа си кандидатствала - много нестандартно ми се струва. Ако не се занимаваш интензивно с ексел, ми се вижда невъзможно да се реши. Аз съм сигурен, че повечето хора в моят офис няма да могат да в решат.

Най лесно е като пуснеш един "auto filter" /Data->Filter->AutoFilter/
отидеш на колоната с годините и му дадеш "custom"
После избираш:

Is grater than or equal to ->40
and
Is less than or equial to -> 50

Така получаваш всички на възраст между 40 и 50.

Другият лесен вариант е да използваш формулите на zhitov:

=AND(E1>=40;E1<50)

и после:

=COUNTIF(F1:F5;TRUE)

като замениш клетка E1 със съответната клетка на годините и рейнджа F1:F5

Мениджър човешки ресурси. Занимавам се с ексел, но не на такова ниво.
Задачата бе следната:
Списък с лица около 1000 с данни трите имена, ЕГН, длъжност трябва да се опреде.и:
1. Колко лица има на длъжност х, у и т.н
2. Колко лица са на възраст над 60 г.
от 50-60 и т.н както ви описах по горе.
Това за длъжностите го направих, но за годините не можах.

Аз отначало си помислих за филтър, но при списък 1000 човека голямо броене ще падне. Ще ползавам формулата.

При така поставена задача най-вероятно бих ползвал Pivot Table.

Би ли доразвил идеята си, моля.
От няколко месеца си блъскам глават асъс тази задача и ми е интересно

Идеята ми е, след като получим възрастта в колона "D", в съседната колона до попълним възрастовия интервал, в който попада всеки човек.

За целта трябва да се направи една допълнителна табличка:

0	9

10	19

20	29

30	39

40	49

50	59

60	69

70	79

80	89

90	повече

Аз я направих в областта "J3:K12"
След това в колона "E" вкарвам следната формулка:
=VLOOKUP(D2;$J$3:$K$12;1) & " - " & VLOOKUP(D2;$J$3:$K$12;2)

Сега вече с пивотка можем да правим всякакви справки - колко души попадат в даден възрастов интервал, колко заемат еди-каква си длъжност, колко пъти се среща името Иван (ако трите имена са в отделни колони) и т.н.

няма нужда от допълнителна колона. Може да се използва "Group and Show detail" функционалността на пивот таблиците, за да се зададат категориите.
Дори, ако ЕГН-тата са форматирани като числа, няма нужда и от формули, а може дирекнто да се групират, но пък е доста пипкаво и податливо на грешки

доста тъпа задача за Мениджър човешки ресурси, мен ако питате

Ами не съм ползвал пивотки точно за такива задачи, на първо четене и без примерни данни това ми хрумна.