Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сводная таблица данных через формулы по нескольким критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сводная таблица данных через формулы по нескольким критериям (Формулы/Formulas)
Сводная таблица данных через формулы по нескольким критериям
KIMVSR Дата: Пятница, 04.11.2016, 14:44 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте,

Подскажите, пожалуйста, при помощи каких формул можно было бы реализовать следующую задачу:

У меня есть таблица с исходными данными.
В исходной таблице имеется список отделов, должностей в этом отделе, а также типы трудовых договоров.

На соседнем листе я сделал сводную таблицу, в которой я хотел бы сделать автоматический подсчёт по определённым критериям:
Какое количество работников в том или ином отделе на каких должностях работают и какой тип трудового договора имеют.

Я сделал подсчёт вручную на примере отделов JT43 и JT44 (подсветил жёлтым и зелёным цветом):



Как я уже сказал выше - имеющиеся цифры я поставил вручную, но хотелось бы реализовать автоматический подсчёт во всех отделах.
Если поиск не даёт результатов, то можно ставить, например, "-" или "--".

Дабы не казаться лентяем, я попробовал сам реализовать это через следующую формулу:
Код
=COUNTIFS('Исходные данные'!A:A;"JT53";'Исходные данные'!B:B;"Pakettide koostaja";'Исходные данные'!C:C;"ATL")

В принципе, всё работает и так. Но возможно есть какой-то иной вариант, чтобы мне не надо было вписывать эту формулу во все 200 ячеек вручную?
Просто протянуть её не получится, т.к. там довольно много динамических параметров.
Наверное, PIVOT-ом можно, но я его никогда не любил - формулы нравятся больше! :D

Заранее спасибо,
К сообщению приложен файл: KIMVSR.xlsx (21.7 Kb)


Сообщение отредактировал KIMVSR - Пятница, 04.11.2016, 14:45
 
Ответить
СообщениеЗдравствуйте,

Подскажите, пожалуйста, при помощи каких формул можно было бы реализовать следующую задачу:

У меня есть таблица с исходными данными.
В исходной таблице имеется список отделов, должностей в этом отделе, а также типы трудовых договоров.

На соседнем листе я сделал сводную таблицу, в которой я хотел бы сделать автоматический подсчёт по определённым критериям:
Какое количество работников в том или ином отделе на каких должностях работают и какой тип трудового договора имеют.

Я сделал подсчёт вручную на примере отделов JT43 и JT44 (подсветил жёлтым и зелёным цветом):



Как я уже сказал выше - имеющиеся цифры я поставил вручную, но хотелось бы реализовать автоматический подсчёт во всех отделах.
Если поиск не даёт результатов, то можно ставить, например, "-" или "--".

Дабы не казаться лентяем, я попробовал сам реализовать это через следующую формулу:
Код
=COUNTIFS('Исходные данные'!A:A;"JT53";'Исходные данные'!B:B;"Pakettide koostaja";'Исходные данные'!C:C;"ATL")

В принципе, всё работает и так. Но возможно есть какой-то иной вариант, чтобы мне не надо было вписывать эту формулу во все 200 ячеек вручную?
Просто протянуть её не получится, т.к. там довольно много динамических параметров.
Наверное, PIVOT-ом можно, но я его никогда не любил - формулы нравятся больше! :D

Заранее спасибо,
Автор - KIMVSR
Дата добавления - 04.11.2016 в 14:44
TimSha Дата: Пятница, 04.11.2016, 14:59 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 627
Репутация: 94 ±
Замечаний: 0% ±

Excel 2013 Pro +
Цитата KIMVSR, 04.11.2016 в 14:44, в сообщении № 1
Наверное, PIVOT-ом можно, но я его никогда не любил - формулы нравятся больше!

Любите любимое... ;)
Но когда надо было еще вчера, а роды формул идут туго, то... "вариант для ленивых" само то.
К сообщению приложен файл: 3630581.xlsx (28.0 Kb)

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Ответить
Сообщение
Цитата KIMVSR, 04.11.2016 в 14:44, в сообщении № 1
Наверное, PIVOT-ом можно, но я его никогда не любил - формулы нравятся больше!

Любите любимое... ;)
Но когда надо было еще вчера, а роды формул идут туго, то... "вариант для ленивых" само то.
Автор - TimSha
Дата добавления - 04.11.2016 в 14:59
_Boroda_ Дата: Пятница, 04.11.2016, 19:43 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
В C1:F1 пишем только тип договора и формат ячеек
"Количество работников с типом договора "@
Это для простоты, можно и без этого.
И тогда формула будет (файл _1)
Код
=СЧЁТЕСЛИМН('Исходные данные'!$A:$A;ПРОСМОТР(;-КОДСИМВ($A$2:$A11);$A$2:$A11);'Исходные данные'!$B:$B;$B11;'Исходные данные'!$C:$C;C$1)

Если не переделывать первую строку, то (файл _2)
для 3-х символов типа договора
Код
=СЧЁТЕСЛИМН('Исходные данные'!$A:$A;ПРОСМОТР(;-КОДСИМВ($A$2:$A11);$A$2:$A11);'Исходные данные'!$B:$B;$B11;'Исходные данные'!$C:$C;ПРАВБ(C$1;3))

Для любого количества, меньшего 96
Код
=СЧЁТЕСЛИМН('Исходные данные'!$A:$A;ПРОСМОТР(;-КОДСИМВ($A$2:$A11);$A$2:$A11);'Исходные данные'!$B:$B;$B11;'Исходные данные'!$C:$C;СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(C$1;" ";ПОВТОР(" ";99));99)))

По поводу ПРОСМОТР - смотрите здесь http://www.excelworld.ru/forum/2-16573-138042-16-1427281717
По поводу КОДСИМВ внутри ПРОСМОТР - смотрите здесь http://www.excelworld.ru/forum/3-1200-1
К сообщению приложен файл: KIMVSR_1.xlsx (22.4 Kb) · KIMVSR_2.xlsx (22.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВ C1:F1 пишем только тип договора и формат ячеек
"Количество работников с типом договора "@
Это для простоты, можно и без этого.
И тогда формула будет (файл _1)
Код
=СЧЁТЕСЛИМН('Исходные данные'!$A:$A;ПРОСМОТР(;-КОДСИМВ($A$2:$A11);$A$2:$A11);'Исходные данные'!$B:$B;$B11;'Исходные данные'!$C:$C;C$1)

Если не переделывать первую строку, то (файл _2)
для 3-х символов типа договора
Код
=СЧЁТЕСЛИМН('Исходные данные'!$A:$A;ПРОСМОТР(;-КОДСИМВ($A$2:$A11);$A$2:$A11);'Исходные данные'!$B:$B;$B11;'Исходные данные'!$C:$C;ПРАВБ(C$1;3))

Для любого количества, меньшего 96
Код
=СЧЁТЕСЛИМН('Исходные данные'!$A:$A;ПРОСМОТР(;-КОДСИМВ($A$2:$A11);$A$2:$A11);'Исходные данные'!$B:$B;$B11;'Исходные данные'!$C:$C;СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(C$1;" ";ПОВТОР(" ";99));99)))

По поводу ПРОСМОТР - смотрите здесь http://www.excelworld.ru/forum/2-16573-138042-16-1427281717
По поводу КОДСИМВ внутри ПРОСМОТР - смотрите здесь http://www.excelworld.ru/forum/3-1200-1
Автор - _Boroda_
Дата добавления - 04.11.2016 в 19:43
KIMVSR Дата: Вторник, 08.11.2016, 11:53 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 118
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Прошу прощения за столь поздний ответ.

TimSha, большое спасибо за пивот!
_Boroda_, огромное спасибо за формулы, с которыми я раньше не сталкивался. Сейчас попробую разобраться, но выглядит сложно! Спасибо!
 
Ответить
СообщениеПрошу прощения за столь поздний ответ.

TimSha, большое спасибо за пивот!
_Boroda_, огромное спасибо за формулы, с которыми я раньше не сталкивался. Сейчас попробую разобраться, но выглядит сложно! Спасибо!
Автор - KIMVSR
Дата добавления - 08.11.2016 в 11:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сводная таблица данных через формулы по нескольким критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!