Расчет результатов в таблицах по формуле

[nick]

добрый день. чесно сказать в exсel совсем новичек. так что по моим силам задача просто невыполнима. суть проблемы (на картинке более наглядно). смысл в том, что в в таблице 2 и 3 автоматом должны появляться значения из таблицы 1.предположим команда 1 сыграла с командой 2 в 1-1. следовательно во 2 таблице где ++ будет 0, где -- тоже 0, а вот где ничья должно появиться значение 1. соответсвенно, если допустим команда 2 сыграет с 3 командой и выигрывает, то во 2 таблице где ++ будет стоять уже не 0,а 1 (как победа). помимо того что все это считается, есть еще и 3 таблица, гбе будет отображаться количество мечей забитых у той или иной команды. проблем вообще тьма: основная в том, что если допустим счет 0-0 то значения во 2 и 3 таблице все равно должны быть-0. тут кажется должен присутсвовать операнд if * than. помимо прочего в 3 таблице еще и итоговое количество очков,и если команда все матчи в ничью, то 3 очка, а вот при всех 3 победах- 9.(кажется тут поможет операнд vlookup).еще проблема в том что при счете скажем 10-0 в двух матчах программа не понимает, что итоговый счет все равно 6 по сумме двух встреч ,а упорно рисует 20.также с нулем- если матч вничью то отказывается писать вообще любые значения, ссылаясь на то, что ячейка пуста.
требуется помощь мужики выручайте.

[IKor]

2 nick

Во-первых, хочу отметить, что идея ручного заполнения таблицы №1 в представленном формате (удобном для восприятия, но не для дальнейшего использования) значительно усложняет передачу данных в таблицы №2 и №3: сначала требуется распознание результата, после его анализ и лишь затем переконвертация в формат данных для новых таблиц.
Поэтому я предлагаю Вам рассмотреть возможность внесения данных в отдельную (можно вынесенную на отдельный лист) таблицу в удобном для анализа формате, на основании которой будут сформированы все три Ваши таблицы.
На мой взгляд удобный формат таблицы (представлены заголовки столбцов):
Команда-Хозяин; Команда-Гость; количество забитых голов командой-Хозяином; количество ... командой-Гостем

Во-вторых, всем будет удобнее разобраться в Вашей проблеме, если Вы вместо картинки приложите файл в формате Excel (отдельно исходные данные, отдельно желаемый результат). Мало кто согласится рисовать за Вас экселевский файл.

И в-третьих, постарайтесь представить все возможные (особенно не очевидные) варианты результатов матча - тогда можно будет проверить работу формул/макросов в различных условиях.

[nick]

понял коммандир.  сейчас будет. правда формулы не идут вообще,так что сосчитали с бойцами вручную (трудно заставить программу поставить команде 3 очка, упорно считает все значения в ячейках).а вот на разных листах вариант забуксовал- получается очень большой список лиг, команд, так что решили на одной странице все сделать. думаю 65000 строчек хватит даже в будущем...

[IKor]

2 nick

Посмотрите прилагаемый файл.
Основные формулы, рассчитывающие нужные Вам цифры приведены в нем.

Обращаю внимание на то, что в формулах использовано имя Table (смотрите вкладку Формулы => Диспетчер имен). Если новые строки (матчи) будут добавляться внутрь существующей таблицы и в трех правых столбцах будут скопированы/протянуты формулы из верхних строк, то новые матчи будут учитываться в таблицах 1-3.
В противном случае потребуется проверить корректность привязки имени к таблице и корректность вспомогательных формул.

Из возможностей по улучшению:
1. названия команд можно доставать из выпадающего списка (на вкладке Данные => меню Проверка данных; в открывшемся окне необходимо указать тип данных "Список" и дать ссылку на источник данных)
2. Можно сортировать данные в таблицах 1 - 3 по количеству очков - в качестве самоподготовки предлагаю разобраться с работой функции НАИБОЛЬШИЙ()
3. Для обоих предложений потребуется где-то организовать дополнительную вспомогательную таблицу

Удачи!

Будут вопросы - обращайтесь

[nick]

ну ни...себе. чума человек. очень напоминает искомое. из того, что сразу пугает- диапазон не хватает мозгов поменять (упорно появляются tba при удалении хотя бы одного столбца). отсюда вопрос- можно ли изменить на ячейки (поскольку таблица будет находится чуть в иной позиции относительно листа и часто будут добавляться\меняться команды, чтобы изменения делались путем + или - та или иная ячейка, а то получается что двигая допустим таблицу на 40 строчек влево и вниз все значения слетают)? нашел такую- =vlookup($C6;Setting!$C$4:$AC$23;5;FALSE) но там чего-то не хватает, хотя и без диапазона с именем вроде.и проблема 0.2 так сказать- работа в этой штуке происходить будет часто с телефона и через Linux- там аналогичные программы не все поддерживают сложные формулы. если ли вероятность того, чтобы оставить имеющиеся значения переделать в другой формат, допустим .odt, .gnumeric. и на случай если с этим возиться уже некогда, все равно спасибо, очень выручили.

[IKor]

2 nick

1. По логике работы значение "TBD" должно возвращаться в том случае, если формула не нашла в таблице матча между указанными хозяином и гостем (выделено красным)
=ЕСЛИ($K2=L$1;"";ЕСЛИОШИБКА(ИНДЕКС(Table;ПОИСКПОЗ($K2&" - "&L$1;ИНДЕКС(Table;0;6);0);3)&" : "&ИНДЕКС(Table;ПОИСКПОЗ($K2&" - "&L$1;ИНДЕКС(Table;0;6);0);4);"TBD"))
Таким образом, если в таблицу Table добавить строку, в которой хозяином будет являться команда, записанная в ячейке К2, а гостем команда из ячейки L1, то формула вернет результат матча, записанный в найденной строке.


2. Формулы ИНДЕКС() и ВПР(_) / INDEX() и VLOOKUP() соответственно чувствительны к изменению указанных в них номеров столбцов/строк. Если Вы модифицируете таблицу (удаляете/переставляете столбцы), то необходимо соответствующим образом изменить ссылки на нужные столбцы внутри функции (номера).


Важно понимать, что функция ВПР(Искомое;Таблица;Номер_строки;Точность_совпадения) возвращает значение из указанного столбца (третий параметр) таблицы (второй параметр) из строки, найденной в первом столбце этой таблицы по точному (четвертый параметр равен FALSE или 0), либо приближенному (четвертый параметр равен TRUE или 1) совпадению со значением, указанным в качестве первого параметра функции.


Поэтому приведенная Вами функция пытается вернуть значение из таблицы с другого листа Setting, из пятого ее столбца "G" и из строки, в которой в столбце С таблицы находится точно такое же значение, что и в ячейке C6 того листа, на котором записана формула.


3. Если поставить столбец-критерий первым в таблице, то вместо конструкции ПОИСКПОЗ(...ИНДЕКС()...) можно будет использовать формулу ВПР(_). - смотрите прилагаемый файл. Я правда не уверен, что эта функция "проще" и поддерживается LINUX'ом или аналогичными программами.
=ЕСЛИ($K2=L$1;"";ЕСЛИОШИБКА(ВПР($K2&" - "&L$1;Table;3;0)&" : "&ВПР($K2&" - "&L$1;Table;4;0);"TBD"))


4. На всякий случай поясню также работу функции ИНДЕКС(Диапазон;Номер_строки;Номер_столбца).
Она возвращает значение из указанного диапазона, лежащие на пересечении указанных номерами строки и столбца. Если в качестве номера строки указать 0, то функция вернет ссылку на весь указанный номером столбец диапазона. Этим свойством я пользовался для указания функции СУММЕСЛИ() требуемых диапазонов.
например, функция  ИНДЕКС(Table;0;2) в моем файле вернет ссылку на диапазон С3:С13, в котором записаны все игравшие команды-хозяева...

[nick]

да, делов тут на...1.2 жизни. спасибо за помощь *ушел разбираться*.