Используем Условное форматирование для подачи сигнала пользователю при вводе ошибочного значения. Под «ошибочным значением» здесь будем понимать значение, не принадлежащее заданному диапазону.
Часто на диаграмме необходимо отобразить не все значения из исходной таблицы, а только те, которые удовлетворяют определенным критериям. Для построения такой диаграммы можно использовать формулы. В этом случае необходимо предварительно …
Часто на диаграмме необходимо отобразить не все данные из исходной таблицы, а лишь только часть, например, значения из 10 последних строк. Причем диаграмма должна динамически изменяться в зависимости от того, …
В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби …
Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить …
При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Например, при заполнении ведомости ввод фамилий сотрудников с клавиатуры можно заменить выбором из определенного заранее списка (табеля), а …
Обычно формулы непосредственно вводятся в ячейки, но можно, предварительно присвоив формуле имя, использовать в ячейке ее имя. Какие преимущества дает именованная формула – читайте в этой статье.
При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов …
При заполнении ячеек данными, бывает необходимо ограничить возможность ввода определенным списком значений – это можно сделать с помощью Выпадающего списка . Если одновременно необходимо обеспечить ввод только неповторяющихся значений, то …
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению фильтра ). В отличие от фильтра отобранные строки будут помещены в отдельную таблицу.
Произведем сложение значений находящихся в строках, поля которых удовлетворяют сразу двум критериям (Условие И). Рассмотрим Текстовые критерии, Числовые и критерии в формате Дат. Разберем функцию СУММЕСЛИМН( ) , английская версия …
Предположим, что счет за продукцию нужно выставлять только в рабочие дни, несмотря на дату доставки. Напишем формулу, которая определяет: если дата доставки попадает на выходной или праздничный день, то дата …
Округлить с точностью до 0,01; 0,1; 1; 10; 100 не представляет труда – для этого существует функция ОКРУГЛ() . А если нужно округлить, например, до ближайшего числа, кратного 50?
При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1") . …
Предположим, что счет за продукцию нужно выставлять только в рабочие дни, несмотря на дату доставки. Напишем формулу, которая определяет: если дата доставки попадает на выходной, то дата счета – следующий …
Для подсчета ЧИСЛОвых значений, Дат и Текстовых значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ( ) , английская версия COUNTIF(). Подсчитаем значения в диапазоне в случае одного критерия, …
Функция ГОД() , английский вариант YEAR(), в озвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999.
Найдем среднее всех ячеек, значения которых соответствуют определенному критерию. Для этой цели существует простая и эффективная функция СРЗНАЧЕСЛИ( ) , английский вариант AVERAGEIF(), которая впервые появилась в EXCEL 2007.
Функция ЕОШ() , английский вариант ISERR(), проверяет на равенство значениям: #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! и возвращает в зависимости от этого ИСТИНА или ЛОЖЬ.
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие …
Иногда пользователю необходимо ограничить возможность ввода определенным шаблоном. Например, при вводе артикулов известно, что правильный артикул имеет длину 6 символов, начинается с латинской буквы, далее идут 4 цифры, затем 1 …
Подсчитаем количество ячеек содержащих числа с помощью функции СЧЁТ ( ) , английская версия COUNT() . Предполагаем, что диапазон содержит числа и числовые значения в текстовом формате.
Разрешим ввод в столбец только неповторяющихся значений с использованием специального Выпадающего списка . Для этого необходимо динамически модифицировать Выпадающий список, последовательно исключая из него только что введенные значения.
Имеется таблица, состоящая их нескольких столбцов. В одном из столбцов имеются повторяющиеся текстовые значения. Создадим список, состоящий только из уникальных текстовых значений. Уникальные значения будем выбирать не из всех повторяющиеся …
Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.
Произведем подсчет значений, удовлетворяющих сразу трем критериям, которые образуют 2 Условия И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты …
Настроим Условное форматирование для выделения только неповторяющихся значений и с учетом РЕгиСТра. Т.е. значения «Петров» и «петров» будут считаться неповторяющимися (разными значениями).
Задача функции ЕЧИСЛО() , английский вариант ISNUMBER(), - проверять являются ли значения числами или нет. Формула = ЕЧИСЛО(5) вернет ИСТИНА, а =ЕЧИСЛО("Привет!") вернет ЛОЖЬ.
Если в диапазоне суммирования встречается значение ошибки #Н/Д (значение недоступно), то функция СУММ() также вернет ошибку. Используем функцию СУММЕСЛИ() для обработки таких ситуаций.
EXCEL в формате Время не отображает миллисекунды, но иногда это требуется. С помощью пользовательского форматирования отобразить миллисекунды не составляет труда.
Задача функции ЕПУСТО() , английский вариант ISBLANK(), - проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется значение 555, то формула = ЕПУСТО(А1) …
Функция ГИПЕРССЫЛКА() , английский вариант HYPERLINK(), создает ярлык или гиперссылку, которая позволяет открыть страницу в сети интернет, файл на диске (документ MS EXCEL, MS WORD или программу, например, Notepad.exe) или …
При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам …
Произведем сложение значений, которые удовлетворяют хотя бы одному из 3-х критериев (Условие ИЛИ). Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце …
Вместо того, чтобы вводить повторяющиеся данные, в таблице часто оставляют незаполненные ячейки. Наличие пустых ячеек в таблице затрудняет применение фильтров , сортировку и построение Сводных таблиц . Заполним пустые ячейки …
Функция ЧСТРОК() , в английском варианте ROWS(), подсчитывает число строк в диапазоне ячеек или массиве констант. Например, формула ЧСТРОК(A1:F3) возвращает значение 3, т.к. в заданном диапазоне 3 строки: 1, 2, …
Функция ПОИСКПОЗ( ) , английский вариант MATCH(), возвращает позицию значения в диапазоне ячеек. Например, если в ячейке А10 содержится значение "яблоки", то формула =ПОИСКПОЗ ("яблоки";A9:A20;0) вернет 2, т.е. искомое значение …
Найдем дату конца квартала, к которому относится заданная дата. Например, если задана дата 05.11.12, то дата конца квартала 31.12.12. Также найдем дату начала соответствующего квартала.
Элемент Полоса прокрутки позволяет изменять значения в определенном диапазоне с шагом (1, 2, 3, ...), если нажимать на кнопки со стрелочками, и с увеличенным шагом, если нажимать на саму полосу …
Двойные кавычки " часто встречаются в названиях фирм, наименованиях товаров, артикулах. Так как двойные кавычки - это специальный символ, то можно ожидать, что избавиться от них не так просто.
Рассчитаем в MS EXCEL сумму регулярного аннуитетного платежа при погашении ссуды. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу ежемесячных платежей …
Компания должна выполнить заказ на поставку нескольких видов изделий, изготовив их из имеющихся на складе материалов. К сожалению, материалов недостаточно для выполнения заказа полностью. Необходимо определить какие изделия стоит произвести …
Компания проводит реорганизацию с целью повысить профессионализм сотрудников. Реорганизация проводится путем приема новых сотрудников, увольнения и переобучения. Необходимо минимизировать затраты компании на реорганизацию. Расчет будем проводить с помощью надстройки Поиск …
Подсчитаем количество выходных дней, содержащихся в диапазоне дат. Даты введены в ячейки листа. Выходными днями считаются суббота и воскресенье (праздники не учитываются).
Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к качеству продукции. Расчет будем проводить с помощью …
Пусть дано 2 списка значений. Определим, какие элементы первого списка НЕ содержатся во втором и какие элементы второго списка НЕ содержатся во первом .
Функция ПРОСМОТР( ) , английский вариант LOOKUP(), похожа на функцию ВПР() : ПРОСМОТР() просматривает левый столбец таблицы и, если находит искомое значение, возвращает значение из соответствующей строки самого правого столбца …
Подсчитаем в MS EXCEL количество перестановок с повторениями из n элементов. С помощью формул выведем на лист все варианты таких перестановок (английский перевод термина: permutations of multisets).
Подсчитаем в MS EXCEL количество Сочетаний с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Сочетаний (английский перевод термина: combinations with …
Обзорная статья, в которой приведены основные функции MS EXCEL для вычисления количества перестановок, сочетаний и размещений. Рассмотрены варианты комбинаций без повторений и с повторениями (выборка с возвращением).
Трансформация (преобразование) геометрической фигуры означает ее изменение по определенным правилам. Например, вращение, смещение или изменение масштаба некого прямоугольника на плоскости. Правила, по которым происходит изменение, будем записывать в матричном виде. …
Построим в MS EXCEL график функции, заданный системой уравнений. Эта задача часто встречается в лабораторных работах и почему-то является "камнем преткновения" для многих учащихся.
Создадим формулу для подачи сигнала пользователю при вводе ошибочного значения. Под «ошибочным значением» здесь будем понимать значение, не принадлежащее заданному диапазону.
Сравним два вида платежей по кредиту: аннуитетные (заемщик регулярно платит за кредит равными суммами) и дифференцированные (сумма основного долга делится на равные части пропорционально сроку кредитования, проценты начисляются на остаток …
Пусть имеется случайная переменная Y, значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от 2-х факторов, значения которых мы можем контролировать, т.е. задавать с требуемой точностью. Покажем …
Определим Приведенную (текущую) стоимость будущих доходов (или расходов) в случае аннуитета. Для этого будем использовать функцию ПС() . Также выведем альтернативную формулу для расчета Текущей стоимости.
Функция ЕНД() , английский вариант ISNA(), п роверяет на равенство значению #Н/Д (значение недоступно) и возвращает в зависимости от этого ИСТИНА или ЛОЖЬ.
При написании сложных формул, таких как =ЕСЛИ(СРЗНАЧ(A2:A10)>200;СУММ(B2:B10);0) , часто необходимо получить промежуточный результат вычисления формулы. Для этого есть специальный инструмент Вычислить формулу.
Функция ЧЁТН() , в английском варианте EVEN(), округляет до ближайшего четного целого, которое больше исходного значения (округляет вверх до ближайшего четного).
Определим день недели для заданной даты в ячейке. Существует несколько решений в зависимости от того результата, который мы хотим получить: номер дня недели (1 - Пн, 2- Вт, ...), текстовую …
Напишем формулу для проверки достижения 20-летнего возраста на сегодняшнюю дату. Формула должна возвращать значение ИСТИНА, если кому-то уже есть полных 20 лет, в противном случае формула должна возвращать значение ЛОЖЬ.
Функция ПОДСТАВИТЬ( ) , английский вариант SUBSTITUTE(), заменяет определенный текст в текстовой строке на новое значение. Формула =ПОДСТАВИТЬ(A2; "январь";"февраль") исходную строку "Продажи (январь)" превратит в строку "Продажи (февраль)".
Часто на диаграмме необходимо отобразить не все значения из исходной таблицы, а только те, которые удовлетворяют определенным критериям. Если в исходной таблице скрыть строки, содержащие ненужные данные, то диаграмма обновиться …
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В …
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине …
Функция ЧИСТРАБДНИ( ) , английская версия NETWORKDAYS() , возвращает количество рабочих дней между двумя датами, т.е. при подсчете праздники и выходные не учитываются.
Произведем подсчет строк таблицы, значения которых удовлетворяют сразу двум критериям, которые образуют Условие ИЛИ. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в …
Если с помощью Выпадающего (раскрывающегося) списка на основе Проверки данных можно ввести новое значение в ячейку, то с помощью Выпадающего списка на основе элемента управления формы Поле со списком можно …
Имея список с повторяющимися текстовыми значениями, создадим список, состоящий только из уникальных значений. Отбор уникальных значений будем производить с учетом РЕгиСТра.
Функция И( ) , английский вариант AND(), проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.
Найдем, формулу возвращающую ссылку на диапазон, содержащий 5 последних значений. Если столбец со значениями постоянно заполняется, то эта задача перестает быть тривиальной.
Копировать ЧИСЛА из WORD в EXCEL приходится не так уж и редко. Чтобы избежать нежелательного переноса разрядов числа на другую строку, в WORD принято разделять разряды неразрывным пробелом (1 234 …
Функция ИНДЕКС( ) , английский вариант INDEX(), возвращает значение из диапазона ячеек по номеру строки и столбца. Например, формула =ИНДЕКС(A9:A12;2) вернет значение из ячейки А10 , т.е. из ячейки расположенной …
Часто EXCEL используют не только для расчетов и анализа данных, но и для подготовки всевозможных отчетов. Иногда требуется, чтобы данные относящиеся к разным разделам таблицы попадали на разные страницы отчета. …
При составлении формул для отображения в ячейке фразы содержащей текст и время, например, «Сейчас 10:35», могут возникнуть сложности с правильным отображением времени. Решим задачу путем предварительного преобразования времени в текстовое …
Используя в Формате ячеек символ @, можно отобразить в ячейке, предназначенной для ввода чисел, текстовую строку. На вычисления эта строка не повлияет, т.к. мы будем применять пользовательский формат. Этот подход …
Сортировку списка можно осуществить через меню Данные/ группа Сортировка и фильтр/ Сортировка . В случае, если в исходный список постоянно вводятся новые значения, то для поддержания списка в сортированном состоянии, …
Функция ЯЧЕЙКА( ) , английская версия CELL() , возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях …
Функция ВПР() , английский вариант VLOOKUP(), ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого столбца таблицы.
Поле со списком представляет собой сочетание текстового поля и раскрывающегося списка. Поле со списком компактнее обычного списка, однако для того чтобы отобразить список элементов, пользователь должен щелкнуть стрелку. Поле со …
Создадим модель для решения Транспортной задачи (Transportation Problem, Shipping Routes). Решение Транспортной задачи позволяет определить самые недорогие маршруты для перевозки товаров от производителей на склад. Расчет будем проводить с помощью …
Создадим модель для определения оптимального размера партии товара, при котором общие переменные затраты минимальны. Затраты связаны с процедурой заказа и хранением партии на складе, и зависят от размера партии. Также …
Требуется разрезать провод на куски определенной длины, так чтобы количество отходов было минимально. Задачу решим методом перебора всех возможных комбинаций разрезки.
Построим модель для принятия решения: компания планирует открыть в нескольких регионах свои представительства, куда будут поступать платежи от ее клиентов. Требуется определить оптимальный вариант открытия представительств, при котором суммарные расходы …
Необходимо определить маршруты, при которых затраты на функционирование сети - минимальны. Построим линейную модель и с помощью надстройки Поиск решения решим задачу.
Построим график функции y=a*x^2+b*x+с (квадратное уравнение). Также рассчитаем дискриминант, найдем корни уравнения, координаты точки экстремума (максимума или минимума). Сделаем форму для сдвига и отражения графика с помощью элементов управления формы.
Если в ячейке числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. Преобразуем числа, сохраненные как текст, в числовой формат.
При вводе большого количества информации в ячейки таблицы легко допустить ошибку. В EXCEL существует инструмент для проверки введенных данных сразу после нажатия клавиши ENTER – Проверка данных.
Сравним 2 столбца значений. Сравнение будем производить построчно: если значение во втором столбце больше, чем в первом, то оно будет выделено красным, если меньше - то зеленым. Выделять ячейки будем …
Вычислим скалярное произведение векторов и проверим вектора на ортогональность. Подберем координаты вектора, ортогонального заданному, а также отобразим вектора в прямоугольной системе координат.
Решим Систему Линейных Алгебраических Уравнений (СЛАУ) методом обратной матрицы в MS EXCEL. В этой статье нет теории, объяснено только как выполнить расчеты, используя MS EXCEL.
Вывести значение из заданной строки таблицы не представляет труда - используйте функцию ИНДЕКС() . Но, если в таблице действет Автофильтр , то задача усложняется.
Произведем сложение значений, которые удовлетворяют хотя бы одному из 2-х критериев (Условие ИЛИ). Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце …
Подсчитаем количество ячеек содержащих хоть какие-нибудь значения с помощью функции СЧЁТЗ( ) , английская версия COUNTA() . Предполагаем, что диапазон содержит числа, значения в текстовом формате, значения ошибки, пустые ячейки, …
Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты (Allocation Problem). Расчет будем проводить с помощью надстройки Поиск решения.
Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим …
Если значение в ячейке удовлетворяет определенному пользователем условию, то с помощью Условного форматирования можно выделить эту ячейку (например, изменить ее фон). В этой статье пойдем дальше - будем выделять всю …
В этой статье приводится перечень контрольных заданий по теме Консолидация. За основу взяты реальные Контрольные задания, которые предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по теме MS …
Создадим связанный список, аналогичный списку рассмотренному в статье Связанный список , но усложним задачу: в элементах выпадающего списка , от которого зависит связанный, будут содержаться двойные кавычки ".
При удалении строки Элементы управления формы (Флажок, Полоса прокрутки, Счетчик) остаются на листе. Если нужно удалять их вместе со строкой, то используйте группировку Элементов управления и/или Фигур.
Выделяем ячейки, содержащие искомый текст с учетом РЕгиСТра. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в …
Функция ИЛИ( ) , английский вариант OR(), проверяет на истинность условия и возвращает ИСТИНА если хотя бы одно условие истинно или ЛОЖЬ если все условия ложны.
Функция ЕСЛИ() , английский вариант IF(), используется при проверке условий. Например, =ЕСЛИ(A1>100;"Бюджет превышен";"ОК!") . В зависимости от значения в ячейке А1 результат формулы будет либо "Бюджет превышен" либо "ОК!".
Рассмотрим Сложный процент (Compound Interest) – начисление процентов как на основную сумму долга, так и на начисленные ранее проценты, в случае переменной ставки.
Рассмотрим способы расчета амортизации с использованием функций MS EXCEL. В этой статье мы будем отталкиваться не от самих функций АПЛ (SLN), АСЧ (SYD), ФУО (DB), ДДОБ (DDB), ПУО (VDB), АМОРУВ …
Пусть имеется функция двух переменных Z=f(X;Y). Изолинии (contour line) - это линии, в которой величина Z=const, т.е. изолинии соединяют точки, в которых функция сохраняет одинаковое значение. Часто изолинии используют для …
Для подсчета значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ() . Если критерий единственный, то ее функциональности вполне достаточно для подсчета и текстовых и числовых значений. А возможность …
Решим задачу о сравнении средних значений нескольких выборок с использованием дисперсионного анализа в случае двух факторов без повторений (Two Factor ANOVA without Replication). Подход используемый для решения данной задачи имеет …
В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1*5). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы …
Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить …
Если фамилия, имя и отчестсво написаны слитно, например, «ПетровИванИванович», то можно создать формулу их разделения, чтобы получить «Петров Иван Иванович» .
В статье Отбор уникальных значений (убираем повторы из списка) в MS EXCEL было показано как из списка с повторами отобрать только уникальные значения. В этой статье покажем как решить обратную …
Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к качеству продукции. Расчет будем проводить с помощью …
Ячейка, содержащая значение Пустой текст (""), обладает замечательным свойством: ячейка выглядит пустой. К сожалению, значение Пустой текст несколько усложняет подсчет значений.
Задача функции СТОЛБЕЦ( ) , английский вариант COLUMN(), - возвращать номер столбца. Формула = СТОЛБЕЦ(B1) вернет 2, т.к. столбец B - второй столбец на листе.
Функция НД( ) , английский вариант NA(), возвращает значение ошибки #Н/Д. Значение ошибки #Н/Д означает, что значение недоступно. Рассмотрим случаи, когда эта функция может пригодиться.
Пусть имеется таблица, состоящая из двух столбцов: наименование организации и ее тип (юридическое лицо, индивидуальный предприниматель, физическое лицо). Необходимо разнести организации по разным столбцам в зависимости от типа: Юрлица, ИП …
Тенденции в рядах значений (например, колебания цен, объемов продаж) можно отслеживать в EXCEL с помощью диаграмм или условного форматирования . В этой статье рассмотрим спарклайны (sparklines), которые появились в MS …
Рассчитаем в MS EXCEL остаток основной суммы долга, который требуется погасить после заданного количества периодов. Выплата кредита производится равными ежемесячными платежами (аннуитетная схема). Процентная ставка и величина платежа - известны, …
Построим автоматическую сетевую диаграмму проекта. Сетевую диаграмму изобразим на диаграмме MS EXCEL типа Точечная. На этой диаграмме выведем работы проекта в виде точек, стрелками изобразим связи между работами. Также изобразим …
Если вам необходимо постоянно добавлять значения в столбец, то для правильной работы Ваших формул, Вам наверняка понадобятся динамические диапазоны, которые автоматически увеличиваются или уменьшаются в зависимости от количества ваших данных.
Вычислим в MS EXCEL дисперсию и стандартное отклонение выборки. Также в статье даны ссылки на формулы вычисления дисперсии случайной величины, если известно ее распределение.
Используем Условное форматирование для выделения строк таблицы, в которых числа принадлежат к определенному диапазону. Например, если число в определенном столбце таблицы меньше 0, то вся строка будет выделена красным.
Рассчитаем Приведенную (к текущему моменту) стоимость инвестиции при различных способах начисления процента: по формуле простых процентов, сложных процентов, аннуитете и в случае платежей произвольной величины.
Найдем среднее всех ячеек, значения которых соответствуют определенному условию. Для этой цели в MS EXCEL существует простая и эффективная функция СРЗНАЧЕСЛИ() , которая впервые появилась в EXCEL 2007. Рассмотрим случай, …
Функция СРЗНАЧ( ) , английский вариант AVERAGE(), возвращает среднее арифметическое своих аргументов. Также рассмотрена функция СРЗНАЧА( ) , английский вариант AVERAGEA()
Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.
Найдем в таблице наибольшую дату, которая меньше или равна заданной (ближайшая снизу). Причем из всех дат в таблице будем учитывать только те, которые относятся к определенному товару (условие). Список может …
Для анализа больших и сложных таблиц обычно используют Сводные таблицы . С помощью формул также можно осуществить группировку и анализ имеющихся данных. Создадим несложные отчеты с помощью формул.
Сортировку списка можно осуществить через меню Данные/ группа Сортировка и фильтр/ Сортировка . В случае, если в исходный список постоянно вводятся новые значения, то для поддержания списка в сортированном состоянии, …
Формулы массива могут возвращать как отдельное значение, так и несколько значений. В первом случае для отображения результата потребуется одна ячейка, во втором – диапазон. В этой статье рассмотрим формулы массива, …
Числовой пользовательский формат – это формат отображения числа задаваемый пользователем. Например, число 5647,22 можно отобразить как 005647 или, вообще в произвольном формате, например, +(5647)руб.22коп. Пользовательские форматы также можно использовать в …
Пользовательский формат – это формат отображения значения задаваемый пользователем. Например, дату 13/01/2010 можно отобразить как: 13.01.2010 или 2010_01_13 или 13-Январь-10 .
Часто необходимо «разнести» текстовою строку из одной ячейки по нескольким. Это может быть или полное имя: «Иванов Иван Иванович», либо адрес «г.Москва, Северный бульвар, д.133», либо паспортные данные. Используйте для …
Из исходной таблицы с повторяющимися значениями отберем только те значения, которые имеют повторы. Теперь при добавлении новых значений в исходный список, новый список будет автоматически содержать только те значения, которые …
Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе …
Создадим модель для определения Оптимальной структуры выпускаемой продукции (Product Mix), при котором выручка максимальна. Расчет будем проводить с помощью надстройки Поиск решения. За основу возьмем пример из файла solvsamp.xls, поставляемый …
Для вычисления медианы в MS EXCEL существует специальная функция МЕДИАНА() . В этой статье дадим определение медианы и научимся вычислять ее для выборки и для заданного закона распределения случайной величины.
Рассмотрим Равномерное дискретное распределение, построим график функции распределения, вычислим среднее значение и дисперсию. Сгенерируем случайные значения (выборку) с помощью функции MS EXCEL СЛУЧМЕЖДУ() . На основании выборки оценим среднее и …
Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к параметрам заказа (количество каждого вида продукции и …
Создадим модель для нахождения варианта разрезки набора лент на части определенной длины, таким образом, чтобы суммарные отходы после разрезки были минимальными. Расчет будем проводить с помощью надстройки Поиск решения.
Найдем текстовое значение, которое при сортировке диапазона по возрастанию будет выведено первым, т.е. первое по алфавиту. Также найдем последнее значение по алфавиту.
Рассмотрим равномерное непрерывное распределение. Вычислим математическое ожидание и дисперсию. Сгенерируем случайные значения с помощью функции MS EXCEL СЛЧИС() и надстройки Пакет Анализа, произведем оценку среднего значения и стандартного отклонения.
Функция ТРАНСП() , в анлийском варианте TRANSPOSE(), преобразует вертикальный диапазон ячеек в горизонтальный и наоборот. Научимся транспонировать (поворачивать) столбцы, строки и диапазоны значений.
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в …
Создадим модель для решения прикладной задачи: разместить файлы на DVD оптимальным образом, чтобы осталось как можно меньше пустого места. Эта задача является одной из разновидностей задачи о рюкзаке. Расчет будем …
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Критерии заданы с использованием подстановочных знаков . Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет …
Функция ЕСЛИОШИБКА() , английский вариант IFERROR(), п роверяет выражение на равенство значениям #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! Если проверяемое выражение или значение в ячейке содержит ошибку, то …
В реальной жизни часто приходится сравнивать списки, созданные разными людьми, в разное время, содержащие опечатки, лишние пробелы, данные в неправильном формате и пр. В этой статье рассмотрим не только само …
Рассмотрим Гамма распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ГАММА.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров …
Для поиска значения на пересечении строки и столбца требуется наличие таблицы специального вида: в строке заголовков и самом левом столбце должны быть неповторяющиеся значения.
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие …
Выделим диапазон, содержащий 5 последних заполненных ячеек в списке. Если столбец со значениями постоянно заполняется, то эта задача перестает быть тривиальной.
Рассмотрим поиск чисел в списке с повторами. Задав в качестве критерия для поиска нужное значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а …
При добавлении в таблицу новых строк приходится вручную восстанавливать нумерацию строк. Использование таблиц в формате Excel 2007 позволяет автоматизировать этот процесс.
Произведем подсчет значений, удовлетворяющих сразу трем критериям, которые образуют 2 Условия И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты …
Создадим обычную гистограмму с накоплением и гистограмму с накоплением , в которой бы наглядно отображалось каждое последующее изменение начальной величины.
Выполним ABC-анализ для определения ключевых клиентов и ранжирования номенклатуры товаров, используя надстройку MS EXCEL Fincontrollex® ABC Analysis Tool.
Для устранения двусмысленности толкования терминов: уникальное значение, неповторяющееся значение, дубликат, повтор и пр., в этой статье приведена соответствующая классификация.
Если исходный список, содержит и текст и числа, то с помощью формул массива можно в один список отобрать все текстовые значения, а в другой – числовые.
Подсчитаем промежуточные итоги в таблице MS EXCEL. Например, в таблице содержащей сведения о продажах нескольких различных категорий товаров подсчитаем стоимость каждой категории .
Выполним детерминированный факторный анализ на примере модели, описывающей связь финансовых показателей предприятия. Рассмотрим наиболее общий способ цепных подстановок. Для проведения факторного анализа используем надстройку MS EXCEL Variance Analysis Tool от …
Рассчитаем в MS EXCEL сумму регулярного платежа в случае накопления определенной суммы. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу регулярных …
Бывает, что при экспорте значений в EXCEL , даты записываются в незнакомом для EXCEL формате, например 20081223 (т.е. 2008г, 23 декабря). Для дальнейшей работы с такими датами выполним преобразование в …
При составлении формул для отображения в ячейке фразы содержащей текст и дату, например, «Сегодня 02.10.10», можно получить вот такой результат: «Сегодня 40453», т.е. дата будет отражена в числовом виде. Решим …
Рассчитаем в MS EXCEL сколько времени потребуется для погашения кредита в случае равных ежемесячных платежей (по аннуитетной схеме). Процентная ставка и величина платежа - известны, начисление процентов за пользование кредитом …
Пусть известна сумма и срок кредита, а также величина регулярного аннуитетного платежа. Рассчитаем в MS EXCEL под какую процентную ставку нужно взять этот кредит, чтобы полностью его погасить за заданный …
Определим Будущую стоимость инвестиции в случае аннуитета. Под инвестицией будем понимать как регулярные взносы, так и начальный взнос. Для этого будем использовать функцию БС() . Также выведем альтернативную формулу для …
В этой статье приводится перечень контрольных заданий по теме Сводные таблицы в MS EXCEL. За основу взяты реальные Контрольные задания, которые предлагается решить в ВУЗах, техникумах, школах и других обучающих …
Рассчитаем Будущую стоимость инвестиции для различных способов начисления процента: по формуле простых процентов, сложных процентов и формуле аннуитета.
Найдем максимальное количество подряд идущих значений в столбце. Например, максимально число подряд идущих положительных значений или нечетных значений или значений равных определенному числу.
Рассмотрим распределение Фишера (F-распределение). С помощью функции MS EXCEL F .РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Создадим удобную форму для составления автоматического отчета - бланка с информацией о водителе. На одном листе будем вводить подробную информацию о водителях в виде списка или перечня, на другом будем …
Подсчитаем количество подряд идущих значений в столбце. Рассмотрим неповторяющиеся серии значений (111222234444...) и повторяющиеся (110111000010 и 110221010112).
Функция НЕЧЁТ( ) , английский вариант ISODD(), округляет до ближайшего нечетного целого, которое больше исходного значения (округляет вверх до ближайшего нечетного).
Найдем с помощью функции МИН( ) , английский вариант MIN(), минимальное значение в списке аргументов. Предполагаем, что диапазон может содержать числа, числовые значения в текстовом формате, значения ошибки, пустые ячейки.
Найдем с помощью функции МАКС( ) , английский вариант MAX(), максимальное значение в списке аргументов. Предполагаем, что диапазон может содержать числа, числовые значения в текстовом формате, значения ошибки, пустые ячейки.
Функция НАИБОЛЬШИЙ( ) , английский вариант LARGE(), возвращает k-ое по величине значение из массива данных. Например, формула =НАИБОЛЬШИЙ(A2:B6;1) вернет максимальное значение (первое наибольшее) из диапазона A2:B6 .
Функция РАНГ( ) , английский вариант RANK(), возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 …
Функция СЧИТАТЬПУСТОТЫ( ) , английская версия COUNTBLANK() , подсчитывает количество пустых ячеек в заданном диапазоне. Также она подсчитывает ячейки с формулами, результатом вычисления которых является значение Пустой текст "" (например, …
Функция ПРОПНАЧ( ) , английский вариант PROPER(), делает первую букву в тексте ПРОПИСНОЙ (ЗАГЛАВНОЙ), например =ПРОПНАЧ("ааа") вернет "Ааа". =ПРОПНАЧ("ааа аа") вернет "Ааа Аа". В статье также показано как из "ааа …
В этой статье приводится контрольное задание по теме Одномерный массив MS EXCEL. За основу взято реальное Контрольное задание, которое предлагается решить по информатике студентам обучающимся на инженера-строителя. Желающие могут самостоятельно …
В этой статье приводится примеры расчета параметров кредита: процентных ставок, сумм ежемесячных выплат, сроков и т.д. За основу взяты реальные Контрольные задания, которые предлагается решить в ВУЗах, техникумах, школах и …
Сравним две таблицы имеющих практически одинаковую структуру. Таблицы различаются значениями в отдельных строках, некоторые наименования строк встречаются в одной таблице, но в другой могут отсутствовать.
Элемент Счетчик позволяет изменять значения в определенном диапазоне с определенным шагом (1, 2, 3, ...). По умолчанию диапазон изменения значений определен от 0 до 30000, шаг =1.
Показатели деятельности компании часто контролируются на периодической основе, что порождает соответствующие отчеты. Например, объем продаж по различным группам товаров ежемесячно вносится в таблицу. Для быстрого анализа изменений, произошедших за последний …
Продолжим идеи, изложенные в статье Отбор уникальных значений в MS EXCEL . Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем только уникальные …
Создадим модель для оптимизации затрат на приобретение и транспортировку товаров. Это очень распространенная задача, эффективное ее решение экономит компаниям миллионы рублей. Расчет будем проводить с помощью надстройки Поиск решения.
Управление капиталом — модель для поиска схемы получения максимальной прибыли при краткосрочных и долгосрочных вложениях. Расчет будем проводить с помощью надстройки Поиск решения. За основу возьмем пример из файла solvsamp.xls, …
Компания имеет на выбор 6 проектов с различным уровнем прибыльности и инвестиций. Определить оптимальный вариант инвестирования, при котором суммарный доход от проектов будет максимальным. Расчет будем проводить с помощью надстройки …
Рассмотрим таблицу продаж, состоящую из столбцов Дата продажи и Сумма. Т.к. в день может быть несколько продаж, то столбец с датами содержит повторы. Задав в качестве критерия поиска дату, найдем …
Компания перевозит изделия со своих заводов в складские комплексы. Общая мощность заводов достаточна для удовлетворения существующего спроса, однако уровень транспортных затрат слишком высок. Желая оптимизировать расходы на транспортировку, Компания рассматривает …
Компания распределяет новых сотрудников по офисам. Каждый сотрудник высказал свое предпочтение по каждому из офисов. Необходимо распределить сотрудников по офисам так, чтобы было как можно меньше недовольных. Расчет будем проводить …
Рабочие распределяются по сменам. Каждая смена длится 1 неделю, в которой 5 рабочих дней и 2 выходных. Необходимо обеспечить, определенное количество рабочих каждый день недели. Определить минимальное количество рабочих. Расчет …
Рассмотрим основы создания и настройки диаграмм в MS EXCEL 2010. Материал статьи также будет полезен пользователям MS EXCEL 2007 и более ранних версий. Здесь мы не будем рассматривать типы диаграмм …
Решим Систему Линейных Алгебраических Уравнений (СЛАУ) методом Крамера в MS EXCEL. В этой статье нет теории, объяснено только как выполнить расчеты, используя MS EXCEL.
Пусть имеется несколько множеств — {A1, A2, A3, А4 ...}, {B1, B2, B3, ...}, {C1, C2, ...}, количество элементов в которых может быть различно. Требуется составить все возможные комбинации элементов …
Пусть имеется 5 столбцов с данными. В каждом столбце по 10 чисел, необходимо найти сумму чисел в каждом столбце. Обычно итоговое значение выводится внизу столбца или над его заголовком, поэтому …
Здесь даны ссылки на статьи о проверке статистических гипотез о среднем значении и дисперсии распределения. Также рассмотрены двухвыборочный z -тест, t -тест и F -тест.
Часто текстовая строка может содержать несколько значений. Например, адрес компании: "г.Москва, ул.Тверская, д.13", т.е. название города, улицы и номер дома. Если необходимо определить все компании в определенном городе, то нужно …
Научимся вращать в MS EXCEL трехмерные фигуры вокруг координатных осей Х, Y, Z, а также поворачивать плоскости вокруг произвольно заданной оси. Для этого используем соответствующие матрицы вращения. Также покажем, что …
Решим задачу об оптимизации плана-графика работ по проекту с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника "Методы оптимизации управления и принятия решений" авторы Зайцев …
В качестве подписей данных в точечной диаграмме (XY Scatter) можно установить: имя ряда, значения Х и значения Y. Однако, часто требуется присвоить индивидуальную подпись для каждой точки. Если у Вас …
Рассмотрим стандартный фильтр ( Данные/ Сортировка и фильтр/ Фильтр ) – автофильтр. Это удобный инструмент для отбора в таблице строк, соответствующих условиям, задаваемым пользователем.
Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), - преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ("11.09.2009") возвращает число 40067, соответствующее 11 …
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью экспоненциальной функции.
Сценарии - это инструмент MS EXCEL из группы Анализ "что-если" ( Вкладка Данные/ Группа Работа с данными ). Диспетчер сценариев позволяет создавать и подставлять различные значения исходных данных в модель, …
Сравним две таблицы имеющих одинаковую структуру (одинаковое количество строк и столбцов). Таблицы будем сравнивать построчно: выделим те значения из строки1 таблицы1, которые содержатся в строке1 таблицы2, а также значения из …
В статье рассмотрены финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , ПС() , БС() , а также ОБЩДОХОД() и ОБЩПЛАТ() , которые используются для расчетов параметров …
Пусть имеется таблица наименований обуви. Каждое наименование обуви (столбец №1) повторяется столько раз, сколько у него имеется различных размеров (столбец №2) . Иногда требуется «перевернуть» не всю таблицу, а только …
Функция ДВССЫЛ() , английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой . Например, формула = ДВССЫЛ("Лист1!B3") эквивалентна формуле = Лист1!B3 . Мощь этой функции состоит в том, что …
Функция ТЕКСТ( ) , английская версия TEXT() , преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(100;"0,00 р.") вернет текстовую строку 100,00 …
Задача функции СЕГОДНЯ( ) , английский вариант TODAY(), - вернуть текущую дату. Записав, формулу =СЕГОДНЯ() получим 21.05.2011 (если конечно сегодня этот день).
Планируйте размещение данных в книге, на листе, в таблице, так как этого ожидает EXCEL . Похоже, что разработчики EXCEL хорошо знают типичные задачи, стоящие перед пользователями и создали такую среду, …
Формулы массива могут возвращать как единственное значение, так и несколько значений. В первом случае для отображения результата потребуется одна ячейка, во втором – диапазон. В этой статье рассмотрим формулы массива, …
Элементы управления формы (Поле со списком, Флажок, Счетчик и др.) помогают быстро менять данные на листе в определенном диапазоне, включать и выключать опции, делать выбор и пр. В принципе, без …
Рассмотрим простые проценты - метод начисления, при котором сумма начисленных процентов определяется исходя только из первоначальной величины вклада (или долга). Процент на начисленные проценты не начисляется (проценты не капитализируются).
Задача функции ЕТЕКСТ() , английский вариант ISTEXT(), - проверять является ли содержимое ячейки текстовым значением или нет. Формула = ЕТЕКСТ(5) вернет ЛОЖЬ, а =ЕТЕКСТ("Привет!") вернет ИСТИНА.
Функция ПЛТ( ) , английский вариант PMT(), позволяет рассчитать месячную сумму платежа по кредиту в случае аннуитетных платежей (когда за кредит платится равными частями).
Функция АДРЕС() , английский вариант ADDRESS(), возвращает адрес ячейки на листе, для которой указаны номера строки и столбца. Например, формула АДРЕС(2;3) возвращает значение $C$2 .
Функция ВЫБОР() , английский вариант CHOOSE(), возвращает значение из заданного списка аргументов-значений в соответствии с заданном индексом. Например, формула =ВЫБОР(2;"ОДИН";"ДВА";"ТРИ") вернет значение ДВА. Здесь 2 - это значение индекса, а …
При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Например, при заполнении ведомости ввод фамилий сотрудников с клавиатуры можно заменить выбором из определенного заранее списка (табеля).
Главный недостаток стандартного фильтра ( Данные/ Сортировка и фильтр/ Фильтр ) – это отсутствие визуальной информации о примененном в данный момент фильтре: необходимо каждый раз лезть в меню фильтра, чтобы …
Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону с датами .
Произведем подсчет строк таблицы, удовлетворяющих сразу трем критериям, которые образуют Условие ИЛИ и Условие И, затем просуммируем числовые значения, этих строк. Например, в таблице с перечнем Фруктов и их количеством …
Запишем число прописью в Excel без использования VBA . Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 …
Найти, например, второе наибольшее значение в списке можно с помощью функции НАИБОЛЬШИЙ() . В статье приведено решение задачи, когда наибольшее значение нужно найти не среди всех значений списка, а только …
Пусть дана таблица с двумя столбцами: текстовый столбец с повторами и числовой столбец. Просуммируем только те числа, которые соответствуют неповторяющимся текстовым значениям.
Определим, имеется ли в текстовой строке буквы из латиницы, цифры или ПРОПИСНЫЕ символы. Научимся определять наличие нежелательных символов одной формулой.
Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существуют специальные функции, например, ВПР() , но они работают только если исходный список сортирован по возрастанию или убыванию.
Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существует специальные функции, например, ВПР() , ПРОСМОТР() , ПОИСКПОЗ() , но они работают только если исходный список сортирован по возрастанию или убыванию. …
Нахождение максимального/ минимального значения - простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию.
Ручной перевод формул с английского языка на русский всегда утомителен. Например, если нужно перевести RIGHT(A1,LEN(A1)) в ПРАВСИМВ(A1;ДЛСТР(A1)) . Попробуем автоматизировать процесс.
Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). Расчет будем проводить с помощью надстройки Поиск решения.
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию с учетом РЕгиСТРА. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для …
Маркер заполнения - небольшой черный квадратик, который появляется в правом нижнем углу выделенной ячейки или выделенного диапазона. Маркер заполнения используется для заполнения соседних ячеек на основе содержимого выделенных ячеек.
Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, F -статистики и др.).
Рассмотрим анализ таблиц в программе NeoNeuro PIVOT TABLE. Эта программа позволяет создать сводную таблицу, диаграмму и отчет буквально за несколько секунд.
Если текстовая строка в ячейке содержит несколько слов, например, «Василий Иванович Петров», то можно создать формулу для вывода, например, первого (второго, третьего и т.д.) слова.
Рассмотрим Отрицательное Биномиальное распределение, вычислим его математическое ожидание и дисперсию. С помощью функции MS EXCEL ОТРБИНОМ.РАСП() построим графики функции распределения и плотности вероятности.
Для построения формул массива иногда используют числовую последовательность, например {1:2:3:4:5:6:7}, вводимую непосредственно в формулу. Эту последовательность можно сформировать вручную, введя константу массива , или с использованием функций, например СТРОКА() . …
Подсчитаем количество рабочих дней между двумя датами в случае нестандартной рабочей недели: в случае четырехдневной недели или когда выходные дни - воскресенье и среда. При подсчете учтем праздники.
Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.
Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .
При расчете временных интервалов, результат может получиться в годах с дробной частью (6,9 лет). Определим, сколько месяцев составляет 0,9 лет. Результат запишем в виде: 6 лет / 10 месяцев.
Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах . Рассмотрим процесс создания несложных Сводных таблиц.
Для упрощения управления логически связанными данными в EXCEL 2007 введен новый формат таблиц. Использование таблиц в формате EXCEL 2007 снижает вероятность ввода некорректных данных, упрощает вставку и удаление строк и …
Если возникла необходимость выполнить вычисления с датами до 01.01.1900, то придется прибегнуть к некоторым ухищрениям, чтобы использовать встроенные функции EXCEL для работы с датами.
В MS WORD спецсимволы (конец абзаца, разрыв строки и т.п.) легко найти с помощью стандартного поиска ( CTRL+F ), т.к. им соответствуют специальные комбинации символов (^v, ^l). В окне стандартного …
Функция СМЕЩ( ) , английский вариант OFFSET(), возвращает ссылку на диапазон ячеек. Размер диапазона и его положение задается в параметрах этой функции.
Наиболее быстрый способ добиться, чтобы содержимое ячеек отображалось полностью – это использовать механизм автоподбора ширины столбца/ высоты строки по содержимому.
Буквы могут находиться в ВЕРХНЕМ и нижнем регистре (ПРОПИСНЫЕ и строчные). Текстовые строки, соответственно, могут состоять целиком из строчных или ПРОПИСНЫХ букв, а также состоять из букв находящихся в разном …
Рассмотрим функцию ВРЕМЯ() , у которой 3 аргумента: часы, минуты, секунды. Записав формулу =ВРЕМЯ(10;30;0) , получим в ячейке значение 10:30:00 в формате Время. Покажем, что число 0,4375 соответствует 10:30 утра.
Найдем числовые значения, равные заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в …
Функция ДАТА() , английский вариант DATE(), в озвращает целое число, представляющее определенную дату. Формула =ДАТА(2011;02;28) вернет число 40602. Если до ввода этой формулы формат ячейки был задан как Общий, то …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о равенстве дисперсий 2-х нормальных распределений. Вычислим значение тестовой статистики F 0 , рассмотрим процедуру «двухвыборочный F -тест», вычислим Р-значение (Р- value …
Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот …
Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца . Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего …
Необходимо найти максимальную пропускную способность сетевого трубопровода. Построим линейную модель и с помощью надстройки Поиск решения решим задачу.
Произведем подсчет строк таблицы, удовлетворяющих сразу трем критериям, которые образуют Условие ИЛИ и Условие И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых …
Для отбора уникальных значений можно использовать формулы , расширенный фильтр или можно воспользоваться меню Данные/ Работа с данными/ Удалить дубликаты . В этой статье используем Сводные таблицы .
Имеется таблица, состоящая из двух столбцов: из столбца с повторяющимися текстовыми значениями и столбца с числами. Создадим таблицу, состоящую только из строк, с уникальными текстовыми значениями. По числовому столбцу произведем …
Имея список с повторяющимися значениями, создадим список, состоящий только из уникальных значений. При добавлении новых значений в исходный список, список уникальных значений должен автоматически обновляться.
Справочник состоит из двух таблиц: справочной таблицы, в строках которой содержатся подробные записи о некоторых объектах (сотрудниках, товарах, банковских реквизитах и пр.) и таблицы, в которую заносятся данные связанные с …
Имея список с повторяющимися текстовыми значениями, создадим список, состоящий только из неповторяющихся значений. А из столбца соседнего с исходным, выведем соответствующие им числовые значения.
В этой статье приводится перечень контрольных заданий по теме Автофильтр . За основу взяты реальные Контрольные задания, которые предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по теме …
В России принято записывать денежный формат в виде 123 456 789,00р. В США десятичная часть отделяется от целой не запятой, точкой, а разряды не пробелом, а запятой. Если требуется отобразить …
Рассчитаем в MS EXCEL сумму процентов, которую необходимо выплатить за определенное количество периодов по кредиту. Выплата кредита производится равными ежемесячными платежами (аннуитетная схема). Процентная ставка и величина платежа - известны, …
При составлении формул для отображения в ячейке фразы содержащей текст и число могут возникнуть сложности с правильным отображением формата числа. Например, требуется составить фразу «Масса груза 66,00 кг», но получается …
EXCEL хранит ВРЕМЯ в числовой форме (в дробной части числа). Например, 0,75 соответствует 18:00, 0,5 - 12:00. Если, по какой-то причине, значения ВРЕМЕНИ сохранены в десятичной форме, например, 10,5 часов, …
В EXCEL легко отформатировать шрифт, чтобы отобразить надстрочные (x 2 ) и подстрочные (Al 2 O 3 ) символы. Это можно сделать выделив часть текста в ячейке и через диалоговое …
Если у вас есть таблица с важными данными (номера кредитных карт, номера личных телефонов, номера страховых полисов), то для сторонних лиц вы можете настроить отображение только последних цифр номера.
Часто, особенно при импорте данных в EXCEL , на листе могут формироваться таблицы с ПОЛНОСТЬЮ пустыми строками. Научимся быстро удалять эти ненужные строки, которые в дальнейшем могут затруднить работу с …
В этой статье приводится перечень контрольных заданий по теме Расширенный фильтр . За основу взяты реальные Контрольные задания, которые предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по …
В этой статье приводится перечень контрольных заданий по теме Промежуточные Итоги . За основу взяты реальные Контрольные задания, которые предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по …
Функция СЛЧИС( ) , английский вариант RAND(), возвращает случайное вещественное число ( равномерно распределенное ), которое большее или равно 0 и меньше 1 (например, 0,797285074257933).
Функция НАИМЕНЬШИЙ( ) , английский вариант SMALL(), возвращает k-ое наименьшее значение из массива данных. Например, если диапазон A1:А4 содержит значения 2;10;3;7, то формула = НАИМЕНЬШИЙ (A1:А4;2) вернет значение 3 (второе …
Подписи данных на диаграмме часто необходимы, но могут ее загромождать. Отобразим только те подписи данных (значения), которые больше определенного порогового значения.
Элемент управления формы Список выводит список нескольких элементов, которые может выбрать пользователь. Этот элемент имеет много общего с элементом Поле со списком.
Определим дату ближайшей субботы перед заданным днем. Т.е. если сегодня 25.11.2014 (вторник), то ближайшая прошедшая суббота - 22.11.2014. Также определим дату ближайшей субботы после заданного дня и просто дату ближайшей …
В статье Многоуровневый связанный список рассмотрен вариант 3-х уровневого списка. Элементы каждого уровня в нем располагаются на отдельных листах. Это не всегда удобно: при создании 4-х и 5-и уровневых списков …
Создадим модель для одной из модификаций классической задачи о рюкзаке: укладка как можно большего числа вещей в рюкзак при условии, что общий объём (или вес) всех предметов, способных поместиться в …
Создадим модель для решения следующей задачи: компания желает минимизировать затраты, стоит ли ей закрыть один из своих заводов? Расчет будем проводить с помощью надстройки Поиск решения.
Небольшая авиакомпания должна организовать регулярные полеты между тремя городами. Необходимо составить расписание полетов так, чтобы минимизировать издержки компании. Расчет будем проводить с помощью надстройки Поиск решения.
Определим выражение для вычисления ошибки второго рода и мощности теста, построим в MS EXCEL кривые оперативной характеристики (Operating-characteristic curves).
Построим диаграмму Водопад, которая используется для визуализации динамики показателя (за одинаковые периоды), а также для демонстрации влияния различных факторов на показатель . Диаграмма Водопад также часто называют "каскадной диаграммой".
Рассмотрим построение в MS EXCEL 2010 диаграмм с несколькими рядами данных, а также использование вспомогательных осей и совмещение на одной диаграмме диаграмм различных типов.
Из исходной таблицы отберем только уникальные значения и выведем их в отдельный диапазон с сортировкой по возрастанию. Отбор и сортировку сделаем с помощью одной формулой массива. Формула работает как для …
Рассмотрим трехмерные диаграммы в MS EXCEL 2010. С помощью трехмерных диаграмм отображают поверхности объемных фигур (гиперболоид, эллипсоид и др.) и изолинии.
Нахождение максимального/ минимального значения - простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию. Эта …
В статье приведен перечень распределений вероятности, имеющихся в MS EXCEL 2010 и в более ранних версиях. Даны ссылки на статьи с описанием соответствующих функций MS EXCEL.
С помощью функции ВПР() можно выполнить поиск в столбце таблицы (называется ключевым столбцом), а затем вернуть значение из той же строки, но другого столбца. Здесь рассмотрим более сложный поиск: искать …
Подсчитаем в MS EXCEL количество Размещений из n по k и с помощью формул выведем на лист соответствующие варианты размещений (английский перевод термина: partial permutation или sequence without repetition).
Подсчитаем в MS EXCEL количество Размещений с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Размещений (английский перевод термина: sequence with …
Подсчитаем в MS EXCEL количество перестановок из n элементов. С помощью формул выведем на лист все варианты перестановок (английский перевод термина: permutation).
В этой статье рассмотрены операции сложения и вычитания над матрицами одного порядка, а также операции умножения матрицы на число. Примеры решены в MS EXCEL.
В этой статье рассмотрены операции умножения матриц с помощью функции МУМНОЖ() или англ.MMULT и с помощью других формул, а также свойства ассоциативности и дистрибутивности операции умножения матриц. Примеры решены в …
Вычислим определитель (детерминант) матрицы с помощью функции МОПРЕД() или англ. MDETERM, разложением по строке/столбцу (для 3 х 3) и по определению (до 6 порядка).
Транспонирование матрицы - это операция над матрицей, при которой ее строки и столбцы меняются местами. Для этой операции в MS EXCEL существует специальная функция ТРАНСП() или англ. TRANSPOSE.
Найдем длину вектора по его координатам (в прямоугольной системе координат), по координатам точек начала и конца вектора и по теореме косинусов (задано 2 вектора и угол между ними).
Метод критического пути (МКП) - это метод планирования выполнения работ, в основе которого лежит математический алгоритм. Рассчитаем критический путь для связанных между собой работ проекта в MS EXCEL.
Построим сетевую диаграмму проекта на диаграмме MS EXCEL. Сетевая диаграмма будет автоматически перестраиваться при изменении связей между работами. Для этого нам потребуется автоматически определить все пути проекта (не только критические).
Подсчитаем в MS EXCEL количество сочетаний из n элементов по k. С помощью формул выведем на лист все варианты сочетаний (английский перевод термина: Combinations without repetition).
Здесь даны ссылки на статьи о построении доверительных интервалов для оценки среднего значения и дисперсии распределения. Также рассмотрено построение доверительных интервалов в случае двух выборок.
Для сложных иерархических структур с тремя и более уровнями создадим Многоуровневый связанный список типа Предок-Родитель. Теперь структуры типа: Регион-Страна-Город-Улица можно создавать в MS EXCEL.
Решим задачу об оптимизации смен рабочих с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника "Методы оптимизации управления и принятия решений" авторы Зайцев М.Г. и …
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + …
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью степенной функции.
Рассмотрим Геометрическое распределение, вычислим его математическое ожидание и дисперсию. С помощью функции MS EXCEL ОТРБИНОМ.РАСП() построим графики функции распределения и плотности вероятности.
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).
Регрессия позволяет прогнозировать зависимую переменную на основании значений фактора. В MS EXCEL имеется множество функций, которые возвращают не только наклон и сдвиг линии регрессии, характеризующей линейную взаимосвязь между факторами, но …
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью тригонометрического полинома.
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью логарифмической функции.
Построим диаграмму рассеяния для различных видов взаимосвязей двух переменных. Сгенерируем различные варианты трендов: линейный, квадратичный и затухающий синусоидальный.
При частом вводе данных в формате времени (2:30), необходимость ввода двоеточия «:» серьезно снижает скорость работы. Возникает вопрос: Можно ли обойтись без ввода двоеточия?
Массив значений (или константа массива или массив констант) – это совокупность чисел или текстовых значений, которую можно использовать в формулах массива . Константы массива необходимо вводить в определенном формате, например, …
Рассмотрим Распределение Стьюдента (t-распределение). С помощью функции MS EXCEL СТЬЮДЕНТ.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Рассмотрим Распределение ХИ-квадрат. С помощью функции MS EXCEL ХИ2.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Рассмотрим распределение Пуассона, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ПУАССОН.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения, его математического ожидания и …
Если в ячейке содержится дата или номер месяца, то с помощью формул или Формата ячейки можно вывести название месяца. Также решим обратную задачу: из текстового значения названия месяца получим его …
Рассмотрим Экспоненциальное распределение, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ЭКСП.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметра …
Напишем формулу для округления числа до первой значащей цифры. Например, 0,00234271 до 0,002; 0,01613 до 0,02. Та же формула округлит 233,64 до 200; 2563,6 до 3000.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ( ) , английский вариант SUBTOTAL(), используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью квадратичной функции y=ax 2 +bx+с .
Даны определения Функции распределения и Плотности вероятности непрерывной случайной величины. Рассмотрены примеры вычисления Функции распределения и Плотности вероятности с помощью функций MS EXCEL .
Рассмотрим Гипергеометрическое распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ГИПЕРГЕОМ.РАСП() построим графики функции распределения и плотности вероятности. Приведем пример аппроксимации гипергеометрического распределения биномиальным.
Продемонстрируем основные выводы Центральной предельной теоремы с помощью MS EXCEL : построим выборочное распределение среднего, рассчитаем стандартную ошибку и сравним значения, полученные на основе выборки, с выводами ЦПТ.
Если ячейка содержит значение больше 1000, то часто не требуется указывать точное значение, а достаточно указать число тысяч или миллионов, один-два знака после запятой и соответственно сокращение: тыс. или млн. …
Научимся строить изолинии (contour line) в MS EXCEL для одного из частных случаев поверхностей второго порядка: 2*a 12 *x*y+2*a 14 *x+ 2*a 24 *y +2*a 34 *z+a 44 =0 . …
Диаграмма Водопад (или каскадная диаграмма) является стандартным средством визуализации изменения финансовых показателей компании. В этой статье для построения каскадной диаграммы воспользуемся надстройкой Waterfall Chart Studio от компании fincontrollex.com.
Рассмотрим распределение Вейбулла, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ВЕЙБУЛЛ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров …
Рассмотрим Биномиальное распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БИНОМ.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения p, математического ожидания распределения …
Рассмотрим Логнормальное распределение. С помощью функции MS EXCEL ЛОГНОРМ .РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по логнормальному закону, произведем оценку параметров распределения, среднего …
Рассмотрим Бета-распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БЕТА.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров распределения.
Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.
Построение графика проверки распределения на нормальность ( Normal Probability Plot ) является графическим методом определения соответствия значений выборки нормальному распределению.
Гистограмма распределения - это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и …
Для вычисления квартилей в MS EXCEL существует специальная функция КВАРТИЛЬ() . В этой статье дадим определение квартилей и научимся их вычислять для выборки и для непрерывного распределения. Также вычислим интерквартильный …
Гистограмма - это инструмент, позволяющий визуально оценить величину и характер разброса данных в выборке. С помощью диаграммы MS EXCEL создадим двумерную гистограмму для сравнения 2-х наборов данных.
Пусть задана некая (пользовательская) функция распределения дискретной случайной величины. Сгенерируем случайное число из этой генеральной совокупности. Также рассмотрим функцию ВЕРОЯТНОСТЬ() .
Рассмотрим Нормальное распределение. С помощью функции MS EXCEL НОРМ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по нормальному закону, произведем оценку параметров распределения, среднего значения …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (дисперсии этих 2-х распределений разные). Вычислим значение тестовой статистики t 0 *, …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о среднем значении распределения в случае известной дисперсии. Вычислим тестовую статистику Z 0 , рассмотрим процедуру «одновыборочный z-тест», вычислим Р-значение (Р- value …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (дисперсии этих 2-х распределений одинаковы). Вычислим значение тестовой статистики t 0 , …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (парный тест). Вычислим значение тестовой статистики t 0 , рассмотрим соответствующую процедуру …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о дисперсии нормального распределения. Вычислим тестовую статистику χ 2 и Р-значение (Р- value ).
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае известных дисперсий. Вычислим значение тестовой статистики Z 0 , рассмотрим процедуру «двухвыборочный z-тест», вычислим …
Критерий независимости хи-квадрат используется для определения связи между двумя категориальными переменными. Примерами пар категориальных переменных являются: Семейное положение vs. Уровень занятости респондента; Порода собак vs. Профессия хозяина, Уровень з/п vs. …
Рассмотрим использование MS EXCEL при проверке статистических гипотез о среднем значении распределения в случае неизвестной дисперсии. Вычислим тестовую статистику t 0 , рассмотрим процедуру «одновыборочный t -тест», вычислим Р-значение (Р- …
Пусть имеется случайная переменная Y , значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от фактора, значения которого мы можем контролировать, т.е. задавать с требуемой точностью. Покажем …
В статье напомним некоторые понятия математической статистики: выборка, статистика, точечная оценка, выборочное распределение. Продемонстрируем в MS EXCEL сходимость некоторых распределений статистик к нормальному распределению, распределению ХИ-квадрат, распределению Стьюдента и F …