Добрый вечер! Есть Excel файл в котором на многих листах, собрана одинаковая информация за разные месяцы. Например на каждом из литов, есть столбец «Посещаемость», и он всегда находится в ячейке А. Есть столбец «Сумма», и он находится в ячейке С. Вопрос, как можно на одном листе, динамически выводить статистику по любому из столбцов, по всем месяцам? Например увидеть посещаемость за (Январь, Февраль, Март). или сумму за (Январь, Февраль, Март).
Теоретически выглядит просто. Мы имеем данные вида: =Январь!A2 =Февраль!A2 =Март!A2 =Январь!A3 =Февраль!A3 =Март!A3 =Январь!A4 =Февраль!A4 =Март!A4
Сразу хочется просто заменить «А», например на «С». Но в ручную менять каждый раз не вариант, так как страниц намного больше 12. Как это можно менять динамически, каким либо переключателем?
Буду благодарен за любые советы!
Добрый вечер! Есть Excel файл в котором на многих листах, собрана одинаковая информация за разные месяцы. Например на каждом из литов, есть столбец «Посещаемость», и он всегда находится в ячейке А. Есть столбец «Сумма», и он находится в ячейке С. Вопрос, как можно на одном листе, динамически выводить статистику по любому из столбцов, по всем месяцам? Например увидеть посещаемость за (Январь, Февраль, Март). или сумму за (Январь, Февраль, Март).
Теоретически выглядит просто. Мы имеем данные вида: =Январь!A2 =Февраль!A2 =Март!A2 =Январь!A3 =Февраль!A3 =Март!A3 =Январь!A4 =Февраль!A4 =Март!A4
Сразу хочется просто заменить «А», например на «С». Но в ручную менять каждый раз не вариант, так как страниц намного больше 12. Как это можно менять динамически, каким либо переключателем?
Буду благодарен за любые советы! Mint
Сообщение Добрый вечер! Есть Excel файл в котором на многих листах, собрана одинаковая информация за разные месяцы. Например на каждом из литов, есть столбец «Посещаемость», и он всегда находится в ячейке А. Есть столбец «Сумма», и он находится в ячейке С. Вопрос, как можно на одном листе, динамически выводить статистику по любому из столбцов, по всем месяцам? Например увидеть посещаемость за (Январь, Февраль, Март). или сумму за (Январь, Февраль, Март).
Теоретически выглядит просто. Мы имеем данные вида: =Январь!A2 =Февраль!A2 =Март!A2 =Январь!A3 =Февраль!A3 =Март!A3 =Январь!A4 =Февраль!A4 =Март!A4
Сразу хочется просто заменить «А», например на «С». Но в ручную менять каждый раз не вариант, так как страниц намного больше 12. Как это можно менять динамически, каким либо переключателем?
Последовательность yn = C называют постоянной или стационарной.
Арифметическая прогрессия — (an), задана таким соотношением: a1 = a, an+1= an + d.
Последовательность Фибоначчи — когда каждое следующее число равно сумме двух предыдущих чисел: an+1 = an + an-1.
Пример: 1, 1, 2, 3, 5, 8, 13, 21, 34, 55.
Так как алгебраическая числовая последовательность — это частный случай числовой функции, то ряд свойств функций рассматриваются и для последовательностей.
Свойства числовых последовательностей:
Возрастающие и убывающие последовательности называют монотонными последовательностями.
Пример числовой последовательности выглядит так:
В такой математической последовательности каждый номер соответствует одному числу. Это значит, что в последовательности не может быть двух первых чисел и т.д. Первое число (как и любое другое) — всегда одно.
N-ный член алгебраической последовательности — это число с порядковым номером n.
Всю последовательность можно обозначить любой буквой латинского алфавита, например, a. Каждый член этой последовательности — той же буквой с индексом, который равен номеру этого члена: a1, a2. a10. an.
N-ый член последовательности можно задать формулой. Например:
Определение арифметической прогрессии
Так как числовая последовательность — это частный случай функции, которая определена на множестве натуральных чисел, арифметическую прогрессию можно назвать частным случаем числовой последовательности.
Рассмотрим основные определения и как найти арифметическую прогрессию.
Арифметическая прогрессия — это числовая последовательность a1, a2. an. для которой для каждого натурального n выполняется равенство:
an+1= an + d, где d — это разность арифметической прогрессии.
Описать словами эту формулу можно так: каждый член арифметической прогрессии равен предыдущему, сложенному с одним и тем же числом d.
Разность между последующим и предыдущим членами, то есть разность арифметической прогрессии можно найти по формуле:
Если известны первый член a1 и n-ый член прогрессии, разность можно найти так:
Арифметическая прогрессия бывает трех видов:
Пример: последовательность чисел 11, 14, 17, 20, 23. — это возрастающая арифметическая прогрессия, так как ее разность d = 3 > 0.
Свойство арифметической прогрессии
Переведем с языка формул на русский: каждый член арифметической прогрессии, начиная со второго, равен среднему арифметическому двух соседних с ним членов. Что как раз объясняет название «арифметическая» прогрессия.
Рассмотрим пример арифметической прогрессии.
Дано: арифметическая прогрессия (an), где a1 = 0 и d = 2.
Найти: первые пять членов прогрессии и десятый член прогрессии.
Решение арифметической прогрессии:
По условиям задачи n = 10, подставляем в формулу:
Формулы арифметической прогрессии
В 9 классе проходят все формулы арифметической прогрессии. Давайте узнаем, какими способами ее можно задать:
Сумма первых n членов арифметической прогрессии (аn) обозначается Sn:
Формулы нахождения суммы n членов арифметической прогрессии:
Чтобы быстрее запомнить формулы можно использовать такую табличку с основными определениями:
Формула n-го члена арифметической прогрессии
Из определения арифметической прогрессии следует, что равенство истинно:
Значит,
Переведем с языка формул на русский: если мы знаем первый член и разность арифметической прогрессии, то можем найти любой ее член.
Арифметическую прогрессию можно назвать заданной, если известен ее первый член и разность.
Доказательство формулы n-го члена арифметической прогрессии
Формулу n-го члена арифметической прогрессии можно доказать при помощи метода математической индукции.
Пусть дано:
Нужно доказать:
Действительно,
Согласно принципу математической индукции формула верна для любого натурального числа.
Геометрическая прогрессия
Геометрическая прогрессия — это последовательность (bn), в которой каждый последующий член можно найти, если предыдущий член умножить на одно и то же число q.
Если последовательность (bn) является геометрической прогрессией, то для любого натурального значения n справедлива зависимость:
bn+1 = bn * q, где q — знаменатель геометрической прогрессии
Если в геометрической прогрессии (bn) известен первый член b1 и знаменатель q, то можно найти любой член прогрессии:
Общий член геометрической прогрессии bn можно вычислить при помощи формулы:
Пример 1. 2, 6, 18, 54,… — геометрическая прогрессия b = 2, q = 3.
Пример 3. 7, 7, 7, 7,… — геометрическая прогрессия b = 7, q = 1.
Формула ЕСЛИ в Excel — примеры с несколькими условиями
Что возвращает функция
Заданное вами значение при выполнении двух условий ИСТИНА или ЛОЖЬ.
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.
Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2
В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку
Синтаксис функции ЕСЛИ
Вот как выглядит синтаксис этой функции и её аргументы:
=ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)
Логическое выражение – (обязательное) условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);
Значение если «да» – (обязательное) действие, которое выполняется в случае положительного ответа;
Значение если «нет» – (обязательное) действие, которое выполняется в случае отрицательного ответа;
Давайте вместе подробнее рассмотрим эти аргументы.
Первый аргумент – это логический вопрос. И ответ этот может быть только «да» или «нет», «истина» или «ложь».
Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “ =”, “ ”.
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функции И и ИЛИ могут проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Простейший пример применения.
Предположим, вы работаете в компании, которая занимается продажей шоколада в нескольких регионах и работает с множеством покупателей.
Нам необходимо выделить продажи, которые произошли в нашем регионе, и те, которые были сделаны за рубежом. Для этого нужно добавить в таблицу ещё один признак для каждой продажи – страну, в которой она произошла. Мы хотим, чтобы этот признак создавался автоматически для каждой записи (то есть, строки).
В этом нам поможет функция ЕСЛИ. Добавим в таблицу данных столбец “Страна”. Регион “Запад” – это местные продажи («Местные»), а остальные регионы – это продажи за рубеж («Экспорт»).
Применение «ЕСЛИ» с несколькими условиями
Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.
Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта.
Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.
Пропишем формулу для этих условий в нужной нам ячейке.
=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))
Щелкаем Enter и получаем результат согласно заданным условиям.
Далее растягиваем формулу на все оставшиеся строки таблицы.
Операторы сравнения чисел и строк
Операторы сравнения чисел и строк представлены операторами, состоящими из одного или двух математических знаков равенства и неравенства:
Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом, VBA Excel сгенерирует ошибку Type Mismatch (несоответствие типов данных):
Сравнение строк начинается с их первых символов. Если они оказываются равны, сравниваются следующие символы. И так до тех пор, пока символы не окажутся разными или одна или обе строки не закончатся.
Значения буквенных символов увеличиваются в алфавитном порядке, причем сначала идут все заглавные (прописные) буквы, затем строчные. Если необходимо сравнить длины строк, используйте функцию Len.
Одновременное выполнение двух условий
Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».
Рассмотрим на примере нашей таблицы. Теперь скидка 30% будет проставлена только, если это женская обувь и предназначена для бега. При соблюдении этих условий одновременно значение ячейки будет равно 30%, в противном случае – 0.
Для этого используем следующую формулу:
=ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)
Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.
Аналогично примерам выше, растягиваем формулу на остальные строки.
Общее определение и задачи
«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.
Как правильно записать?
Устанавливаем курсор в ячейку G2 и вводим знак “=”. Для Excel это означает, что сейчас будет введена формула. Поэтому как только далее будет нажата буква “е”, мы получим предложение выбрать функцию, начинающуюся этой буквы. Выбираем “ЕСЛИ”.
Далее все наши действия также будут сопровождаться подсказками.
В качестве первого аргумента записываем: С2=”Запад”. Как и в других функциях Excel, адрес ячейки можно не вводить вручную, а просто кликнуть на ней мышкой. Затем ставим “,” и указываем второй аргумент.
Второй аргумент – это значение, которое примет ячейка G2, если записанное нами условие будет выполнено. Это будет слово “Местные”.
После этого снова через запятую указываем значение третьего аргумента. Это значение примет ячейка G2, если условие не будет выполнено: “Экспорт”. Не забываем закончить ввод формулы, закрыв скобку и затем нажав “Enter”.
Наша функция выглядит следующим образом:
Наша ячейка G2 приняла значение «Местные».
Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.
Дополнительная информация
Вложенные условия с математическими выражениями.
Вот еще одна типичная задача: цена за единицу товара изменяется в зависимости от его количества. Ваша цель состоит в том, чтобы написать формулу, которая вычисляет цену для любого количества товаров, введенного в определенную ячейку. Другими словами, ваша формула должна проверить несколько условий и выполнить различные вычисления в зависимости от того, в какой диапазон суммы входит указанное количество товара.
Эта задача также может быть выполнена с помощью нескольких вложенных функций ЕСЛИ. Логика та же, что и в приведенном выше примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (т.е. соответствующей ценой за единицу).
Предполагая, что количество записывается в B8, формула будет такая:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию в зависимости от вашей конкретной задачи.
Например, вместо «жесткого кодирования» цен в самой формуле можно ссылаться на ячейки, в которых они указаны (ячейки с B2 по B6). Это позволит редактировать исходные данные без необходимости обновления самой формулы:
Если вас не интересует, что будет, к примеру, если интересующее вас условие не выполняется, тогда можно не вводить второй аргумент. К примеру, мы предоставляем скидку 10% в случае, если заказано более 100 единиц товара. Не указываем никакого аргумента для случая, когда условие не выполняется.
Что будет в результате?
Насколько это красиво и удобно – судить вам. Думаю, лучше все же использовать оба аргумента.
И в случае, если второе условие не выполняется, но делать при этом ничего не нужно, вставьте в ячейку пустое значение.
Однако, такая конструкция может быть использована в том случае, если значение «Истина» или «Ложь» будут использованы другими функциями Excel в качестве логических значений.
Обратите также внимание, что полученные логические значения в ячейке всегда выравниваются по центру. Это видно и на скриншоте выше.
Более того, если вам действительно нужно только проверить какое-то условие и получить «Истина» или «Ложь» («Да» или «Нет»), то вы можете использовать следующую конструкцию –
Обратите внимание, что кавычки здесь использовать не нужно. Если вы заключите аргументы в кавычки, то в результате выполнения функции ЕСЛИ вы получите текстовые значения, а не логические.
Функция ЕПУСТО
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:
=ЕПУСТО(значение)
Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА() =ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение “Проходите”, если выражение в ячейке А1 имеет значение ИСТИНА:
В противном случае формула возвратит “Стоп”.
Составное условие
Составное условие состоит из простых, связанных логическими операциями И() и ИЛИ().
И() – логическая операция, требующая одновременного выполнения всех условий, связанных ею. ИЛИ() – логическая операция, требующая выполнения любого из перечисленных условий, связанных ею.
Простое условие
Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а.
Блок-схема “Простое условие”. Определение отрицательных и неотрицательных чисел
Условие а>=0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное. Ниже схемы приведена запись формулы в Excel. После условия через точку с запятой перечисляются варианты действий. В случае истинности условия, в ячейке отобразится текст “неотрицательное”, иначе – “отрицательное”. То есть запись, соответствующая ветви схемы «Да», а следом – «Нет».
Текстовые данные в формуле заключаются в кавычки, а формулы и числа записывают без них.
Если результатом должны быть данные, полученные в результате вычислений, то смотрим следующий пример. Выполним увеличение неотрицательного числа на 10, а отрицательное оставим без изменений.
Блок-схема “Простое условие”. Расчет данных
На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10 (выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1 (выделено красным цветом).
Это была краткая вводная часть для начинающих, которые только начали постигать азы Excel. А теперь давайте рассмотрим более серьезный пример с использованием условной функции.
Задание: Процентная ставка прогрессивного налога зависит от дохода. Если доход предприятия больше определенной суммы, то ставка налога выше. Используя функцию ЕСЛИ, рассчитайте сумму налога.
Решение:
Решение данной задачи видно на рисунке ниже. Но внесем все-таки ясность в эту иллюстрацию. Основные исходные данные для решения этой задачи находятся в столбцах А и В. В ячейке А5 указано пограничное значение дохода при котором изменяется ставка налогообложения. Соответствующие ставки указаны в ячейках В5 и В6. Доход фирм указан в диапазоне ячеек В9:В14. Формула расчета налога записывается в ячейку С9: =ЕСЛИ(B9>A$5;B9*B$6;B9*B$5). Эту формулу нужно скопировать в нижние ячейки (выделено желтым цветом).
В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.
Пример функции с несколькими условиями
В функцию «ЕСЛИ» можно также вводить несколько условий. В этой ситуации применяется вложение одного оператора «ЕСЛИ» в другой. При выполнении условия в ячейке отображается заданный результат, если же условие не выполнено, то выводимый результат зависит уже от второго оператора.
Пример использования «ЕСЛИ»
Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».
Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)
При использовании функции IF (ЕСЛИ) в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:
Если сумма баллов больше или равна “35”, то формула возвращает “Сдал”, иначе возвращается “Не сдал”.
Заключение
Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ, которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы.
Блок-схема “Простое условие”. Определение отрицательных и неотрицательных чисел
Блок-схема “Простое условие”. Расчет данных
