Растягивание формулы

[fadsozaur]

Добрый день, не могу решить 1 проблему, как растягивая Е5 вниз до Е13, сделать так, чтобы формула брала ячейки с F4 до N4, а не F5 До E13. И наоборот, взяв с Е6 до F6, брала с G4 по G5?
Спасибо.

[The_Prist]

Здравствуйте.
Ну почему на форумах по Excel все так любят выкладывать скрины своих произведений, вместо примеров в .xls? Здесь же не форум по Fotoshop, не так ли?
По теме: пробуйте различные вариации закрепления, используйте СМЕЩ, а возможно и ДВССЫЛ и СТРОКА(), СТОЛБЕЦ().

[DV68]

=1-СМЕЩ($E$4;СТОЛБЕЦ()-5;СТРОКА()-4),
но лучше так:
=1-ИНДЕКС($E$4:$N$13;СТОЛБЕЦ()-4;СТРОКА()-3)

[Tidus]

А у меня получилось вот так:
Вставляем в ячейку Е5 эту формулу, потом можем спокойно растягивать вниз и всё работает:
=СМЕЩ($E$4;0;СТРОКА()-4)

[DV68]

Сообщение от Tidus

А у меня получилось вот так:
Вставляем в ячейку Е5 эту формулу, потом можем спокойно растягивать вниз и всё работает:
=СМЕЩ($E$4;0;СТРОКА()-4)

Правильно, но Ваша формула работает только в столбце Е, а моя во всех ячейках нижней половины таблицы. Т.е. у Вас не соблюдается условие:

И наоборот, взяв с Е6 до F6, брала с G4 по G5

[fadsozaur]

Большое спасибо.
А почему нету ссылок на столцы и строки? Как объяснить это преподавателю...

[Tidus]

Сообщение от DV68

Правильно, но Ваша формула работает только в столбце Е, а моя во всех ячейках нижней половины таблицы. Т.е. у Вас не соблюдается условие:

Да, вы правы Нужно теперь описать принцип работы того, что вы написали в формулах, так как многие могут не разобраться.

[Tidus]

Сообщение от fadsozaur

Большое спасибо.
А почему нету ссылок на столцы и строки? Как объяснить это преподавателю...

Ссылка на ячейку есть, только в функции она уже формируется по координатам X и Y, которые изменяются динамически при растягивании, - эти координаты находятся в скобках функции СМЕЩ():

($E$4;СТОЛБЕЦ()-5;СТРОКА()-4) , в общем виде можно записать и так (0;X;Y).
Красным цветом выделил виртуальную ячейку 0, так называемую точку отсчёта, относительно которой происходит формирование ссылки на ячейку, ей координаты по осям X и Y в разных направлениях. Функции столбец() и строка() оригинальным образом реализуют динамическое смещение при быстром копировании формулы в горизонтальном X и вертикальном Y направлениях.
Синим цветом указана координата смещения по оси Y от 0 ячейки , то есть по строкам.
А зеленым цветом указаны координаты смещения относительно 0 ячейки по оси X, то есть по столбцам.
Механизм работы: в момент перемещения ползунка вниз, функция столбец() не изменяет свое значение, так как скопированная ячейка находится в этом же столбце, а функция строка() изменяет своё значение и возвращает номер текущей строки, поэтому ссылка на ячейку меняется.
Аналогичным образом этот механизм работает при копировании ячейки вправо или влево по оси X.

[fadsozaur]

Препод меня валит как только может по всем задачам, он мне сказал, а если F4 стереть (типо они не играли), почему в ячейке E5 отображается, что 2участник выиграл, хотя и не играл... Подскажите пожалуйста, что делать уже не знаю.

[IgorGO]

Препод меня валит как только может по всем задачам, он мне сказал, а если F4 стереть (типо они не играли), почему в ячейке E5 отображается, что 2участник выиграл, хотя и не играл...

препод вас не валит, а задает резонный вопрос и на него есть элементарный ответ при наличии знаний по предмету

Подскажите пожалуйста, что делать уже не знаю

изучайте Excel...
вот формула для ячеек под диагональю таблицы:

=ЕСЛИ(СМЕЩ(R4C5;СТОЛБЕЦ()-СТОЛБЕЦ(R4C5);СТРОКА()-СТРОКА(R4C5))="";"";1-СМЕЩ(R4C5;СТОЛБЕЦ()-СТОЛБЕЦ(R4C5);СТРОКА()-СТРОКА(R4C5)))

или так, если угодно

=ЕСЛИ(СМЕЩ($E$4;СТОЛБЕЦ()-СТОЛБЕЦ($E$4);СТРОКА()-СТРОКА($E$4))="";"";1-СМЕЩ($E$4;СТОЛБЕЦ()-СТОЛБЕЦ($E$4);СТРОКА()-СТРОКА($E$4)))