Формулы в виде таблицы

Формулы в виде таблицы

Любые нецензурные комментарии будут удалены, а их авторы занесены в черный список!

Добро пожаловать на OnlineMSchool.
Меня зовут Довжик Михаил Викторович. Я владелец и автор этого сайта, мною написан весь теоретический материал, а также разработаны онлайн упражнения и калькуляторы, которыми Вы можете воспользоваться для изучения математики.

Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Оператор Операция Пример
+ (плюс) Сложение =В4+7
— (минус) Вычитание =А9-100
* (звездочка) Умножение =А3*2
/ (наклонная черта) Деление =А7/А8
^ (циркумфлекс) Степень =6^2
= (знак равенства) Равно
Больше
= Больше или равно
<> Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.

Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

  1. Поставили курсор в ячейку В3 и ввели =.
  2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
  3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.

Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.

  1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
  2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
  3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

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

Читайте также:  Купоны на скидку в steam

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

  1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
  2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
  3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

  1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
  2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
  3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

  • $В$2 – при копировании остаются постоянными столбец и строка;
  • B$2 – при копировании неизменна строка;
  • $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

Чтобы сэкономить время при введении однотипных формул в ячейки таблицы, применяются маркеры автозаполнения. Если нужно закрепить ссылку, делаем ее абсолютной. Для изменения значений при копировании относительной ссылки.

Простейшие формулы заполнения таблиц в Excel:

  1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+"=", чтобы вставить столбец.
  2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
  3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
  4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом.

При пользовании «Инфоуроком» вам не нужно платить за интернет!

Минкомсвязь РФ: «Инфоурок» включен в перечень социально значимых ресурсов .

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

Вводить формулу надо со знака равно ( = ). Это надо для того, чтобы Excel понял, что в ячейку вводится именно формула, а не данные.

Создать простую формулу можно с помощью констант и арифметических операторов. Например, формула =5+2*3 перемножает два числа и прибавляет к результату третье. В Microsoft Office Excel используется стандартный порядок вычисления математических операций. В предыдущем примере сначала выполняется операция умножения (2*3), а затем к результату прибавляется число 5.

Арифметические операторы

«+» — сложение (Пример: «=1+1»);

«-» — вычитание (Пример: «=1-1»);

«*» — умножение (Пример: «=2*3»);

«/» — Деление (Пример: «=1/3»);

«^» — Возведение в степень (Пример: «=2^10»);

«%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). Результатом вычисления любого арифметического выражения будет число.

Ход работы (первый лист «ввод формулы»)

Задание 1. Введите арифметическую формулу 5+7,5*2 в ячейку Е2.

Читайте также:  Как читать канал в телеграмме

В ячейке Е2 введите следующую формулу: =5+7,5*2

Нажмите клавишу Enter . В ячейке Е2 получилось значение 20.

Задание 2. Введите формулу 10*8-Е2*2+10 (с ссылкой на ячейку Е2).

В ячейке введите следующую формулу: =10*8 – Е2*2+10 . Записывая в формулу адрес ячейки Е2 – Вы ссылаетесь на значение, которое находится в этой ячейке, в нашем случае, значение ячейки Е2=20. Чтобы сослаться на ячейку, достаточно просто кликнуть на неё, она подсветится цветной рамочкой, а запись её в формуле будет окрашена в тот же цвет.

Нажмите клавишу Enter . В ячейке Е7 получилось значение 50.

Задание 3. Введите логические формулы следующих выражений: 5>2 10

Значением логической формулы является ответ ИСТИНА, если условие выполняется, и ЛОЖЬ, если условие не выполняется.

Логические операторы

">=" — больше, либо равно;

"=" — равно (проверка на равенство);

"<>" — неравно (проверка на неравенство).

Введите в ячейках следующие формулы:

После введения формулы нажмите клавишу Enter . У Вас должны получиться следующие значения в ячейках:

Задание 4. Соедините два слова «коробка» и «конфет» из разных ячеек в одно выражение «коробка конфет», которое будет находиться в одной ячейке.

Введите в ячейке следующую формулу: =СЦЕПИТЬ( D 17;” “; D 19)

где СЦЕПИТЬ – функция сцепления двух и более строк;

D 17 – ссылка на ячейку со значением «коробка»;

« » — пробел, обрамленный в кавычках, т.к. в данном случае это строчный символ;

D 19 – ссылка на ячейку со значением «конфет».

Аналогичная формула может быть записана так: = D 17&” “& D 19

где D 17 и D 19 ссылки на ячейки со словами;

« » — пробел, обрамленный в кавычках, т.к. в данном случае это строчный символ;

& — Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк.

В ячейке с введенными формулами должна получиться строка «коробка конфет».

Задание 5. Введите формулу: ,где заданы значения x и y

Введите в ячейке следующую формулу: =(E24^2-4*F24)/(2*КОРЕНЬ(100)-3)

где E24 и F24 – ссылки на значение ячеек х и y ;

^ — возведение числа в степень;

КОРЕНЬ(число) – корень из числа.

После введения формулы нажмите клавишу Enter. У Вас должно получиться следующие значение в ячейке:

Задание 6. В коробке 20 конфет, масса брутто = 250 гр. Вес коробки составляет 20% от общей массы. Найдите вес одной конфеты.

Разберем решение задачи по действиям:

Сначала найдем вес коробки: 250г:100*20=50 , в excel это выражение можно записать следующим образом: 250*20%

Далее найдем вес 20-ти конфет: 250-50=200

И наконец, найдем вес одной конфеты: 200:20=10

В Excel в ячейке можно записать эти действия в одну формулу следующим образом:

После введения формулы нажмите клавишу Enter. У Вас должны получиться следующие значение в ячейке:

Ход работы (второй лист «сложные формулы»)

Задание 7. Заполните таблицу с использованием разных видов ссылок на ячейки.

Столбец F заполним формулами с относительными ссылками. Для этого в ячейке F 3 введите следующую формулу: =Е3+1

Относительные ссылки . Относительная ссылка в формуле, в нашем случае Е3, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании формулы на нижние смежные ячейки, из ячейки F 3 в ячейку F 4, она автоматически изменяется с =Е3+1 на =Е4+1.

Далее заполняем столбец F до конца и также заполняем столбец G , для этого из ячейки F 3 скопируйте формулу в ячейку G 3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите как изменились формулы.

Читайте также:  Самый тяжелый язык программирования

Столбец H заполним формулами с абсолютными ссылками. Для этого в ячейке H 3 введите следующую формулу: =$E$3+1

Чтобы сделать ячейку абсолютной, нужно ее обрамить значками доллара, сделать это можно при помощи клавиши F 4, предварительно поставив курсор перед адресом ссылки. Или проставив эти значки в ручную при помощи комбинации клавиш Shift +4 (на латинице).

Абсолютные ссылки. Абсолютная ссылка ячейки в формуле, в нашем случае, $Е$3, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Например, при копировании или заполнении абсолютной ссылки из ячейки Н3 в ячейку Н4 она остается прежней =$Е$3.

Далее заполняем столбец H до конца и также заполняем столбец I , для этого из ячейки H 3 скопируйте формулу в ячейку I 3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите изменились ли формулы.

Столбец J заполним формулами с смешанными ссылками. Для этого в ячейке J3 введите следующую формулу: =E$3+1

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т.д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Например, при копировании или заполнении смешанной ссылки из ячейки J 3 в ячейку J 4 она не изменяется с = E $3+1 на = E $4

Далее заполняем столбец J до конца и также заполняем столбец K , для этого из ячейки J 3 скопируйте формулу в ячейку K 3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите как меняются формулы.

Здесь ссылки в формуле меняются по столбцам, а по строкам нет, т.к. мы зафиксировали одну строку – третью, из ее excel и берет значения.

Столбец L заполним формулами с смешанными ссылками, но на этот раз закрепим столбец. Для этого в ячейке J3 введите следующую формулу: =$E3+1

Скопируйте формулу из ячейки L 3 в ячейку L 4, обратите внимание как изменилась ссылка в формуле:

Далее заполняем столбец L до конца и также заполняем столбец M , для этого из ячейки L 3 скопируйте формулу в ячейку M 3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите как меняются формулы.

Здесь ссылки в формуле меняются по строкам, а по столбцам нет, т.к. мы зафиксировали один столбец – Е, из него excel и берет значения.

В итоге, у Вас должна получиться следующая таблица:

Задание 8. Ввести такие формулы в таблицу, чтобы можно было, введя общее число порций, получить необходимое количество продуктов.

Итак, мы имеем таблицу вида:

В ячейку Н22 введем формулу: «всего порций» * «граммов на 1 порцию», при этом ячейку «всего порций» нам нужно сделать абсолютной, для этого обрамим ее символами $, формула имеет вид: =G22*$H$20

Скопируем (растянем) формулу в соседние нижние ячейки, получим:

Теперь при вводе значения «всего порций» в ячейку Н20, в ячейках «всего (г.)» считается необходимое количество продуктов для заданного количества порций.

Задание 9. Введите соответствующие формулы:

К сумме диапазона ячеек (Е 22 :Е 31 ) прибавить 5 и разделить все на 3.

Введите в ячейку G 22 следующую формулу: =(СУММ(E22:E31)+5)/3

где СУММ(E22:E31) – функция суммирования, в данном случае диапазона ячеек,

соблюдайте расстановку скобок.

Среднее значение диапазона ячеек (Е22:Е31) умножить на 10 корней из 4.

Введите в ячейке G 24 следующую формулу: =СРЗНАЧ(E22:E31)*10*КОРЕНЬ(4)

где СРЗНАЧ(E22:E31) – функция вычисления среднего значения из диапазона ячеек,

КОРЕНЬ(4) – функция вычисления корня из числа.

Ссылка на основную публикацию
Установить программу для сканирования документов бесплатно
Загрузите бесплатно пробную полнофункциональную версию программы для сканирования Scanitto Pro. Данная версия работает без каких-либо ограничений в течение 30 дней....
Террария мешок с сюрпризом
Мешок с сокровищамиTreasure Bag Характеристики Тип Мешок Подсказка Открывать правой кнопкой мыши Редкость Внутренний ID предмета: 3318-3332 Эксклюзивный контент ПК...
Термопринтер для печати чеков
Термопринтер для чеков — это печатное устройство, которое применяется в торговле и ряде других сфер. Большинство моделей являются стационарными и...
Установить протокол mtp media transfer protocol
Описание Компания Microsoft содержит под своим крылом множество драйверов, среди этой коллекции находится и Media Transfer Protocol, тот самый драйвер,...
Adblock detector