Высчитать частоту и записать в лист. - Мир MS Excel

Регистрация

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

= Мир MS Excel/Высчитать частоту и записать в лист. - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Высчитать частоту и записать в лист. (Формулы/Formulas)

Высчитать частоту и записать в лист.

Дата: Понедельник, 15.12.2014, 20:38 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Доброго времени, помогите в построении формулы.
Имеется книга, в книге 2 листа.
Первый лист вводная информация.
Необходимо привязаться к дате периода, высчитать частоту (т.е день недели) при нахождении определенного дня недели то писать во второй лист в необходимый день недели в указанное время.
Во вложении образец.

К сообщению приложен файл: 6477974.xlsx (12.1 Kb)

Ответить

Дата: Понедельник, 15.12.2014, 20:47 | Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, можете вручную на листе2 показать, какой результат вы ожидаете?

Ответить

Дата: Понедельник, 15.12.2014, 21:03 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата buchlotnik, 15.12.2014 в 20:47, в сообщении № 2

можете вручную на листе2 показать, какой результат вы ожидаете?

Спасибо за скорый ответ, во вложении.

Т.е указав в определенный период дат день недели произвести расчет от даты А к дате Б сколько к примеру суббот будет и заполнить в листе 2 каждую дату из периода с привязкой к времени

К сообщению приложен файл: 8810018.xlsx (12.5 Kb)

Ответить

Дата: Понедельник, 15.12.2014, 22:10 | Сообщение № 4

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, монстр получился, но работает. Вопрос такого рода - день недели может совпадать для разных газелей? Тогда придётся еще подумать

К сообщению приложен файл: 8810018-1-.xlsx (98.9 Kb)

Ответить

Дата: Понедельник, 15.12.2014, 22:26 | Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2525

Репутация: 678 ±

Замечаний: 0% ±

2010

У меня монстр поменьше но вроде работает

Код

=ЕСЛИОШИБКА(И(B$1>=Лист1!$B$4;Лист2!B$1<=Лист1!$C$4)*(ДЕНЬНЕД(B$1;2)=СУММ(ИНДЕКС(Лист1!$D$4:$J$20;ПОИСКПОЗ(ЗНАЧЕН(Лист2!$A2);Лист1!$M$4:$M$20;0);)))*ИНДЕКС(Лист1!$A$4:$A$20;ПОИСКПОЗ(ЗНАЧЕН(Лист2!$A2);Лист1!$M$4:$M$20;0);1);0)

После 20:00 формулы убрал, файл не проходил по объему.

К сообщению приложен файл: 0992576.xlsx (92.7 Kb)

ЯД-41001506838083

Ответить

Дата: Понедельник, 15.12.2014, 22:32 | Сообщение № 6

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

gling, у Вас привязка к датам по газели №1 - а там разные диапазоны дат для разных машин, потому у меня и получилось "жирное нечто"

сейчас думаю как совпадающие дни обработать, но что-то подсказывает, что проще UDF написать

Ответить

Дата: Понедельник, 15.12.2014, 22:35 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата buchlotnik, 15.12.2014 в 22:10, в сообщении № 4

день недели может совпадать для разных газелей?

вааа, это абалденно, да я что то не подумал о газелях, а можно как-то дополнить ?

Ответить

Дата: Понедельник, 15.12.2014, 22:52 | Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2525

Репутация: 678 ±

Замечаний: 0% ±

2010

Цитата

там разные диапазоны дат

Точно, замарочился окончанием формулы, а про начало забыл. ~~Думаем дальше.~~Придумал.

ЯД-41001506838083

Сообщение отредактировал gling - Понедельник, 15.12.2014, 23:07

Ответить

Дата: Понедельник, 15.12.2014, 23:05 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2525

Репутация: 678 ±

Замечаний: 0% ±

2010

Изменил немного формулу, монстр подрос

Код

=ЕСЛИОШИБКА(И(B$1>=ИНДЕКС(Лист1!$B$4:$B$20;ПОИСКПОЗ(Лист2!$A2;Лист1!$M$4:$M$20;0););Лист2!B$1<=ИНДЕКС(Лист1!$C$4:$C$20;ПОИСКПОЗ(Лист2!$A2;Лист1!$M$4:$M$20;0);))*(ДЕНЬНЕД(B$1;2)=СУММ(ИНДЕКС(Лист1!$D$4:$J$20;ПОИСКПОЗ(Лист2!$A2;Лист1!$M$4:$M$20;0);)))*ИНДЕКС(Лист1!$A$4:$A$20;ПОИСКПОЗ(Лист2!$A2;Лист1!$M$4:$M$20;0);1);0)

По поводу одинаковых дней в неделе, не страшно, вот когда еще время совпадет, то отобразится только первая в списке Газель с таким днем и временем. Как всех вместить в одну ячейку? Если надо, то тогда лучше макрос писать. Да и эта формула не будет правильно работать, если у одной Газели будет две отметки в неделю. Не знаю существует ли такой вариант.

ЯД-41001506838083

Сообщение отредактировал gling - Понедельник, 15.12.2014, 23:23

Ответить

Дата: Вторник, 16.12.2014, 16:45 | Сообщение № 10

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Сделал вариант для нескольких отметок в неделю.

Код

=ЕСЛИОШИБКА(ЕСЛИ(И(ЕСЛИОШИБКА(ИНДЕКС(Лист1!$D$5:$J$21;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$21;0);ПОИСКПОЗ(ДЕНЬНЕД('Лист2 (2)'!B$1;2);Лист1!$D$4:$J$4;0));ЛОЖЬ);ИНДЕКС(Лист1!$B$5:$B$21;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$21;0);1)<='Лист2 (2)'!B$1;ИНДЕКС(Лист1!$C$5:$C$21;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$21;0);1)>='Лист2 (2)'!B$1);ИНДЕКС(Лист1!$A$5:$A$21;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$21;0);1);"");"")

К сообщению приложен файл: _8810018-1-.xlsx (71.0 Kb)

Ответить

Дата: Вторник, 16.12.2014, 17:05 | Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

buchlotnik, Спасибо большое.
Подскажите пытаюсь внести изменения в таблицу, теперь потребовалось произвести расчет на втором листе в интервале для заполнения в 5 минут.
И как применить формулу ко всему листу второму? Что то у меня не вышло (я не гуру exel но ранее успешно проходило.)

Ответить

Дата: Вторник, 16.12.2014, 17:18 | Сообщение № 12

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, просто замените время в крайнем левом столбце на нужный вам шаг. Формула вводится простым протягиванием

Сообщение отредактировал buchlotnik - Вторник, 16.12.2014, 17:18

Ответить

Дата: Вторник, 16.12.2014, 21:33 | Сообщение № 13

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

buchlotnik, да но при добавлении строк в первый лист, во второй лист данные не идут...

К сообщению приложен файл: 5158160.png (17.2 Kb)

Ответить

Дата: Вторник, 16.12.2014, 21:43 | Сообщение № 14

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, понял - давайте разбирать формулу. вот этот элемент

Код

ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$21;0)

21 замените на реальный номер последней строки на первом листе (4 раза по ходу формулы). Ещё один момент - некоторые значения времени на листе 2 были текстовыми - я перевводил во временном формате - тогда формула работает. На листе два формулу из поста №10 введите в B2 и протяните. Как-то так

стоп, туплю - все 21 по ходу формулы замените на нужное число строк, можно на 1000 и не париться

Сообщение отредактировал buchlotnik - Вторник, 16.12.2014, 21:53

Ответить

Дата: Вторник, 16.12.2014, 22:04 | Сообщение № 15

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

buchlotnik, то ли я то ли лыжи не едут.... Доступно только для пользователей

Ответить

Дата: Вторник, 16.12.2014, 23:24 | Сообщение № 16

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, да, с лыжами какая-то беда

формулу поменял:

Код

=ЕСЛИОШИБКА(ЕСЛИ(И(ЕСЛИОШИБКА(ИНДЕКС(Лист1!$D$5:$J$1982;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$1982;0);ПОИСКПОЗ(ДЕНЬНЕД('Лист2 (2)'!B$1;2);Лист1!$D$4:$J$4;0));ЛОЖЬ);ИНДЕКС(Лист1!$B$5:$B$1982;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$1982;0);1)<='Лист2 (2)'!B$1;ИНДЕКС(Лист1!$C$5:$C$1982;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$1982;0);1)>='Лист2 (2)'!B$1);ИНДЕКС(Лист1!$A$5:$A$1982;ПОИСКПОЗ('Лист2 (2)'!$A2;Лист1!$M$5:$M$1982;0);1);"");"")

вылечил битые ссылки - на листе 2 - сделайте замену ":" на ":" (через ctrl+h), других проблем не увидел

К сообщению приложен файл: -2-.zip (93.1 Kb)

Ответить

Дата: Среда, 17.12.2014, 10:37 | Сообщение № 17

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

buchlotnik, поделитесь магическим песком, вот второй лист ставлю время с шагом 5 минут, протягиваю формулу и все не работает....

Ответить

Дата: Среда, 17.12.2014, 13:29 | Сообщение № 18

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, мой файл из сообщения №16 работает?

Ответить

Дата: Среда, 17.12.2014, 15:53 | Сообщение № 19

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

buchlotnik, частично, ответил в почту...
[moder]Почему в почту? Нарушаете Правила форума п. 5о[/moder]
update
файл не прикрепился ибо большой....http://rghost.ru/

Сообщение отредактировал Piople - Среда, 17.12.2014, 16:18

Ответить

Дата: Четверг, 18.12.2014, 00:16 | Сообщение № 20

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Piople, сделал как в посте №16 - работает

К сообщению приложен файл: 1302928.zip (84.1 Kb)

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Высчитать частоту и записать в лист. (Формулы/Formulas)

Страница 1 из 1
1

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