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

Вход

Регистрация

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

 

= Мир MS Excel/Сбор персональной статистики в турнирном журнале - Мир MS Excel

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

Доброго времени суток!
Я не знаю каким инструментом можно решить мою задачу, поэтому прошу подсказки мастеров.
Что имеем: Турнирный журнал, в который вносится статистика игроков после каждого матча в лист "Статистика" и лист "Персональная статистика", куда уже потом вручную копируется информация про каждого игрока и там высчитывается среднее значение его результатов.
Задача: Автоматически собрать информацию (Игрок-Герой-KDA) с листа "Статистика" и скопировать в лист "Персональная статистика".
Пример можно увидеть в прикрепленном файле, там сделано вручную.
Если есть еще советы по автоматизации заполнения журнала, буду рад услышать предложения.
К сообщению приложен файл: __Dota2-2.xlsx(81.9 Kb)


Сообщение отредактировал patotpot - Вторник, 13.04.2021, 07:08
 
Ответить
СообщениеДоброго времени суток!
Я не знаю каким инструментом можно решить мою задачу, поэтому прошу подсказки мастеров.
Что имеем: Турнирный журнал, в который вносится статистика игроков после каждого матча в лист "Статистика" и лист "Персональная статистика", куда уже потом вручную копируется информация про каждого игрока и там высчитывается среднее значение его результатов.
Задача: Автоматически собрать информацию (Игрок-Герой-KDA) с листа "Статистика" и скопировать в лист "Персональная статистика".
Пример можно увидеть в прикрепленном файле, там сделано вручную.
Если есть еще советы по автоматизации заполнения журнала, буду рад услышать предложения.

Автор - patotpot
Дата добавления - 13.04.2021 в 07:01
Pelena Дата: Вторник, 13.04.2021, 09:03 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 17499
Репутация: 3886 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Здравствуйте.
Почему на листе "Персональная статистика" у Ильи нет в списке СФ, Марс и Арк?

Добавлено.
Ответа не дождалась. Как поняла
К сообщению приложен файл: _Dota2-2.xlsx(93.9 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Почему на листе "Персональная статистика" у Ильи нет в списке СФ, Марс и Арк?

Добавлено.
Ответа не дождалась. Как поняла

Автор - Pelena
Дата добавления - 13.04.2021 в 09:03
patotpot Дата: Вторник, 13.04.2021, 12:02 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Pelena, просто собрали за 10.04.2021 персоналку, для теста.
Извиняюсь за поздний ответ, просто живем на Чукотке :^)
Вроде бы ваша формула работает, но она такая большая такая, можете объяснить как она работает вкратце? Чтобы я потом не накосячил и смог с ней работать. :)
 
Ответить
СообщениеPelena, просто собрали за 10.04.2021 персоналку, для теста.
Извиняюсь за поздний ответ, просто живем на Чукотке :^)
Вроде бы ваша формула работает, но она такая большая такая, можете объяснить как она работает вкратце? Чтобы я потом не накосячил и смог с ней работать. :)

Автор - patotpot
Дата добавления - 13.04.2021 в 12:02
patotpot Дата: Вторник, 13.04.2021, 12:16 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Если у вас нет времени на объяснение, в любом случае огромное спасибо вам. Будем пользоваться с радостью :)
 
Ответить
СообщениеЕсли у вас нет времени на объяснение, в любом случае огромное спасибо вам. Будем пользоваться с радостью :)

Автор - patotpot
Дата добавления - 13.04.2021 в 12:16
Pelena Дата: Среда, 14.04.2021, 10:07 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 17499
Репутация: 3886 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Пояснялка (для столбца Герой)
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Статистика!$E$3:$E$1000;АГРЕГАТ(15;6;(СТРОКА(Статистика!$E$3:$E$1000)-2)/(Статистика!$D$3:$D$1000=A$1);СТРОКА(A1)));ИНДЕКС(Статистика!$N$3:$N$1000;АГРЕГАТ(15;6;(СТРОКА(Статистика!$M$3:$M$1000)-2)/(Статистика!$M$3:$M$1000=A$1);СТРОКА(A1)-СЧЁТЕСЛИ(Статистика!$D$3:$D$1000;A$1))));"")

если вкратце
Статистика!$D$3:$D$1000=A$1 -на листе Статистика в столбце D ищем имя из ячейки A1 (Илья). В результате получаем массив ИСТИНА/ЛОЖЬ из 1000 элементов. Это можно увидеть, выделив фрагмент в строке формул и нажав клавишу F9 на клавиатуре.
(СТРОКА(Статистика!$E$3:$E$1000)-2)/(Статистика!$D$3:$D$1000=A$1) -там, где нашлось имя (ИСТИНА в предыдущем фрагменте), получаем соответствующую строку. Там где имя не совпадает и в предыдущей части была ЛОЖЬ, получаем ошибку деление на 0. В результате имеем массив из номеров строк и ошибок #ДЕЛ/0!
АГРЕГАТ(15;6;(СТРОКА(Статистика!$E$3:$E$1000)-2)/(Статистика!$D$3:$D$1000=A$1);СТРОКА(A1)) -функция АГРЕГАТ(15;6 из полученного ранее массива начинает выбирать нужные номера строк, начиная с наименьшего (аргумент 15) и дальше по возрастанию, пропуская ошибки (аргумент 6). Возрастание обеспечивается аргументом СТРОКА(A1), т.к. при протягивании формулы вниз он будет давать 1, 2, 3 и т.д. То есть наименьший первый элемент массива, наименьший второй элемент массива ит.д.
ИНДЕКС(Статистика!$E$3:$E$1000; -берём с листа Статистика столбец E - Герой (взяла 1000 строк, можно корректировать при необходимости) и в качестве номера строки подставляем результат функции АГРЕГАТ, полученный на предыдущем шаге, соответственно, получаем имя Героя, находящееся на той же строке, что найденное имя Илья
ЕСЛИОШИБКА(ИНДЕКС( -функция ЕСЛИОШИБКА в данном случае нужна, чтобы перебрать ещё и вторую часть таблицы. То есть когда в первой части нужное имя не найдено, смотрим вторую часть (столбцы M и N). Вторая часть аналогична первой.
Самая первая функция ЕСЛИОШИБКА позволяет, когда имена закончились и в первой и во второй части, вывести пустую ячейку, а не ошибку #ЧИСЛО!

Более подробно можно почитать/посмотреть Как разобраться в работе сложной формулы


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПояснялка (для столбца Герой)
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Статистика!$E$3:$E$1000;АГРЕГАТ(15;6;(СТРОКА(Статистика!$E$3:$E$1000)-2)/(Статистика!$D$3:$D$1000=A$1);СТРОКА(A1)));ИНДЕКС(Статистика!$N$3:$N$1000;АГРЕГАТ(15;6;(СТРОКА(Статистика!$M$3:$M$1000)-2)/(Статистика!$M$3:$M$1000=A$1);СТРОКА(A1)-СЧЁТЕСЛИ(Статистика!$D$3:$D$1000;A$1))));"")

если вкратце
Статистика!$D$3:$D$1000=A$1 -на листе Статистика в столбце D ищем имя из ячейки A1 (Илья). В результате получаем массив ИСТИНА/ЛОЖЬ из 1000 элементов. Это можно увидеть, выделив фрагмент в строке формул и нажав клавишу F9 на клавиатуре.
(СТРОКА(Статистика!$E$3:$E$1000)-2)/(Статистика!$D$3:$D$1000=A$1) -там, где нашлось имя (ИСТИНА в предыдущем фрагменте), получаем соответствующую строку. Там где имя не совпадает и в предыдущей части была ЛОЖЬ, получаем ошибку деление на 0. В результате имеем массив из номеров строк и ошибок #ДЕЛ/0!
АГРЕГАТ(15;6;(СТРОКА(Статистика!$E$3:$E$1000)-2)/(Статистика!$D$3:$D$1000=A$1);СТРОКА(A1)) -функция АГРЕГАТ(15;6 из полученного ранее массива начинает выбирать нужные номера строк, начиная с наименьшего (аргумент 15) и дальше по возрастанию, пропуская ошибки (аргумент 6). Возрастание обеспечивается аргументом СТРОКА(A1), т.к. при протягивании формулы вниз он будет давать 1, 2, 3 и т.д. То есть наименьший первый элемент массива, наименьший второй элемент массива ит.д.
ИНДЕКС(Статистика!$E$3:$E$1000; -берём с листа Статистика столбец E - Герой (взяла 1000 строк, можно корректировать при необходимости) и в качестве номера строки подставляем результат функции АГРЕГАТ, полученный на предыдущем шаге, соответственно, получаем имя Героя, находящееся на той же строке, что найденное имя Илья
ЕСЛИОШИБКА(ИНДЕКС( -функция ЕСЛИОШИБКА в данном случае нужна, чтобы перебрать ещё и вторую часть таблицы. То есть когда в первой части нужное имя не найдено, смотрим вторую часть (столбцы M и N). Вторая часть аналогична первой.
Самая первая функция ЕСЛИОШИБКА позволяет, когда имена закончились и в первой и во второй части, вывести пустую ячейку, а не ошибку #ЧИСЛО!

Более подробно можно почитать/посмотреть Как разобраться в работе сложной формулы

Автор - Pelena
Дата добавления - 14.04.2021 в 10:07
patotpot Дата: Четверг, 15.04.2021, 03:24 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Pelena, очень сложно и круто, постараюсь это переварить. Еще раз спасибо за ваш труд!
Есть еще маленький вопрос: по какому принципу вставляются герои в Персональную статистику? Там они то в середине столбца появляются, то в конце.
 
Ответить
СообщениеPelena, очень сложно и круто, постараюсь это переварить. Еще раз спасибо за ваш труд!
Есть еще маленький вопрос: по какому принципу вставляются герои в Персональную статистику? Там они то в середине столбца появляются, то в конце.

Автор - patotpot
Дата добавления - 15.04.2021 в 03:24
Pelena Дата: Четверг, 15.04.2021, 08:52 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 17499
Репутация: 3886 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
по какому принципу вставляются герои в Персональную статистику?
сначала просматривается левая часть (СВЕТ) сверху вниз, выбираются совпадения по имени, затем правая часть (ТЬМА) также сверху вниз


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
по какому принципу вставляются герои в Персональную статистику?
сначала просматривается левая часть (СВЕТ) сверху вниз, выбираются совпадения по имени, затем правая часть (ТЬМА) также сверху вниз

Автор - Pelena
Дата добавления - 15.04.2021 в 08:52
Светлый Дата: Четверг, 15.04.2021, 12:16 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1589
Репутация: 416 ±
Замечаний: 0% ±

Excel 2010, 2013
Я вот тоже нарисовал формулу. Универсальную. Массивную. Но потребовалось добавить столбец A.
Код
=ЕСЛИОШИБКА(ИНДЕКС(Статистика!$A:$R;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N)*1000+СТРОКА($3:$52));СТРОКА(A1));3);ОСТАТ(СТОЛБЕЦ(B1);2)*4+НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N)*1000+СТРОКА($3:$52));СТРОКА(A1))/1000);"")
*Или немного короче:
Код
=ЕСЛИОШИБКА(ИНДЕКС(Статистика!$A:$R;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N)*1000+СТРОКА($3:$52));СТРОКА(A1));3);ОСТАТ(СТОЛБЕЦ(B1);2)*4+НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N));СТРОКА(A1)));"")
К сообщению приложен файл: Dota2-3.xlsx(95.2 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 15.04.2021, 15:55
 
Ответить
СообщениеЯ вот тоже нарисовал формулу. Универсальную. Массивную. Но потребовалось добавить столбец A.
Код
=ЕСЛИОШИБКА(ИНДЕКС(Статистика!$A:$R;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N)*1000+СТРОКА($3:$52));СТРОКА(A1));3);ОСТАТ(СТОЛБЕЦ(B1);2)*4+НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N)*1000+СТРОКА($3:$52));СТРОКА(A1))/1000);"")
*Или немного короче:
Код
=ЕСЛИОШИБКА(ИНДЕКС(Статистика!$A:$R;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N)*1000+СТРОКА($3:$52));СТРОКА(A1));3);ОСТАТ(СТОЛБЕЦ(B1);2)*4+НАИМЕНЬШИЙ(ЕСЛИ(Статистика!$D$3:$M$52=A$1&B$1;СТОЛБЕЦ($E:$N));СТРОКА(A1)));"")

Автор - Светлый
Дата добавления - 15.04.2021 в 12:16
patotpot Дата: Пятница, 16.04.2021, 02:34 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Светлый, сохраню оба файлика на всякий, спасибо!
Ну и тогда последний вопросец: если придется добавить нового человека, то достаточно просто протянуть формулу в его сторону и оно будет искать имя, которое указано в первой строке в Персональной статистике?
 
Ответить
СообщениеСветлый, сохраню оба файлика на всякий, спасибо!
Ну и тогда последний вопросец: если придется добавить нового человека, то достаточно просто протянуть формулу в его сторону и оно будет искать имя, которое указано в первой строке в Персональной статистике?

Автор - patotpot
Дата добавления - 16.04.2021 в 02:34
Светлый Дата: Пятница, 16.04.2021, 09:37 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1589
Репутация: 416 ±
Замечаний: 0% ±

Excel 2010, 2013
достаточно просто протянуть формулу
Да. И диапазон с $3:$52 расширить можно вниз до нужного значения.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
достаточно просто протянуть формулу
Да. И диапазон с $3:$52 расширить можно вниз до нужного значения.

Автор - Светлый
Дата добавления - 16.04.2021 в 09:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сбор персональной статистики в турнирном журнале (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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