Как считать проценты от числа
Для подсчета процентов в электронной таблице выберите ячейку для ввода расчетной формулы. Поставьте знак «равно», затем напишите адрес ячейки (используйте английскую раскладку), в которой находится число, процент от которого будете вычислять. Можно просто кликнуть мышкой в эту ячейку и адрес вставится автоматически. Далее ставим знак умножения и вводим число процентов, которое необходимо вычислить. Посмотрите на пример вычисления скидки при покупке товара. Формула =C4*(1-D4)
Вычисление стоимости товара с учетом скидки
В C4 записана цена пылесоса, а в D4 – скидка в %. Необходимо вычислить стоимость товара с вычетом скидки, для этого в нашей формуле используется конструкция (1-D4). Здесь вычисляется значение процента, на которое умножается цена товара. Для Excel запись вида 15% означает число 0.15, поэтому оно вычитается из единицы. В итоге получаем остаточную стоимость товара в 85% от первоначальной.
Вот таким нехитрым способом с помощью электронных таблиц можно быстро вычислить проценты от любого числа.
Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.
Если Excel при нажатии кнопки Зависимые зависимые или Зависимые зависимые , Excel отследил все уровни формулы или пытается отследить элемент, который невозможно найти. Следующие элементы на этих таблицах, на которые могут ссылаться формулы, невозможно отследить с помощью средств аудита:
-
Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.
-
Отчеты для отчетов в отчетах.
-
Ссылки на именуемые константы.
-
Формулы, расположенные в другой книге, которые ссылаются на активную ячейку, если другая книга закрыта.
Примечания:
-
Чтобы увидеть выделение цветом влияющих ячеек для аргументов формулы, выделите ячейку и нажмите клавишу F2.
-
Для выбора ячейки на другом конце стрелки дважды щелкните эту стрелку. Если ячейка находится на другом листе или в другой книге, дважды щелкните черную стрелку для отображения диалогового окна Переход, а затем дважды щелкните требуемую ссылку в списке Переход.
-
Все стрелки трассировки исчезают, если изменить формулу, на которую указывают стрелки, вставить или удалить столбцы или строки либо удалить или переместить ячейки. Для восстановления стрелок зависимости после сделанных изменений, необходимо снова использовать на этом листе команды аудита. Для отслеживания первоначальных стрелок зависимости следует до внесения изменений распечатать лист с отображенными стрелками зависимости.
Арифметическая прогрессия в Excel
Рассмотрим 2 способа задания прогрессии в Excel — с помощью стандартного инструмента Прогрессия и через формулы.
В первом случае на панели вкладок выбираем Главная -> Редактирование -> Заполнить -> Прогрессия:
Далее мы увидим диалоговое окно с настройками параметров:
В данных настройках мы можем выбрать дополнительные параметры, которые позволят нам более детально настроить и заполнить прогрессию в Excel:
- Расположение — расположение заполнения (по столбцам или строкам);
- Тип — тип (арифметическая, геометрическая, даты и автозаполнение);
- Единицы — вид данных (при выборе даты в качестве типа);
- Шаг — шаг (для арифметической) или знаменатель (для геометрической);
- Автоматическое определение шага — автоматическое определение шага, если заданы несколько значений последовательности;
- Предельное значение — ограничение по значению последнего элемента последовательности.
Разберем как сделать арифметическую прогрессию в Excel на конкретном примере.
Создадим набор чисел 3, 7, 11, … , то есть первый элемент равняется 3, а шаг равен 4.
Выделяем диапазон (к примеру, A1:J1) в котором мы хотим разместить набор чисел (диапазон можно и не выделять, однако в этом случае в настройках будет необходимо указать предельное значение), где в первой ячейке будет указан первый элемент (в нашем примере это 3 в ячейке A1), и указываем параметры (расположение, тип, шаг и т.д.):
В результате мы получим заполненный диапазон с заданным набором чисел:
Аналогичный результат можно получить и при задании элементов с помощью формул.
Для этого также задаем начальный элемент в первой ячейке, а в последующих ячейках указываем рекуррентную формулу члена арифметической прогрессии (то есть текущий член получается как сумма предыдущего и шага):
Примеры построения различных представлений логических функций
Построим совершенные дизъюнктивную и дизъюнктивную нормальные формы, а также полином Жегалкина для функции трёх переменных F = ¬ab∨¬bc∨ca
1. Построим таблицу истинности для функции
| a | b | c | ¬a | ¬a∧b | ¬b | ¬b∧c | ¬a∧b∨¬b∧c | c∧a | ¬a∧b∨¬b∧c∨c∧a |
| 1 | 1 | ||||||||
| 1 | 1 | 1 | 1 | 1 | 1 | ||||
| 1 | 1 | 1 | 1 | 1 | |||||
| 1 | 1 | 1 | 1 | 1 | 1 | ||||
| 1 | 1 | ||||||||
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||
| 1 | 1 | ||||||||
| 1 | 1 | 1 | 1 | 1 |
Построение совершенной дизъюнктивной нормальной формы:
Найдём наборы, на которых функция принимает истинное значение: { 0, 0, 1 } { 0, 1, 0 } { 0, 1, 1 } { 1, 0, 1 } { 1, 1, 1 }
В соответствие найденным наборам поставим элементарные конъюнкции по всем переменным, причём если переменная в наборе принимает значение 0, то она будет записана с отрицанием:
K1: { 0, 0, 1 } — ¬a¬bc
K2: { 0, 1, 0 } — ¬ab¬c
K3: { 0, 1, 1 } — ¬abc
K4: { 1, 0, 1 } — a¬bc
K5: { 1, 1, 1 } — abc
K1 ∨ K2 ∨ K3 ∨ K4 ∨ K5 = ¬a¬bc ∨ ¬ab¬c ∨ ¬abc ∨ a¬bc ∨ abc
Построение совершенной конъюнктивной нормальной формы:
Найдём наборы, на которых функция принимает ложное значение: { 0, 0, 0 } { 1, 0, 0 } { 1, 1, 0 }
В соответствие найденным наборам поставим элементарные дизъюнкции по всем переменным, причём если переменная в наборе принимает значение 1, то она будет записана с отрицанием:
D1: { 0, 0, 0 } — a∨b∨c
D2: { 1, 0, 0 } — ¬a∨b∨c
D3: { 1, 1, 0 } — ¬a∨¬b∨c
D1 ∧ D2 ∧ D3 = (a∨b∨c) ∧ (¬a∨b∨c) ∧ (¬a∨¬b∨c)
Построение полинома Жегалкина:
Добавим новый столбец к таблице истинности и запишем в 1, 3, 5 и 7 строки значения из тех же строк предыдущего столбца таблицы истинности, а значения в строках 2, 4, 6 и 8 сложим по модулю два со значениями из соответственно 1, 3, 5 и 7 строк:
| a | b | c | F | 1 | |
| → | |||||
| 1 | 1 | ⊕ 0 | 1 | ||
| 1 | 1 | → | 1 | ||
| 1 | 1 | 1 | ⊕ 1 | ||
| 1 | → | ||||
| 1 | 1 | 1 | ⊕ 0 | 1 | |
| 1 | 1 | → | |||
| 1 | 1 | 1 | 1 | ⊕ 0 | 1 |
Добавим новый столбец к таблице истинности и запишем в 1 и 2, 5 и 6 строки значения из тех же строк предыдущего столбца таблицы истинности, а значения в строках 3 и 4, 7 и 8 сложим по модулю два со значениями из соответственно 1 и 2, 5 и 6 строк:
| a | b | c | F | 1 | 2 | |
| → | ||||||
| 1 | 1 | 1 | → | 1 | ||
| 1 | 1 | 1 | ⊕ 0 | 1 | ||
| 1 | 1 | 1 | ⊕ 1 | 1 | ||
| 1 | → | |||||
| 1 | 1 | 1 | 1 | → | 1 | |
| 1 | 1 | ⊕ 0 | ||||
| 1 | 1 | 1 | 1 | 1 | ⊕ 1 |
Добавим новый столбец к таблице истинности и запишем в 1 2, 3 и 4 строки значения из тех же строк предыдущего столбца таблицы истинности, а значения в строках 5, 6, 7 и 8 сложим по модулю два со значениями из соответственно 1, 2, 3 и 4 строк:
| a | b | c | F | 1 | 2 | 3 | |
| → | |||||||
| 1 | 1 | 1 | 1 | → | 1 | ||
| 1 | 1 | 1 | 1 | → | 1 | ||
| 1 | 1 | 1 | 1 | → | 1 | ||
| 1 | ⊕ 0 | ||||||
| 1 | 1 | 1 | 1 | 1 | ⊕ 1 | ||
| 1 | 1 | ⊕ 1 | 1 | ||||
| 1 | 1 | 1 | 1 | 1 | ⊕ 1 | 1 |
Окончательно получим такую таблицу:
| a | b | c | F | 1 | 2 | 3 |
| 1 | 1 | 1 | 1 | 1 | ||
| 1 | 1 | 1 | 1 | 1 | ||
| 1 | 1 | 1 | 1 | 1 | ||
| 1 | ||||||
| 1 | 1 | 1 | 1 | 1 | ||
| 1 | 1 | 1 | ||||
| 1 | 1 | 1 | 1 | 1 | 1 |
Выпишем наборы, на которых получившийся вектор принимает единичное значение и запишем вместо единиц в наборах имена переменных, соответствующие набору (для нулевого набора следует записать единицу):
{ 0, 0, 1 } — c, { 0, 1, 0 } — b, { 0, 1, 1 } — bc, { 1, 1, 0 } — ab, { 1, 1, 1 } — abc
Объединяя полученные конъюнкции с помощью операции исключающего или, получим полином Жегалкина: c⊕b⊕bc⊕ab⊕abc
Отображение связей между формулами и ячейками
Если в формуле используются влиятельные или зависимые ячейки, может быть сложно проверить формулы на точность или найти источник ошибки.
Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.
Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.
Для проверки формул можно использовать команды «Ячейки трассировки» и «Зависимые ячейки» для графического отображения и трассировки связей между этими ячейками и формулами с помощью стрелки трассировки, как показано на рисунке.
Чтобы отобразить связи между ячейками, выполните указанные здесь действия.
Щелкните Файл > параметры > Дополнительные параметры.
Примечание: Если вы используете Excel 2007; нажмите кнопку Microsoft Office
В разделе Показать параметры для этой книги выберите книгу и убедитесь, что выбрано все в разделе Для объектов, показать.
Чтобы указать ссылки на ячейки в другой книге, эта книга должна быть открыта. Microsoft Office Excel не может перейти к ячейке книги, если она не открыта.
Выполните одно из указанных ниже действий.
Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, которая непосредственно предоставляет данные активной ячейке, на вкладке Формулы в группе Зависимости формул нажмите кнопку Ячейки трассировки
Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой
Чтобы определить следующий уровень ячеек, которые предоставляют данные для активной ячейки, снова нажмите кнопку
Чтобы удалить стрелки трассировки по одному уровню, начните с влияемой ячейки, которая дальше от активной ячейки. Затем на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить стрелки
Укажите ячейку, для которой следует найти зависимые ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, зависимой от активной ячейки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Зависимые ячейки
Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку ссылается ячейка на другом месте или в другой книге, при наконечнике выбранной ячейки на значок
Чтобы определить следующий уровень ячеек, зависящих от активной ячейки, снова нажмите кнопку Зависимые ячейки
Чтобы удалить стрелки зависимости по одному уровню, начиная с зависимой ячейки, наиболее далекой от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить зависимые стрелки
В пустой ячейке введите = (знак равно).
Нажмите кнопку Выделить все.
Вы выберите ячейку, а затем на вкладке Формулы в группе Зависимости формул дважды нажмите кнопку
Чтобы удалить все стрелки трассировки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Удалить стрелки
Как в Excel построить график?
- Создать таблицу с исходными данными
- Выделить столбец с теми данными, которые будут отображаться на оси Y
- Выполнить команду Вставка-График
- На графике вызвать контекстное меню — Выбрать данные — Подписи горизонтальной оси — Изменить — Выбрать диапазон значений для оси Х — ОК.
Итак, это был краткий алгоритм того, как создать график в Excel, теперь более подробно на примерах:
Для начала, нам нужно сделать таблицу с исходными данными, в которой в разных столбцах будут:
- данные для отображения на оси X (как правило, значения, от которых что-то зависит, в графиках функций — значения переменной x либо даты, месяца)
- данные для отображения на оси Y (обычно значения показателя, который изменяется в зависимости от X)
Например, чтобы построить график функции в Excel, создадим таблицу, в которой уже выполнен расчет функции y = x2:
Затем, выделим столбец со значениями Y (вместе с названием столбца)
На панели инструментов в группе команд «Вставка» выбираем «График» и щелкаем по понравившемуся образцу мышкой.
Как мы видим, у нас появился график, вполне себе красивый и хороший, но на оси Х отображаются числа от 1 до 15, а не значения нашей переменной Х. Как это изменить?
Щелкаем правой кнопкой мыши на графике, выбираем пункт — Выбрать данные — Подписи горизонтальной оси — Изменить
И в появившемся окошке «Диапазон подписей оси» нажимаем кнопку выбора и выделяем все ячейки, содержащие значения для отображения на оси Х.
Далее нажимаем снова на кнопку выбора данных и ОК, и еще раз ОК.
В результате у Вас получится график функции, на оси Х будут значения Х (первого столбца таблицы), на оси Y — значения второго столбца таблицы — Y = X2
Как в Excel построить график функции
Видеоурок: Как в excel построить график
teacher
Информация воспринимается легче, если представлена наглядно. Один из способов презентации отчетов, планов, показателей и другого вида делового материала – графики и диаграммы. В аналитике это незаменимые инструменты.
Построить график в Excel по данным таблицы можно несколькими способами. Каждый из них обладает своими преимуществами и недостатками для конкретной ситуации. Рассмотрим все по порядку.
Рассмотрим поэтапно условное форматирование ячеек.
- Для выбора ячеек на которые будет распространятся условие форматирование выделим их и перейдем к меню “Условное форматирование” в разделе “Стили” на панеле задач Эксель.
- Выбираем пункт создать правило
- Выбираем правило “Форматировать все ячейки на основании их значений”. Стиль формата перекючаем на “Наборы значков”.Настраиваем правила отображения значков. Здесь начинается самое веселье. Мне не удалось сразу понять алгоритм работы имеющихся правил и только после длительных экспериментов картина прояснилась.
Итоговый результат настроек условного форматирования выглядит так:
Минимизая функции через равносильные преобразования
Алгоритм минимизии логической функции
- Замена импликации и эквиваленции.
- Упрощение функции через законы де Моргана.
- Раскрытие скобок, используя законы поглощения, исключенного третьего, противоречия.
- Минимизация через закон дистрибутивности.
Алгоритм Куайна построения сокращенной ДНФ
- Получить СДНФ функции.
- Провести все операции неполного склеивания.
- Провести все операции поглощения.
Построение логической схемы по таблице истинности
По заданной СДНФ (по таблице истинности) определяются существенные и фиктивные переменные, полином Жегалкина и принадлежность классам T0,T1, S, M, L. ► Также можно создать новую логическую схему (если не выбран пункт Строить новую схему при минимизации булевой функции). Если вычисления происходят по исходной схеме и она понадобится в дальнейшем, то ее можно предварительно сохранить (меню Действия/Сохранить).
Название переменных можно изменить. Для этого их необходимо выбрать (первая строка таблицы).
Количество переменныхВвести как вектор значений (в виде строки)OK
| a | b | c | f |
| 1 | |||
| 1 | |||
| 1 | 1 | ||
| 1 | |||
| 1 | 1 | ||
| 1 | 1 | ||
| 1 | 1 | 1 |
Для установки параметров решения, необходимо нажать Далее.
Далее
Пример. Найдите СДНФ(А) и СКНФ(А) с помощью равносильных преобразований и таблицы истинности, если A = xvyv(x→y)&x
| x | y | x | y | xvy | xvy | x→y | (x→y)&x | xvyv(x→y)&x |
| 1 | 1 | 1 | 1 | |||||
| 1 | 1 | 1 | 1 | |||||
| 1 | 1 | 1 | ||||||
| 1 | 1 | 1 | 1 | 1 | 1 |
xx v yxx v yxxxxiiiiiiiiiiiiiyxyxyxyxСовершенная дизъюнктивная нормальная форма формулы12nСовершенная конъюнктивная нормальная форма формулы12nyx
- Нефедов В.Н., Осипова В.А. Курс дискретной математики. М.,1992.
- Бауэр Ф.Л., Гооз Г. Информатика. Вводный курс: Часть 2, М.: Мир, 1990.
- Горбатов В.А. Основы дискретной математики. – М.: Высш. школа, 1986. – 312 с.
Как сделать зависимость значения ячейки от данных другой
единственное, что изменяется,Начнем с того, что они значительно облегчают если вам нужно которых продавцы заказывали стиль ссылок в в Excel.TabСнова кликните поНадежда еще жива. Пожалуйста, иллюзии, верните на
f4 (получится - данные, взятые из значение «100500». При
: Спасибо! как там ниже на рисунке: ввести $H3: $H15. это начало диапазона мы уже умеем, как написание, так разделить их на
товары. Из всего Excel, заданный по Каждый лист в Excel .Trace Precedents либо скажите, что грешную Землю!
$K$1). другой. Например: этом 100500 ставится появилось текстовое форматированиеПроверьте, в каких ячейках Однако использование имен и его высота то есть с и чтение формулы. подкатегории, то метод,
ассортимента они должны умолчанию. Для получения состоит из тысячЧтобы проверить наличие распространенных(Влияющие ячейки). она не напрасна,
Заранее всем благодарна!Теперь при копированиипри заполнении ячейки потому, что есть сам не понял, используются данные для диапазонов в формуле
(то есть количество создания раскрывающегося спискаПрисвоим имена двум диапазонам.
который я описываю были выбрать те подробной информации обратитесь прямоугольников, которые называют ошибок, которые закрадываютсяКак и ожидалось, для либо добейте уж!Igor67
всех последующих ячеек А1 цифрой 1, ячейки А3 и я уже подправил вычисления результата формулой делает ее проще элементов в списке).
в ячейке B12. Список всех категорий ниже, будет идеальным. продукты, которые они с уроку: Что ячейками. Ячейка – в формулы, сделайте расчета общей стоимостиСпасибо!: Посмотрите функцию ЯЧЕЙКА(«адрес»;Лист2!A1). вниз зависимая ячейка ячейка А2 автоматически А4, между которыми ячейки, но на в F2. и легко читаемой.Начало диапазона будет перемещено Поэтому выберите эту и рабочий список Смело используйте! собирались продать. такое стиль ссылок это пересечение строки следующее. используется список различныхЛузерВот результат работы меняться не будет. принимала значение ячейки связь по принципу будущее подскажите какВыберите: «Формулы»-«Зависимости формул»-«Влияющие ячейки»Вот и все: относительно ячейки H2 ячейку и нажмите категорий. Это будутА конечный результат выглядитКаждый продавец сначала определял в Excel. и столбца. СтолбцыВведите значение 0 в затрат.: А у мну функции Лист2!$A$1Стешка А3, а при «текст ячейки А3″ менять формат ячейки и вы увидитеСкачать пример зависимого выпадающего
Как в экселе сделать ячейки зависимыми.
на такое количество «Данные» / «Проверка диапазоны A3:A5 (список следующим образом: товарную группу, аЧтобы ввести данные или в Excel обозначают ячейкуЧтобы убрать стрелки, выполните он появляется, ноСтешка: Доброго всем времени заполнении ячейки А1 = «100500»., т.е. на формульный? Какая источники данных для
списка в Excel ячеек вниз (по данных», а в категорий в зеленойДля того чтобы этого затем конкретный товар отредактировать содержимое ячейки, буквами (A, B,С12 следующие действия:
не нажимается :(: Igor67! Я не суток! цифрой 2, ячейка «если А3=»мама», то
Зависимости формул в Excel
ошибок» из развернутого использовать? (не включая заголовок, СМЕЩ позволяет динамически Enter. есть подкатегорий. ОченьПервым был список всех
ячейки при помощи С и строки
Влияющие ячейки
С13 ниже:Влияющие ячейки данные в которую имена и их характеристика, соответствующая названию. добрый.Pelena
списка меню.Excel оснащен инструментами для так как речь
определять диапазоны
ВначалеТакое же действие совершите важно, чтобы данные категорий продуктов, второй клавиш со стрелками 5. При выделении
Формула пытается делитьВыделите ячейку
Убрать стрелки
попадают из «другого адреса)? Спасибо заранее.Хотя, чувствую, что: Изначально устанавливается ОБЩИЙ
На этом же листе прослеживания зависимости формул идет о диапазоне
мы определяем ячейку, для диапазона рабочего были отсортированы по – список всех
Убрать стрелки
на клавиатуре (клавиши ячейки ее адрес
- числа на 0.С12Зависимые ячейки листа».Заранее всем благодарна.Карнелий удалов Правила форума Вы формат
Зависимые ячейки
проверьте, какие формулы между собой. Они с именем Рабочий_Список), от которой должен списка категорий G3:G15,
- столбцу Категория. Это продуктов, находящихся в управления курсором).
-
отображается в полеДля отладки формулы методом.Показать формулыУ меня такаяIgor67: беда, коля. не читали.
webrelax
используют содержимое D2. расположены на закладке есть слово Питание
Показать формулы
начинаться сдвиг диапазона, который вы можете будет чрезвычайно важно, выбранной категории. ПоэтомуПри работе с Excel
Имя
Обратите внимание, оценки каждой еёНа вкладкеПроверка наличия ошибок же «Таблица», но: Может вот такоебд в помощьКароч, эта тема
: Спасибо! Подскажите еще. Перейдите на ячейку D2. «Формулы» в разделе (его первое появление). а в последующих вызвать “Рабочий_Список”
Этот когда позже будем я создал выпадающий часто возникает необходимость что при выборе части индивидуально, выполнитеFormulasВычисление формулы
Перейдите на ячейку D2. «Формулы» в разделе (его первое появление). а в последующих вызвать “Рабочий_Список”. Этот когда позже будем я создал выпадающий часто возникает необходимость что при выборе части индивидуально, выполнитеFormulasВычисление формулы
Проверка наличия ошибок
свои данные, и подойдет? Скачал сЗибин закрыта.
-
можно ли сделатьВыберите: «Зависимые ячейки». «Зависимости формул». Рассмотрим Мы используем этот
-
аргументах определяем его диапазон мы будем писать формулу. список, зависимый от выделить большую группу ячейки, заголовки строки следующие действия:(Формулы) в разделе
Зависимости формул в Excel
мне нужно поставить форума, там и: вариантыкак в экселе сделать вывод значения ячейки
Вычисление формулы
Повторно нажмите на этот детально все действия факт собственно для размеры.
- использовать в формуле.Можно было бы также выбора, сделанного в
-
ячеек или диапазон. и столбца, наВыберите ячейкуFormula Auditing позволяют отображать соотношения свои данные в ссылочка на постИспользование функции ВПР
-
ячейки зависимыми - ставка в зависимости же инструмент для этих инструментов.
определения начала диапазона.
В нашем примере диапазонЭто будет просто: использовать таблицы с предыдущем списке (здесь
Нажмите на первую ячейку
office-guru.ru>
Использование вложенных формул в Excel
Что касается использования встроенных в Excel формул (функций), то на первый взгляд использование их путём вложения друг в друга невозможно. Такую точку зрения часто обосновывают тем, что при вставке формулы в ячейку программа показывает всплывающее окно, предназначенное для ручного или автоматического (при помощи мышки) выбора диапазонов ячеек, отдельных ячеек или отдельных значений (как числовых, так и текстовых). На скриншоте ниже приведён пример для вставки формулы вычисления среднего значения («СРЗНАЧ»).
Действительно, при использовании Мастера для вставки и редактирования формул Вы можете лишь создавать простые выражения. В большинстве случаев этого достаточно, но при создании комплексных конструкций вида «формула в формуле» в Excel следует применять иной подход, а именно: создавайте сложные выражения вручную при помощи строки формул, расположенной в панели инструментов.
Пример создания формулы в формуле в Excel
Рассмотрим очень простой пример, в котором продемонстрируем применения вложенных формул. Изначально у нас есть два столбца с числами:
| А | B | |
|---|---|---|
| 1 | 10 | 60 |
| 2 | 20 | 70 |
| 3 | 30 | 80 |
| 4 | 40 | 90 |
| 5 | 50 | 100 |
Что нужно сделать: вычислим сумму средних значений обоих столбцов.
Если не использовать комплексные вычисления, то нам бы потребовалось целых три ячейки:
- В первой ячейке: вычислить среднее значение для колонки 1 (столбец А) при помощи функции СРЗНАЧ;
- Во второй колонке сделать то же самое, но для колонки 2 (столбец B);
- В третьей ячейке при помощи функции СУММ сложить два промежуточных результата;
Это типичный подход к решению задач такого рода. В принципе, две лишние ячейки вроде бы не жалко, но если вычислений на листе много, то получится не очень красиво. Поэтому решим вопрос без использования промежуточных ячеек через комплексную формулу с использованием вложения стандартных функций друг в друга.
Чтобы понять, как это сделать, нам необходимо рассмотреть синтаксис используемых «встроенных» формул Excel:
- Функция «СУММ»Применяется для сложения двух или более чисел или диапазонов чисел.Синтаксис: СУММ(X; Y; Z;)
- Функция «СРЗНАЧ»Применяется для вычисления среднего значения двух или более чисел или диапазонов чисел.Синтаксис: СРЗНАЧ(X; Y; Z;)
Здесь X, Y, Z — это или конкретные числа, указанные вручную, или адреса ячеек, или диапазон ячеек вида «A1:A10»
Обратите внимание, что при указании в качестве параметров диапазонов ячеек, аргумент у функций может быть всего один; при указании отдельных адресов ячеек или чисел — минимум два аргумента
В качестве аргументов встроенных формул разрешается использовать другие формулы, если они возвращают приемлемое для данной формулы значение (в данном случае это должно быть число).
А раз так, то мы можем составить комплексную формулу сразу для вычисления конечного результата. Для этого применим принцип Excel «формула в формуле». Вот что должно получиться для нашего примера с двумя колонками чисел:
СУММ(СРЗНАЧ(A1:A5); СРЗНАЧ(В1:В5))
Сумма будет равна 110. Вы можете проверить это, вычислив среднее значение отдельно для каждого столбца и сложив их.
Создание формул со скобками
Excel – мощный инструмент для работы с таблицами и выполнения сложнейших расчетов, потому в нем легко можно работать с большим количество данных, а формулы нужно составлять, руководствуясь законами математики.
Допустим, у нас есть данные по продажам за 1 и 2 квартала, при этом цена товара оставалась неизменной. Узнать нужно общую сумму реализованного товара за 1-2 кварталы.
Для начала нужно сложить количество проданного товара в 1 и 2 кварталах, и далее умножить полученную сумму на цену за 1 штуку.
Произвести расчет можно сразу по одной формуле. По правилам математики, действие сложения нужно брать в скобки, иначе первым выполнится умножение (что даст неверный результат). В Excel действуют те же самые правила, и используются классические знаки скобок (открывающих и закрывающих).
-
Ставим курсор на результирующую ячейку (E2), пишем знак “=”, далее открываем скобку, в ней складываем ячейки B2 и C2, далее скобку закрываем, ставим знак умножения и, наконец, координаты ячейки D2. Так формула выглядит в конечном виде: =(B2+C2)*D2
-
Формула готова. Теперь жмем клавишу “ENTER”, чтобы увидеть результат вычислений.
-
При желании и необходимости, формулу можно протянуть на остальные строки, как это было описано выше.
Примечание: Стоит отметить, что вовсе не обязательно все ячейки, участвующие в формуле, располагать по соседству или на одном листе Excel. Пусть даже они будут на разных листах — результат получится правильный, если формула составлена корректно.
Пример создания зависимого выпадающего списка в ячейке Excel
Пример использования зависимого выпадающего списка для создания удобной формы заполнения документов, с помощью которых продавцы заказывали товары. Из всего ассортимента они должны были выбрать те продукты, которые они собирались продать.
Каждый продавец сначала определял товарную группу, а затем конкретный товар из этой группы. Форма должна включать полное имя группы и определенный индекс товара. Поскольку набирать это вручную было бы слишком трудоемким (и раздражающим) занятием, я предложил очень быстрое и простое решение – 2 зависимых выпадающих списка.
Первым был список всех категорий продуктов, второй – список всех продуктов, находящихся в выбранной категории. Поэтому я создал выпадающий список, зависимый от выбора, сделанного в предыдущем списке (здесь вы найдете материал о том, как создать два зависимых раскрывающихся списка).
Тот же самый результат хочет получить пользователь шаблона домашнего бюджета где нужна категория и подкатегория расходов. Пример данных находится на рисунке ниже:
Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.
Оператор формул в Excel
Вместо знака “+” в формуле можно использовать знаки вычитания, умножения и деления, при этом в Эксель есть специальные знаки для этих математических действий, которые называются операторами формул:
- Плюс – это знак “+”
- Минус – это знак “-” (дефис).
- Умножение – это знак “*” (звездочка).
- Деление – это знак “/” (слэш или косая черта).
- Возведение в степень – это знак “^” (циркумфлекс, находится на клавише с цифрой 6, печатается вместе с зажатой клавишей Shift).
А так могут выглядеть формулы в ячейке:
- =А1-А2
- =А1/А2
- =А1*А2
Примечание: все знаки должны идти без пробелов.
Также, в формулах может быть задействовано сколько угодно ячеек из различных строк и столбцов (=А1+А2+А3+А4+В6… и т.д), а результирующей может быть любая свободная ячейка.
Решение 2: вносим изменения в параметры
Строка формул, также, может быть выключена в параметрах программы. Включить ее обратно можно методом, который описан выше, или воспользоваться планом действий ниже:
- Открываем меню “Файл”.
- В открывшемся окне в перечне слева щелкаем по разделу “Параметры”.
- В параметрах переключаемся в подраздел “Дополнительно”. В основной части окна справа листаем содержимое, пока не найдем блок инструментов “Отображение” (в ранних версиях программы группа может иметь название “Экран”). Находим опцию “Показывать строку формул”, ставим напротив нее галочку и подтверждаем изменение нажатием кнопки OK.
- Как и в рассмотренном ранее методе решения проблемы, строка вернется на свое место.
Данные в Excel
Листы Excel состоят из ячеек, которые сгруппированы по строкам и столбцам. Строкам присвоены номера, а столбцам — буквы A, B, C или цифры в зависимости от текущих настроек программы.
На листе больше миллиона строк и 16 000 столбцов, и вы можете вставлять формулы в любые ячейки на их пересечении.
Данные в любой ячейке могут относиться к одному из следующих типов: текст, число, формула.
Текстовый тип — последовательность любых символов, при вводе отображается в ячейке с левого края. В формулы и функции вводится в кавычках.
Числовой тип данных содержит только цифры, запятую* (знак десятичной дроби) или знак минус (для отрицательного числа). Посторонние символы и пробелы не допускаются. Над числами могут производиться любые математические действия.
Числа отображаются в ячейках с правого края, бывают:
- Обычные (0,33; 256,44; 1768 и т.д. до 15 цифр);
- Дата (01.01.1900 – 31.12.9999, от 1 до 2958465);
- Время (от 00:00:00 до 23:59:59, от 0 до 0,99).
* Запятая, знак разделителя для программы на русском языке, для ENG версии разделителем будет точка.