В ячейке а11 программы эксель записана формула сумма а1 а10 при копировании
Функция ЕСЛИ() относится к наиболее часто используемым функциям. Поэтому рассмотрим ее подробно.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Функция имеет 3 аргумента.
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, выражение A1>100. Если в ячейке А1 содержится значение 102, то выражение A1>100 вернет ИСТИНА, т.к. 102>100. Если в ячейке А1 содержится значение 88, то выражение вернет ЛОЖЬ, т.к. неравенство 88>100 неверно. Полностью формула может выглядеть так =ЕСЛИ(A1>100;"Бюджет проекта превышен";"ОК!") Если в ячейке A1 содержится значение большее 100, то формула вернет текстовое значение Бюджет проекта превышен, а если меньше 100 или равное 100, то будет выведено ОК!
Второй аргумент - Значение_если_истина и третий аргумент значение_если_ложь
Как было показано выше, если логическое выражение (первый аргумент функции) принимает значение ИСТИНА, то функция возвращает значение из второго аргумента значение_если_истина. Соответственно, если логическое выражение принимает значение ЛОЖЬ, то функция возвращает значение из третьего аргумента значение_если_ложь .
Второй и третий аргумент могут быть любым значением (число или текст) или являться выражением (формулой с функциями). Например формула =ЕСЛИ(A1>100;0;А1*1%) позволяет вычислять премию для проектов, в которых не было превышения бюджета (>100). Если бюджет превышен (в А1 введено значение 200 или любое другое более 100), то формула вернет 0, если в А1 введено значение менее 100, то будет вычислено значение премии А1*1% (т.е. если в А1 число 90, то формула вернет 0,9).
Если в качестве второго и третьего аргументов введено выражение, то оно может содержать и функции, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше или равно 100, то по столбцу С . Не забывайте, что при значении А1 = 100 в данном случае будет срабатывать выражение из аргумента значение_если_ложь.
Аргумент значение_если_ложь можно опустить (не вводить), тогда при значении логического выражения =ЛОЖЬ будет выведено ЛОЖЬ. Например, если в А1 содержится 600, то формула =ЕСЛИ(A1>=1000;"Больше или равно 1000") вернет просто ЛОЖЬ. Понятно, что при значении логического выражения =ИСТИНА будет выведено "Больше или равно 1000", т.е. второй аргумент функции ЕСЛИ() - Значение_если_истина.
Вложенные ЕСЛИ (несколько условий)
Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов значение_если_истина и значение_если_ложь введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу
- Если значение из ячейки А8 менее 5, то будет выведено 1
- Если значение из ячейки А8 более 10, то будет выведено 3
- Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2
Примечание : В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.
Примечание : Формулу =ЕСЛИ(A8 10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)
или более сложный вариант =ЕСЛИ(A8
Формула =ЕСЛИ(A8 10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8 =5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.
Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:
1) Определитесь с диапазонами:
1.1) они не должны пересекаться. В примере выше - 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8 10;3;ЕСЛИ(A8 10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.
1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.
Примечание: В перечисленных выше случаях логические выражения вычисляются на основании одной переменной А8. Иногда нужно придумывать формулы с ЕСЛИ(), зависящие от 2-х и более ячеек: =ЕСЛИ(B8>=5;ЕСЛИ(A8>10;3;2);1). Это гораздо сложнее, но, конечно, возможно. Следуйте правилам 1.1)-1.3) и следите за диапазонами. Еще сложнее, если обе переменных являются зависимыми, т.е. например B8 вычисляется на основании А8. Написание таких формул требует предельной аккуратности и напрямую связано с корректностью формулирования самой задачи (возможно вы не продумали до конца как использовать такую сложную зависимость). Советую по возможности разбить вычисления на 2 или более формулы, чтобы проще контролировать результат вычисления.
2) Правильно записывайте логические выражения. Например, условие 5 10;3;2)) в строке формул можно записать как
Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.
4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения - последовательности 1; 2; 3 . Это покажет какой блок вложенных ЕСЛИ() срабатывает.
5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета
=ЕСЛИ(A1>=100;"Очень крупный проект";
ЕСЛИ(A1>=90;"Крупный проект";
ЕСЛИ(A1>=50;"Средний проект";"Малый проект ")))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Формула =ПРОСМОТР(A1;;) выглядит необычно, но по сути второй аргумент , как и третий аргумент это запись содержимого 4-х ячеек. Вместо можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.
Еще пример. Пусть требуется вывести разный текст (например " 0") в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():
Примечание : -1E+307 - это самое маленькое отрицательное число в EXCEL, 1E-307 - это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).
Второй массив можно заменить ссылкой на диапазон, например для того чтобы вывести результат вычисления формул:
Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).
Опущен третий аргумент [значение_если_ложь]
Третий аргумент функции не обязателен. Если логическое условие не выполнено, т.е. ЛОЖЬ, а третий аргумент функции не указан, то функция вернет также значение ЛОЖЬ. Например:
Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Сложные логические выражения
В аргументе Лог_выражение можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.
Основное правило - логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.
Приведем несколько полезных примеров.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1 10") . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;">10") . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
Задача № 7. Электронные таблицы. Абсолютная и относительная адресация. Графики и диаграммы.
Автор материалов — Лада Борисовна Есакова.
Microsoft Excel (в дальнейшем просто — Excel) — это программа выполнения расчетов и управления так называемыми электронными таблицами.
Excel позволяет выполнять сложные расчеты, в которых могут использоваться данные, расположенные в разных областях электронной таблицы и связанные между собой определенной зависимостью. Для выполнения таких расчетов в Excel существует возможность вводить различные формулы в ячейки таблицы. Excel выполняет вычисления и отображает результат в ячейке с формулой.
Важной особенностью использования электронной таблицы является автоматический пересчет результатов при изменении значений ячеек. Excel также может строить и обновлять графики, основанные на введенных числах.
Адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, C15.
Для написания формул используют адреса ячеек и знаки арифметических операций (+, -, *, /, ^). Формула начинается знаком =.
В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).
Диапазон ячеек обозначается следующим образом: A1:D4 (все ячейки прямоугольника от A1 до D4.
Адреса ячеек бывают относительными, абсолютными и смешанными.
Они по-разному ведут себя при копировании формулы из ячейки в ячейку.
Если в ячейке B2 мы напишем формулу =D1+3, то таблица воспримет это как «взять значение ячейки на две правее и на одну выше текущей, и прибавить к нему 3».
Т.е. адрес D1 воспринимается таблицей, как положение относительно ячейки, куда вводится формула. Такой адрес называется относительным. При копировании такой формулы в другую ячейку, таблица автоматически пересчитает адрес относительно нового расположения формулы:
Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле — поставить знак $ перед буквой и индексом ячейки: =$D$1+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:
Если же мы хотим, чтобы при копировании формулы автоматически пересчитывался, к примеру, только индекс ячейки, а буква оставалась неизменной, мы можем «закрепить» в формуле только букву (или наоборот): =$D1+3. Такой адрес называется смешанным. При копировании формулы будет меняться только индекс в адресе ячейки:
Электронные таблицы. Копирование формул.
В ячейке C2 записана формула =$E$3+D2. Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?
1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E3 4) =$F$4+D2
Место расположения формулы меняется с C2 на B1, т.е. формула сдвигается на одну ячейку влево и на одну ячейку вверх (буква «уменьшается» на единицу и индекс уменьшается на единицу). Значит, так же изменятся все относительные адреса, а абсолютные (закрепленные знаком $) останутся неизменными:
=$E$3+С1.
В ячейке В11 электронной таблицы записана формула. Эту формулу скопировали в ячейку А10. В результате значение в ячейке А10 вычисляется по формуле х—Зу, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке В11.
1) =C22-3*D22 2) =D$22-3*$D23 3) =C$22-3*D$22 4) =$C22-3*$D22
Проанализируем поочередно каждую формулу:
Место расположения формулы меняется с B11 на A10, т.е. буква «уменьшается» на 1 и индекс уменьшается на 1.
Тогда при копировании формулы изменятся следующим образом:
Условию задачи соответствует формула 2).
Электронные таблицы. Определение значения формулы.
Дан фрагмент электронной таблицы:
В ячейку D1 введена формула =$А$1*В1+С2, а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?
1) 10 2) 14 3) 16 4) 24
Место расположения формулы меняется с D1 на D2, т.е. буква не меняется, а индекс увеличивается на 1.
Значит, формула примет вид: =$А$1*В2+С3. Подставим в формулу числовые значения ячеек:1*5+9=14. Правильный ответ указан под номером 2.
В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно (-2). Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно 5?
1) 1 2) −1 3) −3 4) 7
По определению среднего значения:
СРЗНАЧ(A6:C6) = СУММ(A6:С6)/3 = −2
Значит, СУММ(A6:С6) = −6
СУММ(A6:D6) = СУММ(A6:С6)+D6 = −6+5 = −1
Электронные таблицы и диаграммы.
Дан фрагмент электронной таблицы в режиме отображения формул.
После выполнения вычислений построили диаграмму по значениям диапазона A1:D1. Укажите полученную диаграмму:
Вычислим по формулам значения ячеек A1:D1.
Этим данным соответствует диаграмма 3.
Ты нашел то, что искал? Поделись с друзьями!
Звоните нам: (бесплатный звонок по России) (бесплатный звонок по Москве)
Или нажмите на кнопку «Узнать больше», чтобы заполнить контактную форму. Мы обязательно Вам перезвоним.
Квадрат разлинован на N × N клеток (1
Пример входных данных (для таблицы размером 4 × 4):
1 | 8 | 8 | 4 |
10 | 1 | 1 | 3 |
1 | 3 | 12 | 2 |
2 | 3 | 5 | 6 |
Для указанных входных данных ответом должна быть пара чисел: 22 и 41.
Сначала найдём максимальную денежную сумму. Для этого найдём максимальную денежную сумму для каждой ячейки таблицы. Для каждой ячейки верхней строки это будет сумма всех ячеек слева от текущей. Для каждой ячейки левого столбца это будет сумма всех ячеек сверху от текущей. В ячейку P1 запишем формулу =СУММ($A$1:A1). Скопируем эту формулу во все ячейки в диапазоне Q1:AD1 и в диапазоне P2:P15. Для остальных ячеек будем сравнивать значение ячейки слева и значение ячейки сверху и присваивать текущей ячейке значение суммы той ячейки, в которой значение больше, и текущей ячейки. В Q2 запишем формулу =ЕСЛИ(P2>Q1;P2+B2;Q1+B2) и скопируем эту формулу во все ячейки диапазона Q2:AD15. Таким образом, в ячейке AD15 получим значение максимальной денежной суммы — 669.
Аналогичным образом найдём значение минимальной денежной суммы. Ячейки диапазонов P1:P15 и Q1:AD1 заполняются также, как при поиске максимальной денежной суммы. В Q2 запишем формулу =ЕСЛИ(P2 Задание 18 № 35476 Дан квадрат 15 × 15 клеток, в каждой клетке которого записано целое число. В левом верхнем углу квадрата стоит робот. За один ход робот может переместиться на одну клетку вправо, вниз или по диагонали вправо вниз. Выходить за пределы квадрата робот не может. Необходимо переместить робота в правый нижний угол так, чтобы сумма чисел в клетках, через которые прошёл робот (включая начальную и конечную), была максимальной. В ответе запишите максимально возможную сумму. Исходные данные записаны в электронной таблице. Пример входных данных (для таблицы размером 4 × 4):4 21 −36 11 37 −12 29 7 −30 24 −1 −5 8 −8 9 21
Для указанных входных данных ответом будет число 95 (робот проходит через клетки с числами 4, 37, 24, 9, 21).
Найдём максимальную сумму. Для этого найдём максимальную сумму для каждой ячейки таблицы. Для каждой ячейки верхней строки это будет сумма всех ячеек слева от текущей. Для каждой ячейки левого столбца это будет сумма всех ячеек сверху от текущей. В ячейку Q1 запишем формулу =СУММ($A$1:A1). Скопируем эту формулу во все ячейки в диапазоне R1:AE1 и в диапазоне Q2:Q15. Для остальных ячеек будем сравнивать значение ячейки слева, значение ячейки сверху и значение ячейки по диагонали слева сверху и присваивать текущей ячейке значение суммы той ячейки, в которой значение больше, и текущей ячейки. В R2 запишем формулу
=МАКС(Q2;Q1;R1)+B2
и скопируем эту формулу во все ячейки диапазона R2:AE15. Таким образом, в ячейке AE15 получим значение максимальной суммы — 820.
Задание 18 № 35907
Дан квадрат 15 × 15 клеток, в каждой клетке которого записано целое число. В правом верхнем углу квадрата стоит робот. За один ход робот может переместиться на одну клетку влево, вниз или по диагонали влево вниз. Выходить за пределы квадрата робот не может. Необходимо переместить робота в левый нижний угол так, чтобы сумма чисел в клетках, через которые прошёл робот (включая начальную и конечную), была максимальной. В ответе запишите максимально возможную сумму.
Исходные данные записаны в электронной таблице.
Пример входных данных (для таблицы размером 4 × 4):
4 | 21 | −36 | 11 |
37 | −12 | 29 | 7 |
−30 | 24 | −1 | −5 |
8 | −8 | 9 | 21 |
Для указанных входных данных ответом будет число 79 (робот проходит через клетки с числами 11, 7, 29, 24, 8).
Найдём максимальную сумму. Для этого найдём максимальную сумму для каждой ячейки таблицы. Для каждой ячейки верхней строки это будет сумма всех ячеек справа от текущей. Для каждой ячейки правого столбца это будет сумма всех ячеек сверху от текущей. В ячейку AE1 запишем формулу =СУММ(O1:$O$1). Скопируем эту формулу во все ячейки в диапазоне Q1:AD1 и в диапазоне AE2:AE15. Для остальных ячеек будем сравнивать значение ячейки справа, значение ячейки сверху и значение ячейки по диагонали справа сверху и присваивать текущей ячейке значение суммы той ячейки, в которой значение больше, и текущей ячейки. В AD2 запишем формулу
=МАКС(AD1;AE1;AE2)+N2
и скопируем эту формулу во все ячейки диапазона Q2:AD15. Таким образом, в ячейке Q15 получим значение максимальной суммы — 842.
Задание 18 № 35992
Дан квадрат 15×15 клеток, в каждой клетке которого записано целое число. В левом верхнем углу квадрата стоит робот. За один ход робот может переместиться на одну клетку вправо или на одну клетку вниз. Выходить за пределы квадрата робот не может. При этом ведётся подсчёт суммы по следующим правилам: число в очередной клетке, через которую проходит робот, включается в сумму, если оно больше числа в предыдущей клетке на пути робота. Если число в очередной клетке не больше числа в предыдущей, сумма не изменяется. Число в начальной клетке всегда включается в сумму. Необходимо переместить робота в правый нижний угол так, чтобы полученная сумма была максимальной. В ответе запишите максимально возможную сумму.
Исходные данные записаны в электронной таблице.
Пример входных данных (для таблицы размером 4 × 4):
44 | 42 | 89 | 37 |
18 | 35 | 50 | 20 |
6 | 41 | 26 | 64 |
7 | 9 | 70 | 85 |
Для указанных входных данных оптимальным маршрутом будет путь по клеткам 44, 42, 89, 50, 26, 70, 85. Итоговая сумма равна 44 + 89 + 70 + 85 = 288. Числа 42, 50 и 26 не включаются в сумму, так как 42 A1;B1+P1;P1). Скопируем эту формулу во все ячейки в диапазоне R1:AD1. В ячейку P2 запишем формулу =ЕСЛИ(A2>A1;A2+P1;P1). Скопируем эту формулу во все ячейки в диапазоне P3:P15. В ячейке Q2 запишем формулу
=МАКС(ЕСЛИ(B2>A2;B2+P2;P2);ЕСЛИ(B2>B1;B2+Q1;Q1))
и скопируем эту формулу во все ячейки диапазона Q2:AD15. Таким образом, в ячейке AD15 получим значение максимальной суммы — 1296.
Задание 18 № 36873
Дан квадрат 15×15 клеток, в каждой клетке которого записано целое число. В левом нижнем углу квадрата стоит робот. За один ход робот может переместиться на одну клетку вправо или на одну клетку вверх. Выходить за пределы квадрата робот не может. При этом ведётся подсчёт суммы по следующим правилам: число в очередной клетке, через которую проходит робот, включается в сумму, если оно больше числа в предыдущей клетке на пути робота. Если число в очередной клетке не больше числа в предыдущей, сумма не изменяется. Число в начальной клетке всегда включается в сумму. Необходимо переместить робота в правый верхний угол так, чтобы полученная сумма была максимальной. В ответе запишите максимально возможную сумму.
Исходные данные записаны в электронной таблице.
Пример входных данных (для таблицы размером 4 × 4):
44 | 42 | 89 | 37 |
18 | 35 | 50 | 20 |
6 | 41 | 26 | 64 |
7 | 9 | 70 | 85 |
Для указанных входных данных оптимальным маршрутом будет путь по клеткам 7, 9, 70, 26, 50, 89, 37. Итоговая сумма равна 7 + 9 + 70 + 50 + 89 = 225. Числа 26 и 37 не включаются в сумму, так как 26 A15;B15+P15;P15). Скопируем эту формулу во все ячейки в диапазоне R15:AD15. В ячейку P14 запишем формулу =ЕСЛИ(A14>A15;A14+P15;P15). Скопируем эту формулу во все ячейки в диапазоне P1:P13. В ячейке Q14 запишем формулу
=МАКС(ЕСЛИ(B14>A14;B14+P14;P14);ЕСЛИ(B14>B15;B14+Q15;Q15))
и скопируем эту формулу во все ячейки диапазона Q1:AD14. Таким образом, в ячейке AD1 получим значение максимальной суммы — 1403.
Сформируем последовательность 1, 2, 3, . Пусть в ячейке A2 введен первый элемент последовательности - значение 1 . В ячейку А3 , вводим формулу =А2+1 и копируем ее в ячейки ниже (см. файл примера ).
Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки , то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1 , затем в =А4+1 и т.д., тем самым формируя числовую последовательность 2, 3, 4, .
Если последовательность нужно сформировать в строке, то формулу нужно вводить в ячейку B2 и копировать ее нужно не вниз, а вправо.
Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7, . необходимо изменить формулу в ячейке А3 на =А2+2 . Чтобы сформировать последовательность 100, 200, 300, . необходимо изменить формулу на =А2+100 , а в ячейку А2 ввести 100.
Другим вариантом создания последовательности 1, 2, 3, . является использование формулы =СТРОКА()-СТРОКА($A$1) (если первый элемент последовательности располагается в строке 2 ). Формула =СТРОКА(A2)-СТРОКА($A$1) позволяет создать вертикальную последовательность, в случае если ее первый элемент последовательности располагается в любой строке. Тот же результат дают формулы =ЧСТРОК($A$1:A1) , =СТРОКА(A1) и =СТРОКА(H1) . Формула =СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1) создает последовательность, размещенную горизонтально. Тот же результат дают формулы =ЧИСЛСТОЛБ($A$1:A1) , =СТОЛБЕЦ(A1) .
Чтобы сформировать последовательность I, II, III, IV , . начиная с ячейки А2 , введем в А2 формулу =РИМСКОЕ(СТРОКА()-СТРОКА($A$1))
Сформированная последовательность, строго говоря, не является числовой, т.к. функция РИМСКОЕ() возвращает текст. Таким образом, сложить, например, числа I+IV в прямую не получится.
Другим видом числовой последовательности в текстовом формате является, например, последовательность вида 00-01 , 00-02, . Чтобы начать нумерованный список с кода 00-01 , введите формулу =ТЕКСТ(СТРОКА(A1);"00-00") в первую ячейку диапазона и перетащите маркер заполнения в конец диапазона.
Выше были приведены примеры арифметических последовательностей. Некоторые другие виды последовательностей можно также сформировать формулами. Например, последовательность n2+1 ((n в степени 2) +1) создадим формулой =(СТРОКА()-СТРОКА($A$1))^2+1 начиная с ячейки А2 .
Создадим последовательность с повторами вида 1, 1, 1, 2, 2, 2. Это можно сделать формулой =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1) . С помощью формулы =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2 получим последовательность 2, 2, 2, 2, 4, 4, 4, 4. , т.е. последовательность из четных чисел. Формула =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1 даст последовательность 1, 1, 1, 1, 3, 3, 3, 3, .
Примечание . Для выделения повторов использовано Условное форматирование .
Формула =ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1 даст последовательность 1, 2, 3, 4, 1, 2, 3, 4, . Это пример последовательности с периодически повторяющимися элементами.
Используем клавишу CTRL
Пусть, как и в предыдущем примере, в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая клавишу CTRL , скопируем Маркером заполнения (при этом над курсором появится маленький плюсик), значение из A 2 в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …
ВНИМАНИЕ! Если на листе часть строк скрыта с помощью фильтра , то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL , выделите любую ячейку с заголовком фильтра и дважды нажмите CTRL + SHIFT + L (сбросьте фильтр).
Используем правую клавишу мыши
Пусть в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши появится контекстное меню, в котором нужно выбрать пункт Заполнить . Получим последовательность чисел 1, 2, 3, 4 …
Используем начало последовательности
Если начало последовательности уже задано (т.е. задан первый элемент и шаг последовательности), то создать последовательность 1, 2, 3, . можно следующим образом:
Получаем результат как в предыдущем случае. Если в ячейке А3 введено значение 3 , т.е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.
Создадим последовательность вида 1, 2, 3, 1, 2, 3. для этого введем в первые три ячейки значения 1, 2, 3, затем маркером заполнения , удерживая клавишу CTRL , скопируем значения вниз.
Использование инструмента Прогрессия
Воспользуемся стандартным средством EXCEL для создания прогрессий, в то числе и арифметических.
- вводим в ячейку А2 значение 1 ;
- выделяем диапазон A2:А6 , в котором будут содержаться элементы последовательности;
- вызываем инструмент Прогрессия ( Главная/ Редактирование/ Заполнить/ Прогрессия. ), в появившемся окне нажимаем ОК.
Использование в работе : Подходы для создания числовых последовательностей можно использовать для нумерации строк , сортировки списка с числами , разнесения значений по столбцам и строкам .
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О последовательностях значений в формате дат (и времени) вида 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . можно прочитать в статье Последовательности дат и времен . О массивах значений, содержащих последовательности конечной длины, используемых в формулах массива , читайте в статье Массив значений (или константа массива или массив констант) .
В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании Именованных формул , задания правил Условного форматирования и при формировании условий Проверки данных .
В подавляющем большинстве формул EXCEL используются ссылки на ячейки. Например, если в ячейке В1 содержится формула =А1+5 , то означает, что в ячейку В1 будет помещено значение ячейки А1 находящейся на пересечении столбца А и строки 1 , к которому прибавлено число 5. Также в формулах используются ссылки на диапазоны ячеек, например, формула =СУММ(А2:А11) вычисляет сумму значений из ячеек А2 , А3 , . А11 . Однако, формула =СУММ($А$2:$А$11) также вычисляет сумму значений из тех же ячеек. Тогда в чем же разница? Разница проявляется при копировании этой формулы в соседние ячейки.
Абсолютная адресация (абсолютные ссылки)
Для создания абсолютной ссылки используется знак $. Ссылка на диапазона записывается ввиде $А$2:$А$11 . Абсолютная ссылка позволяет при копировании формулы зафиксировать адрес диапазона или адрес ячейки. Рассмотрим пример.
Пусть в ячейке В2 введена формула =СУММ( $А$2:$А$11 ) , а в ячейке С2 формула =СУММ(А2:А11). Скопировав формулы вниз, например с помощью Маркера заполнения, во всех ячейках столбца В получим одну и ту же формулу =СУММ( $А$2:$А$11 ) , т.е. ссылка на диапазон ячеек при копировании не изменилась . А в столбце С получим другой результат: в ячейке С3 будет формула =СУММ(A3:A12) , в ячейке С4 будет формула =СУММ(A4:A13) и т.д. Т.е. при копировании ссылка была модифицирована .
Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.
Другой пример.
Пусть в диапазоне А1:А5 имеются числа (например, зарплата сотрудников отдела), а в С1 – процент премии установленный для всего отдела. Для подсчета премии каждого сотрудника необходимо все зарплаты умножить на % премии. Рассчитанную премию поместим в диапазоне В1:В5 . Для этого введем в ячейку В1 формулу =А1*С1 . Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в В2:В5 нули (при условии, что в диапазоне С2:С5 нет никаких значений). В ячейке В5 будем иметь формулу =А5*С5 (EXCEL при копировании формулы модифицировал ссылки на ячейки, т.к. их адреса не были записаны в виде абсолютных ссылок).
Чтобы выйти из ситуации - откорректируем формулу в ячейке В1 .
Такм образом, введем в В1 формулу =А1*$С$1 . Это можно сделать и в ручную, введя знак $ перед буквой столбца и перед номером строки.
Нажмем ENTER и протянем ее вниз. Теперь в В5 будет правильная формула =А5*$С$1 . Всем сотрудникам теперь достанется премия :).
Относительная адресация (относительные ссылки)
Введем в ячейку B1 формулу =А1 , представляющую собой относительную ссылку на ячейку А1 . Что же произойдет с формулой при ее копировании в ячейки расположенные ниже В1 ? После протягивания ее вниз Маркером заполнения , в ячейке В5 будет стоять формула =А5 , т.е. EXCEL изменил первоначальную формулу =A1 . При копировании вправо в ячейку С1 формула будет преобразована в =В1.
Теперь примеры.
Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.
Т.е. в B2 должна быть формула: =СУММ(A1:A2) , в B3 : =СУММ(A2:A3) и т.д.
Решить задачу просто: записав в B2 формулу =СУММ(A1:A2) , протянем ее с помощью Маркера заполнения в ячейку B3 и ниже.
Альтернативное решение
Другим вариантом решения этой задачи является использование Именованной формулы . Для этого:
- выделите ячейку B2 (это принципиально при использовании относительных ссылок в Именах ). Теперь B2 – активная ячейка;
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите, например Сумма2ячеек ;
- убедитесь, что в поле Диапазон введена формула =СУММ(A1:A2)
- Нажмите ОК.
Теперь в B2 введем формулу = Сумма2ячеек . Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист пример1 ). Если формулу ввести в ячейку B5 , то она будет суммировать ячейки A4:A5 , если ввести в D10 , то – ячейки С9:С10 .
Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).
Относительная адресация при создании формул для Условного форматирования.
Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист пример2 ). Построим такую таблицу:
Важно отметить, что, если бы, при создании правила, активной ячейкой была F11 , то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $: B2 на F11 и $A2 на $A11 .
Внимание! При использовании относительной адресации в Именованных формулах , Именованных диапазонах , Условном форматировании , Проверке данных (примеры см. в соответствующих статьях) необходимо следить, какая ячейка является активной в момент создания формулы (активной может быть только одна ячейка на листе, не смотря на то, что выделено может быть несколько).
Смешанные ссылки
Смешанные ссылки имеют формат =$В3 или =B$3 . В первом случае при копировании формулы фиксируется ссылка на столбец B , а строка может изменяться в зависимости при копировании формулы.
Предположим, у нас есть столбец с ценами в диапазоне B 3: B 6 (см. файл примера , лист пример3 ). В столбцах С, D , Е содержатся прогнозы продаж в натуральном выражении по годам (в шт.). Задача: в столбцах F , G , H посчитать годовые продажи в рублях, т.е. перемножить столбцы С, D , Е на столбец B . Использование механизма относительной адресации позволяет нам ввести для решения задачи только одну формулу. В ячейку F вводим: =$В3*C3 . Потом протягиваем формулу маркером заполнения вниз до F 6 ,
а затем весь столбец таблицы протягиваем вправо на столбцы G и H .
Обратите внимание, что в формуле =$В3*C3 перед столбцом B стоит значок $. При копировании формулы =$В3*C3 в ячейки столбцов F, G и H , этот значок $ говорит EXCEL о том, что ссылку на столбец B модифицировать не нужно. А вот перед столбцом С такого значка нет и формула в ячейке H6 примет вид =$В6*E6 .
Вводим знак $ в адрес ячейки
Существует несколько возможностей при вводе формулы ввести знак $ в адрес ячейки или диапазона. Рассмотрим ввод на примере формулы =СУММ($А$2:$А$5)
1. Ввести знак $ можно вручную, последовательно вводя с клавиатуры все знаки =СУММ($А$2:$А$5)
2. С помощью клавиши F4 (для ввода абсолютной ссылки):
- Введите часть формулы без ввода $: =СУММ(А2:А5
- Затем сразу нажмите клавишу F4 , знаки $ будут вставлены автоматически: =СУММ( $А$2:$А$5
- Для окончания ввода формулы нажмите ENTER.
Если после ввода =СУММ(А2:А5 в формуле передвинуть курсор с помощью мыши в позицию левее,
а затем вернуть его в самую правую позицию (также мышкой),
то после нажатия клавиши F4 , знаки $ будут автоматически вставлены только во вторую часть ссылки! =СУММ( А2:$А$5
Чтобы вставить знаки $ во всю ссылку, выделите всю ссылку А2:$А$5 или ее часть по обе стороны двоеточия, например 2:$А , и нажмите клавишу F4. Знаки $ будут автоматически вставлены во всю ссылку $А$2:$А$5
3. С помощью клавиши F4 (для ввода относительной ссылки).
- Введите часть формулы без ввода $: =СУММ(А2:А5
- Затем сразу нажмите клавишу F4 , будут автоматически вставлены знаки $: =СУММ( $А$2:$А$5
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А$2:А$5 (фиксируются строки)
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ($ А2:$А5 (фиксируется столбец)
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А2:А5 (относительная ссылка). Последующие нажатия изменяют ссылку заново по кругу.
- Для окончания ввода нажмите ENTER.
Чтобы изменить только первую или втрорую часть ссылки - установите мышкой курсор в нужную часть ссылки и последовательно нажимайте клавушу F4.
"СуперАбсолютная" адресация
В заключении расширим тему абсолютной адресации. Предположим, что в ячейке B 2 находится число 25, с которым необходимо выполнить ряд вычислений, например, возвести в разные степени (см. файл примера , лист пример4 ). Для этого в столбце C напишем формулу возведения в степень (значения степени введем в столбец D ): =$B$2^$D2 .
Мы использовали абсолютную ссылку на ячейку B 2 . При любых изменениях положения формулы абсолютная ссылка всегда будет ссылаться на ячейку, содержащую наше значение 25 :
- при копировании формулы из С3 в Н3 – формула не изменится, и мы получим правильный результат 625 ;
- при вставке нового столбца между столбцами А и В – формула превратится в =$C$2^$E3 , но мы снова получим правильный результат 625 .
Все правильно, т.к. это и есть суть абсолютной адресации: ссылки автоматически модифицируются для сохранения адресации на нужные ячейки при любых модификациях строк и столбцах листа (ну, кроме удаления ячейки с формулой, конечно). Однако бывают ситуации, когда значения на лист попадают из внешних источников. Например, когда созданный пользователем макрос вставляет внешние данные в ячейку B 2 (т.е. всегда во второй столбец листа). Теперь, при вставке столбца между столбцами А и В – формула как и раньше превратится в =$C$2^$E3 , но т.к. исходное число (25) будет вставляться макросом не в С2 , а по прежнему в ячейку B 2 , и мы получим неправильный результат.
Вопрос: можно ли модифицировать исходную формулу из С2 ( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?
Решение заключается в использовании функции ДВССЫЛ() , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ("B2") , то она всегда будет указывать на ячейку с адресом B2 вне зависимости от любых дальнейших действий пользователя, вставки или удаления столбцов и т.д.
Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :
При ссылке на ячейку В2 с другого листа =ДВССЫЛ("пример4!B2") может возникнуть и другая сложность: при изменении названия листа пример4 – формула перестает работать. Но это также можно обойти – см. пример из статьи Определяем имя листа .
Другим способом заставить формулу ссылаться на один и тот же столбец является использование функции СМЕЩ() – об этом читайте статью Как заставить формулу все время ссылаться на один и тот же столбец .
Читайте также: