Excel 2010 в Excel. Бесплатные примеры и статьи.


Контроль ввода ОШИБочных значений в MS EXCEL. Часть 1 - с применением Условного форматирования


Используем Условное форматирование для подачи сигнала пользователю при вводе ошибочного значения. Под «ошибочным значением» здесь будем понимать значение, не принадлежащее заданному диапазону.

update Опубликовано: 26 октября 2012

Динамические диаграммы в MS EXCEL. Часть2: Выборка данных формулами


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

update Опубликовано: 03 декабря 2012

Динамические диаграммы в MS EXCEL. Часть4: Выборка данных из определенного диапазона


Часто на диаграмме необходимо отобразить не все данные из исходной таблицы, а лишь только часть, например, значения из 10 последних строк. Причем диаграмма должна динамически изменяться в зависимости от того, …

update Опубликовано: 03 декабря 2012

Пользовательский ЧИСЛОвой формат в MS EXCEL (через Формат ячеек)


В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби …

update Опубликовано: 31 марта 2013

Построение в MS EXCEL диаграммы процента выполнения работ


Построим в MS EXCEL диаграмму, иллюстрирующую % выполнения работ.

update Опубликовано: 03 декабря 2012

Диаграмма Ганта в MS EXCEL с % выполнения и связями между работами


Построим диаграмму Ганта отображающую связанные работы и процент их выполнения.

update Опубликовано: 03 декабря 2012

Динамическое название диаграммы в MS EXCEL


Свяжем название диаграммы в MS EXCEL со значением в ячейке.

update Опубликовано: 03 декабря 2012

Удаляем в MS EXCEL пропуски в списке формулами (удалить пустые ячейки и нули в таблице)


Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить …

update Опубликовано: 24 апреля 2013

Ввод данных из списка значений. Часть 2: Выпадающий список в MS EXCEL с контролем дублирования


При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Например, при заполнении ведомости ввод фамилий сотрудников с клавиатуры можно заменить выбором из определенного заранее списка (табеля), а …

update Опубликовано: 04 декабря 2012

Функция ДСРЗНАЧ() - Вычисление среднего с множественными условиями в MS EXCEL


Функция ДСРЗНАЧ() , английский вариант DAVERAGE(), вычисляет среднее значение в столбце, но только среди значений удовлетворяющих заданным условиям.

update Опубликовано: 05 января 2013

Именованная формула в MS EXCEL


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

update Опубликовано: 21 января 2013

Выпадающий список в MS EXCEL на основе Проверки данных


При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов …

update Опубликовано: 21 января 2013

Динамический выпадающий список в MS EXCEL


При заполнении ячеек данными, бывает необходимо ограничить возможность ввода определенным списком значений – это можно сделать с помощью Выпадающего списка . Если одновременно необходимо обеспечить ввод только неповторяющихся значений, то …

update Опубликовано: 21 января 2013

Расширяемый Связанный список в MS EXCEL


В статье рассмотрен улучшенный вариант Связанного списка .

update Опубликовано: 21 января 2013

Запрос на выборку данных в MS EXCEL (на основе элементов управления формы)


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

update Опубликовано: 21 января 2013

Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И)


Произведем сложение значений находящихся в строках, поля которых удовлетворяют сразу двум критериям (Условие И). Рассмотрим Текстовые критерии, Числовые и критерии в формате Дат. Разберем функцию СУММЕСЛИМН( ) , английская версия …

update Опубликовано: 02 апреля 2013

Сложение значений на основании их повторяемости (Часть3.Сложение Повторяющихся) в MS EXCEL


Диапазон суммирования содержит повторяющиеся значения. Произведем только тех чисел, которые имеют повторы.

update Опубликовано: 03 апреля 2013

Максимальное значение по модулю в MS EXCEL


Найдем максимальное значение по модулю в диапазоне с положительными и отрицательными числами.

update Опубликовано: 03 апреля 2013

Ближайший рабочий день в MS EXCEL с учетом праздников и переносов


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

update Опубликовано: 07 апреля 2013

Максимальное минимальное среди неповторяющихся значений в MS EXCEL


Найдем максимальное/ минимальное только среди неповторяющихся значений.

update Опубликовано: 03 апреля 2013

Максимальное минимальное среди повторяющихся значений в MS EXCEL


Найдем максимальное/ минимальное только среди повторяющихся значений.

update Опубликовано: 03 апреля 2013

Округляем до ближайшего ЧИСЛА, кратного заданному (четному, нечетному) в MS EXCEL


Округлить с точностью до 0,01; 0,1; 1; 10; 100 не представляет труда – для этого существует функция ОКРУГЛ() . А если нужно округлить, например, до ближайшего числа, кратного 50?

update Опубликовано: 03 апреля 2013

Подсчет вхождений в MS EXCEL Букв (Символов) в ТЕКСТовой строке


Найдем количество вхождений определенной Буквы или любого символа в ячейке EXCEL.

update Опубликовано: 03 апреля 2013

Определение имени листа в MS EXCEL для использования в функции ДВССЫЛ()


При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1") . …

update Опубликовано: 04 апреля 2013

Создаем в MS EXCEL Календарь одной формулой массива


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

update Опубликовано: 09 апреля 2013

Извлечение имени файла в MS EXCEL


Извлечем имя файла из полного имени файла (C:\WINDOWS\TXT\Chapter\ text03.txt)

update Опубликовано: 04 апреля 2013

Нахождение имени текущей книги в MS EXCEL


Имени текущей книги можно получить с помощью функции ЯЧЕЙКА() .

update Опубликовано: 04 апреля 2013

Ближайший рабочий день в MS EXCEL


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

update Опубликовано: 07 апреля 2013

Первый вторник месяца в MS EXCEL


Определим дату, на которую приходится первый вторник месяца, вторую пятницу или третью среду месяца.

update Опубликовано: 07 апреля 2013

Функция ФИКСИРОВАННЫЙ() в MS EXCEL


Функция ФИКСИРОВАННЫЙ( ) , английская версия FIXED() , о кругляет число до заданного количества десятичных цифр и возвращает результат в виде текста.

update Опубликовано: 12 апреля 2013

Последний вторник месяца в MS EXCEL


Определим последний вторник месяца, последнюю пятницу или последнюю среду.

update Опубликовано: 07 апреля 2013

MS EXCEL: Сколько полных месяцев прошло?


Определим, сколько полных месяцев прошло с определенной даты до сегодняшнего дня (или до заданной конечной).

update Опубликовано: 08 апреля 2013

Функция СЧЁТЕСЛИ() в MS Excel - Подсчет значений с единственным критерием


Для подсчета ЧИСЛОвых значений, Дат и Текстовых значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ( ) , английская версия COUNTIF(). Подсчитаем значения в диапазоне в случае одного критерия, …

update Опубликовано: 09 апреля 2013

Поиск значения в MS EXCEL с максимальной или минимальной длиной строки


Найдем в списке текстовых значений строку с максимальной / минимальной длиной .

update Опубликовано: 07 апреля 2013

Поиск ближайшего события в MS EXCEL


Пусть имеется список неких событий, например, праздников. Найдем ближайшее событие, которое еще не произошло.

update Опубликовано: 13 апреля 2013

Функция ГОД() в MS EXCEL


Функция ГОД() , английский вариант YEAR(), в озвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999.

update Опубликовано: 11 апреля 2013

Текстовые последовательности в MS EXCEL


Создадим последовательности текстовых значений различных видов: первый, второй, третий, ... товар1, товар2, товар3, ... А, Б, В, ...1-й период, 2-й период, ...1), 2), ... Также создадим последовательности произвольного вида.

update Опубликовано: 10 апреля 2013

Функция СРЗНАЧЕСЛИ() - Вычисление в MS EXCEL среднего по условию (один ЧИСЛОвой критерий)


Найдем среднее всех ячеек, значения которых соответствуют определенному критерию. Для этой цели существует простая и эффективная функция СРЗНАЧЕСЛИ( ) , английский вариант AVERAGEIF(), которая впервые появилась в EXCEL 2007.

update Опубликовано: 05 января 2013

Функция ЕОШ() в MS EXCEL


Функция ЕОШ() , английский вариант ISERR(), проверяет на равенство значениям: #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! и возвращает в зависимости от этого ИСТИНА или ЛОЖЬ.

update Опубликовано: 11 апреля 2013

Подсчет вхождений в MS EXCEL текстового значения в строке


Найдем количество вхождений подстроки (части строки, последовательности символов) в исходной текстовой строке.

update Опубликовано: 03 апреля 2013

Високосный ли год в MS EXCEL (сколько дней в году?)


Определим число дней в году (365 или 366).

update Опубликовано: 07 апреля 2013

Поиск позиции ЧИСЛА в MS EXCEL с выводом значения из соседнего столбца


Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие …

update Опубликовано: 14 апреля 2013

Сохранение нумерации в таблице при удалении строк в MS EXCEL


Если номера строк в таблице введены вручную, то при удалении строк таблицы нумерация собьется. Ищем решения, чтобы этого избежать.

update Опубликовано: 18 апреля 2013

Ввод данных в MS EXCEL по шаблону


Иногда пользователю необходимо ограничить возможность ввода определенным шаблоном. Например, при вводе артикулов известно, что правильный артикул имеет длину 6 символов, начинается с латинской буквы, далее идут 4 цифры, затем 1 …

update Опубликовано: 05 декабря 2012

Функция БСЧЁТА() - Подсчет ТЕКСТовых значений с множественными условиями в MS EXCEL


Функция БСЧЁТА() , английский вариант DCOUNTA(), подсчитывает текстовые значения в таблице данных, которые удовлетворяют заданным условиям.

update Опубликовано: 18 апреля 2013

Функция СЧЕТ() - Подсчет ЧИСЕЛ (в диапазонах с ТЕКСТом и ЧИСЛАми) в MS EXCEL


Подсчитаем количество ячеек содержащих числа с помощью функции СЧЁТ ( ) , английская версия COUNT() . Предполагаем, что диапазон содержит числа и числовые значения в текстовом формате.

update Опубликовано: 19 апреля 2013

Создание списка неповторяющихся значений с использованием Динамического выпадающего списка в MS EXCEL


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

update Опубликовано: 21 апреля 2013

Функция СРГЕОМ() - Среднее геометрическое в MS EXCEL


Функция СРГЕОМ( ) , английский вариант GEOMEAN(), возвращает среднее геометрическое своих аргументов.

update Опубликовано: 05 января 2013

Непрерывное начисление процентов в MS EXCEL


Рассмотрим непрерывное начисление сложных процентов в MS EXCEL при постоянной номинальной ставке.

update Опубликовано: 03 февраля 2015

Отбор уникальных значений на основании значений из соседнего столбца в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Отбор неповторяющихся значений в MS EXCEL


Имея список с повторяющимися значениями, создадим список, состоящий только из значений, которые не повторяются.

update Опубликовано: 22 апреля 2013

Сложение с одним критерием в MS EXCEL (Часть2.ТЕКСТовый критерий)


Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.

update Опубликовано: 02 апреля 2013

Сложение с несколькими критериями в MS EXCEL (Часть 5.Два Условия И)


Произведем подсчет значений, удовлетворяющих сразу трем критериям, которые образуют 2 Условия И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты …

update Опубликовано: 02 апреля 2013

Сложение цифр ЧИСЛА в MS EXCEL


Пусть имеется число 456258 и необходимо найти сумму всех его цифр, т.е. 4+5+6+2+5+8. Сделать это можно одной формулой.

update Опубликовано: 02 апреля 2013

Выделение неповторяющихся значений с учетом РеГИстРа в MS EXCEL


Настроим Условное форматирование для выделения только неповторяющихся значений и с учетом РЕгиСТра. Т.е. значения «Петров» и «петров» будут считаться неповторяющимися (разными значениями).

update Опубликовано: 22 апреля 2013

Функция ЕЧИСЛО() в MS EXCEL


Задача функции ЕЧИСЛО() , английский вариант ISNUMBER(), - проверять являются ли значения числами или нет. Формула = ЕЧИСЛО(5) вернет ИСТИНА, а =ЕЧИСЛО("Привет!") вернет ЛОЖЬ.

update Опубликовано: 11 декабря 2013

Дифференцированные платежи по кредиту в MS EXCEL


Составим в MS EXCEL график погашения кредита дифференцированными платежами.

update Опубликовано: 03 февраля 2015

Игнорируем ошибку #Н/Д при суммировании в MS EXCEL


Если в диапазоне суммирования встречается значение ошибки #Н/Д (значение недоступно), то функция СУММ() также вернет ошибку. Используем функцию СУММЕСЛИ() для обработки таких ситуаций.

update Опубликовано: 02 апреля 2013

Отбор уникальных СТРОК с помощью Расширенного фильтра в MS EXCEL


Для отображения уникальных строк можно использовать Расширенный фильтр.

update Опубликовано: 22 апреля 2013

Подсчет уникальных строк в MS EXCEL


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

update Опубликовано: 23 апреля 2013

Отображение миллисекунд в MS EXCEL


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

update Опубликовано: 25 апреля 2013

Функция ЕПУСТО() в MS EXCEL


Задача функции ЕПУСТО() , английский вариант ISBLANK(), - проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется значение 555, то формула = ЕПУСТО(А1) …

update Опубликовано: 16 декабря 2013

Функция ГИПЕРССЫЛКА() в MS EXCEL


Функция ГИПЕРССЫЛКА() , английский вариант HYPERLINK(), создает ярлык или гиперссылку, которая позволяет открыть страницу в сети интернет, файл на диске (документ MS EXCEL, MS WORD или программу, например, Notepad.exe) или …

update Опубликовано: 14 апреля 2014

Выделение первых вхождений дубликатов в MS EXCEL


Настроим Условное форматирование для выделения среди повторяющихся значений только тех, которые встречаются первыми (сверху).

update Опубликовано: 22 апреля 2013

Выделение всех повторов в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в MS EXCEL)


При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам …

update Опубликовано: 25 апреля 2013

Microsoft Excel 2010 (краткий обзор)


Статья посвящается тем пользователям, которые продолжают работать в версии MS EXCEL 2007, но уже подумывают о переходе на новую версию.

update Опубликовано: 25 октября 2014

Сложение с множественным выбором в MS EXCEL


Произведем сложение значений, которые удовлетворяют хотя бы одному из 3-х критериев (Условие ИЛИ). Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце …

update Опубликовано: 21 апреля 2014

Заполнение пустых ячеек в MS EXCEL


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

update Опубликовано: 26 апреля 2013

Склоняем правильно в MS EXCEL число месяцев и дней (1 месяц, 2 месяца, 10 месяцев)


Учимся корректно склонять число месяцев: 1 месяц, 2 месяца, 10 месяцев, 1 день, 2 дня, 10 дней.

update Опубликовано: 03 июня 2013

Отбор четных и нечетных строк таблицы в MS EXCEL


Для извлечения из таблицы только четных или нечетных строк можно использовать формулы и Автофильтр .

update Опубликовано: 11 апреля 2014

Функция ЧСТРОК() в MS EXCEL


Функция ЧСТРОК() , в английском варианте ROWS(), подсчитывает число строк в диапазоне ячеек или массиве констант. Например, формула ЧСТРОК(A1:F3) возвращает значение 3, т.к. в заданном диапазоне 3 строки: 1, 2, …

update Опубликовано: 13 апреля 2014

Суммирование нарастающим итогом в MS EXCEL


Используем смешанную алресацию для для создания столбца с суммированием нарастающим итогом.

update Опубликовано: 03 апреля 2013

Функция СТРОЧН() в MS EXCEL


Функция СТРОЧН() , английская версия LOWER(), делает все буквы в тексте строчными (в нижнем регистре).

update Опубликовано: 29 мая 2014

Номер квартала в MS EXCEL


Имея дату (01/02/09), определим квартал, к которому она принадлежит (ответ: первый квартал).

update Опубликовано: 26 апреля 2013

Функция РИМСКОЕ() в MS EXCEL


Функция РИМСКОЕ( ) , английский вариант ROMAN(), преобразует арабское число в римское, которое возвращается в текстовом формате.

update Опубликовано: 23 мая 2014

Функция ПРОПИСН() в MS EXCEL


Функция ПРОПИСН( ) , английский вариант UPPER(), делает все буквы в тексте ПРОПИСНЫМИ (ЗАГЛАВНЫМИ).

update Опубликовано: 29 мая 2014

Функция ПОИСКПОЗ() в MS EXCEL


Функция ПОИСКПОЗ( ) , английский вариант MATCH(), возвращает позицию значения в диапазоне ячеек. Например, если в ячейке А10 содержится значение "яблоки", то формула =ПОИСКПОЗ ("яблоки";A9:A20;0) вернет 2, т.е. искомое значение …

update Опубликовано: 09 января 2014

Дата конца и начала квартала в MS EXCEL


Найдем дату конца квартала, к которому относится заданная дата. Например, если задана дата 05.11.12, то дата конца квартала 31.12.12. Также найдем дату начала соответствующего квартала.

update Опубликовано: 19 июня 2014

Полоса прокрутки - элемент управления формы в MS EXCEL


Элемент Полоса прокрутки позволяет изменять значения в определенном диапазоне с шагом (1, 2, 3, ...), если нажимать на кнопки со стрелочками, и с увеличенным шагом, если нажимать на саму полосу …

update Опубликовано: 09 ноября 2014

Избавляемся от двойных кавычек


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

update Опубликовано: 17 ноября 2014

Ступенчатый график в MS EXCEL


Построим ступенчатый график (диаграмму) в MS EXCEL.

update Опубликовано: 19 января 2015

Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа)


Рассчитаем в MS EXCEL сумму регулярного аннуитетного платежа при погашении ссуды. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу ежемесячных платежей …

update Опубликовано: 02 февраля 2015

Поиск позиции последнего повтора MS EXCEL


В списке повторяющихся числовых или текстовых значений найдем позицию последнего повтора.

update Опубликовано: 02 сентября 2018

Поиск решения MS EXCEL (1.1a). Оптимальная структура выпускаемой продукции (с приобретением недостающего объема продукции)


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

update Опубликовано: 24 марта 2015

Поиск решения MS EXCEL (5.4). Реорганизация подразделения: обучение, прием, увольнение


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

update Опубликовано: 27 марта 2015

Оглавление книги на основе гиперссылок в MS EXCEL


Создадим оглавление Книги с помощью функции ГИПЕРССЫЛКА() .

update Опубликовано: 15 апреля 2014

Поиск решения MS EXCEL (6.8). Оптимальный размер изделия


Определим оптимальный размер изделия, при котором затраты на его производство минимальны. Решение найдем с помощью надстройки Поиск решения.

update Опубликовано: 28 марта 2015

Однофакторный дисперсионный анализ (one-way ANOVA) в MS EXCEL


Пусть имеется случайная переменная Y , значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от фактора, значения которого мы можем контролировать, т.е. задавать с требуемой точностью. Покажем …

update Опубликовано: 26 января 2019

Эллипс и окружность в MS EXCEL


Построим окружность и эллипс.

update Опубликовано: 29 марта 2015

Извлекаем в MS EXCEL из ЧИСЛА единицы, десятки, сотни, тысячи и миллионы


Извлечем из числа 123 456 789 единицы (789), десятки (8), сотни (7), тысячи (456) и миллионы (123) .

update Опубликовано: 03 апреля 2013

Подсчет количества выходных дней в диапазоне дат MS EXCEL


Подсчитаем количество выходных дней, содержащихся в диапазоне дат. Даты введены в ячейки листа. Выходными днями считаются суббота и воскресенье (праздники не учитываются).

update Опубликовано: 10 апреля 2015

Русский и английский алфавит в строке MS EXCEL


Сгенерируем русский (АБВГДЕЖЗИЙКЛМНОП...) и английский (ABCDEFGHIJKLMNOPQRST...) алфавит с помощью функции СИМВОЛ() .

update Опубликовано: 16 апреля 2015

Поиск решения MS EXCEL (1.5). Распределение ресурсов (ограничение - требования к качеству продукции, несколько периодов)


Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к качеству продукции. Расчет будем проводить с помощью …

update Опубликовано: 25 марта 2015

Вращение графика в MS EXCEL


Научимся вращать (поворачивать) график функции относительно начала координат.

update Опубликовано: 16 апреля 2015

Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL


Задана произвольная функция распределения дискретной случайной величины. Сгенерируем случайное число из этой генеральной совокупности. Также рассмотрим функцию ВЕРОЯТНОСТЬ() .

update Опубликовано: 18 июня 2015

Какие элементы у 2-х списков в MS EXCEL отличаются?


Пусть дано 2 списка значений. Определим, какие элементы первого списка НЕ содержатся во втором и какие элементы второго списка НЕ содержатся во первом .

update Опубликовано: 30 июля 2015

Векторное произведение в MS EXCEL


Найдем векторное произведение 2-х векторов с помощью функций MS EXCEL. Также создадим таблицу для проверки векторов на коллинеарность.

update Опубликовано: 19 декабря 2015

Функция ПРОСМОТР() в MS EXCEL


Функция ПРОСМОТР( ) , английский вариант LOOKUP(), похожа на функцию ВПР() : ПРОСМОТР() просматривает левый столбец таблицы и, если находит искомое значение, возвращает значение из соответствующей строки самого правого столбца …

update Опубликовано: 12 января 2014

Убираем в MS EXCEL из текстовой строки числа слева


Пусть текстовая строка содержит буквы и текст. Например, «623Филатова». Уберем числа из текстовой строки и превратим его в просто текст «Филатова».

update Опубликовано: 04 апреля 2013

Номер недели в году в MS EXCEL


Существует 2 способа определения номера недели: в соответствии со стандартом ИСО и с помощью функции НОМНЕДЕЛИ() .

update Опубликовано: 07 апреля 2013

Смешанное произведение векторов в MS EXCEL


Вычислим смешанное произведение векторов в MS EXCEL.

update Опубликовано: 19 декабря 2015

Перестановки с повторениями: Комбинаторика в MS EXCEL


Подсчитаем в MS EXCEL количество перестановок с повторениями из n элементов. С помощью формул выведем на лист все варианты таких перестановок (английский перевод термина: permutations of multisets).

update Опубликовано: 02 февраля 2016

Сочетания с повторениями: Комбинаторика в MS EXCEL


Подсчитаем в MS EXCEL количество Сочетаний с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Сочетаний (английский перевод термина: combinations with …

update Опубликовано: 02 февраля 2016

Первый день месяца в MS EXCEL


Первый день месяца можно определить с помощью функции ДАТА() или КОНМЕСЯЦА() .

update Опубликовано: 07 апреля 2013

Комбинаторика в MS EXCEL


Обзорная статья, в которой приведены основные функции MS EXCEL для вычисления количества перестановок, сочетаний и размещений. Рассмотрены варианты комбинаций без повторений и с повторениями (выборка с возвращением).

update Опубликовано: 01 февраля 2016

Двухвыборочный t-тест с различными дисперсиями в MS EXCEL


Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (дисперсии этих 2-х распределений разные). Вычислим значение тестовой статистики t 0 *, …

update Опубликовано: 11 декабря 2016

Трансформация фигур в двухмерном пространстве (2D Transformation) в MS EXCEL


Трансформация (преобразование) геометрической фигуры означает ее изменение по определенным правилам. Например, вращение, смещение или изменение масштаба некого прямоугольника на плоскости. Правила, по которым происходит изменение, будем записывать в матричном виде. …

update Опубликовано: 17 июля 2017

Строим график функции, заданный системой уравнений, в MS EXCEL


Построим в MS EXCEL график функции, заданный системой уравнений. Эта задача часто встречается в лабораторных работах и почему-то является "камнем преткновения" для многих учащихся.

update Опубликовано: 08 января 2018

Двухфакторный дисперсионный анализ с повторениями в MS EXCEL


Пусть имеется случайная переменная Y, значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от 2-х факторов, значения которых мы можем контролировать, т.е. задавать с требуемой точностью. Покажем …

update Опубликовано: 26 января 2019

Контроль ввода ОШИБочных значений в MS EXCEL. Часть 3 - с применением формул


Создадим формулу для подачи сигнала пользователю при вводе ошибочного значения. Под «ошибочным значением» здесь будем понимать значение, не принадлежащее заданному диапазону.

update Опубликовано: 26 октября 2012

Сравнение графиков погашения кредита дифференцированными и аннуитетными платежами в MS EXCEL


Сравним два вида платежей по кредиту: аннуитетные (заемщик регулярно платит за кредит равными суммами) и дифференцированные (сумма основного долга делится на равные части пропорционально сроку кредитования, проценты начисляются на остаток …

update Опубликовано: 03 февраля 2015

Как в MS EXCEL "Иванов Иван Иванович" превратить в "Иванов И.И."


Превращаем полное ФИО в Фамилию + Инициалы.

update Опубликовано: 04 апреля 2013

Извлекаем в MS EXCEL число из середины текстовой строки


Извлечем число из середины текстовой строки, например, из строки «Пет555ров» получим «555».

update Опубликовано: 04 апреля 2013

Последний день в MS EXCEL на n-й неделе (на 5-й неделе года)


Если известен номер недели, то можно найти, дату последнего дня этой недели.

update Опубликовано: 07 апреля 2013

Аннуитет. Определяем в MS EXCEL Приведенную (Текущую) стоимость


Определим Приведенную (текущую) стоимость будущих доходов (или расходов) в случае аннуитета. Для этого будем использовать функцию ПС() . Также выведем альтернативную формулу для расчета Текущей стоимости.

update Опубликовано: 02 февраля 2015

Функция ЕНД() в MS EXCEL


Функция ЕНД() , английский вариант ISNA(), п роверяет на равенство значению #Н/Д (значение недоступно) и возвращает в зависимости от этого ИСТИНА или ЛОЖЬ.

update Опубликовано: 11 апреля 2013

Пошаговый контроль вычисления в MS EXCEL сложных формул


При написании сложных формул, таких как =ЕСЛИ(СРЗНАЧ(A2:A10)>200;СУММ(B2:B10);0) , часто необходимо получить промежуточный результат вычисления формулы. Для этого есть специальный инструмент Вычислить формулу.

update Опубликовано: 11 апреля 2013

Адрес ячейки в MS EXCEL, содержащей максимальное значение


Найдем адрес ячейки, содержащей максимальное значение в списке.

update Опубликовано: 16 апреля 2013

Сложение значений в MS EXCEL на основании их повторяемости (Часть2.Сложение Неповторяющихся)


Диапазон суммирования содержит повторяющиеся значения. Сложим только те ЧИСЕЛа, которые не имеют повторов.

update Опубликовано: 02 апреля 2013

Последний день месяца в MS EXCEL


Последний день месяца определяется с помощью функции КОНМЕСЯЦА() .

update Опубликовано: 07 апреля 2013

Функция ЧЁТН() в MS EXCEL


Функция ЧЁТН() , в английском варианте EVEN(), округляет до ближайшего четного целого, которое больше исходного значения (округляет вверх до ближайшего четного).

update Опубликовано: 22 мая 2014

День недели в MS EXCEL


Определим день недели для заданной даты в ячейке. Существует несколько решений в зависимости от того результата, который мы хотим получить: номер дня недели (1 - Пн, 2- Вт, ...), текстовую …

update Опубликовано: 07 апреля 2013

В какой день закончится определенный срок (декретный отпуск) в MS EXCEL


Вычислим дату окончания срока, если задана дата начала периода и срок (срок может быть задан в днях, месяцах, годах)

update Опубликовано: 08 апреля 2013

Проверка достижения требуемого возраста в MS EXCEL


Напишем формулу для проверки достижения 20-летнего возраста на сегодняшнюю дату. Формула должна возвращать значение ИСТИНА, если кому-то уже есть полных 20 лет, в противном случае формула должна возвращать значение ЛОЖЬ.

update Опубликовано: 08 апреля 2013

Функция ПОДСТАВИТЬ() в MS EXCEL


Функция ПОДСТАВИТЬ( ) , английский вариант SUBSTITUTE(), заменяет определенный текст в текстовой строке на новое значение. Формула =ПОДСТАВИТЬ(A2; "январь";"февраль") исходную строку "Продажи (январь)" превратит в строку "Продажи (февраль)".

update Опубликовано: 11 апреля 2013

Динамические диаграммы в MS EXCEL. Часть1: Выборка данных через скрытие строк


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

update Опубликовано: 04 октября 2012

Запрос на выборку данных (формулы) в MS EXCEL


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

update Опубликовано: 13 апреля 2013

Нумерация строк и столбцов римскими цифрами в MS EXCEL


Благодаря наличию функции РИМСКОЕ() обычную нумерацию строк можно легко преобразовать в нумерацию римскими цифрами.

update Опубликовано: 18 апреля 2013

Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL


Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине …

update Опубликовано: 16 апреля 2013

Функция ЧИСТРАБДНИ() - подсчет рабочих дней в MS EXCEL


Функция ЧИСТРАБДНИ( ) , английская версия NETWORKDAYS() , возвращает количество рабочих дней между двумя датами, т.е. при подсчете праздники и выходные не учитываются.

update Опубликовано: 08 апреля 2013

Подсчет значений с множественными критериями (Часть 2. Условие ИЛИ) в MS EXCEL


Произведем подсчет строк таблицы, значения которых удовлетворяют сразу двум критериям, которые образуют Условие ИЛИ. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в …

update Опубликовано: 18 апреля 2013

Выпадающий список в MS EXCEL на основе элемента управления формы


Если с помощью Выпадающего (раскрывающегося) списка на основе Проверки данных можно ввести новое значение в ячейку, то с помощью Выпадающего списка на основе элемента управления формы Поле со списком можно …

update Опубликовано: 21 января 2013

Подсчет ТЕКСТовых значений (в диапазонах с ТЕКСТом и ЧИСЛАми) в MS EXCEL


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

update Опубликовано: 18 апреля 2013

Подсчет количества ячеек, содержащих ошибки в MS EXCEL


Бывает, что формулы возвращают ошибки (#ДЕЛ/0!, #Н/Д, #ЗНАЧ! и т.д.) Подсчитаем, количество ячеек, содержащих ошибки.

update Опубликовано: 19 апреля 2013

Сортировка в случайном порядке списка ЧИСЛОвых значений в MS EXCEL


Отсортируем исходный массив ЧИСЕЛ в случайном порядке.

update Опубликовано: 19 апреля 2013

Отбор уникальных ТЕКСТовых значений с учетом РЕгиСТра в MS EXCEL


Имея список с повторяющимися текстовыми значениями, создадим список, состоящий только из уникальных значений. Отбор уникальных значений будем производить с учетом РЕгиСТра.

update Опубликовано: 22 апреля 2013

Последовательности дат и времен в MS EXCEL


Создадим последовательности дат и времен различных видов: 01.01.09, 01.02.09, 01.03.09, ..., янв, апр, июл, ..., пн, вт, ср, ..., 1 кв., 2 кв.,..., 09:00, 10:00, 11:00, ... и пр.

update Опубликовано: 10 апреля 2013

Функция И() в MS EXCEL


Функция И( ) , английский вариант AND(), проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.

update Опубликовано: 11 апреля 2013

Пять последних значений в MS EXCEL


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

update Опубликовано: 16 апреля 2013

Выделение уникальных значений в MS EXCEL


Настроим Условное форматирование для выделения только уникальных (различающихся) значений в столбце.

update Опубликовано: 22 апреля 2013

Описательная статистика в MS EXCEL


Рассмотрим инструмент Описательная статистика, входящий в надстройку Пакет Анализа. Рассчитаем показатели выборки: среднее, медиана, мода, дисперсия, стандартное отклонение и др.

update Опубликовано: 17 ноября 2016

Шрифты для отображения спецсимволов в MS EXCEL


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

update Опубликовано: 03 апреля 2013

Сравнение 2-х списков в MS EXCEL


Сравним 2 списка содержащих ТЕКСТОВЫЕ повторяющиеся значения.

update Опубликовано: 25 апреля 2013

Подсчет Уникальных строк с помощью Расширенного фильтра в MS EXCEL


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

update Опубликовано: 24 апреля 2013

Подсчет Неповторяющихся ЧИСЛОвых значений в многостолбцовом диапазоне в MS EXCEL


Найдем число неповторяющихся числовых значений в заданном многостолбцовом диапазоне.

update Опубликовано: 24 апреля 2013

Преобразование ТЕКСТовых значений в ЧИСЛА (Часть 3. Неразрывный пробел в MS EXCEL)


Копировать ЧИСЛА из WORD в EXCEL приходится не так уж и редко. Чтобы избежать нежелательного переноса разрядов числа на другую строку, в WORD принято разделять разряды неразрывным пробелом (1 234 …

update Опубликовано: 25 апреля 2013

Функция ИНДЕКС() в MS EXCEL


Функция ИНДЕКС( ) , английский вариант INDEX(), возвращает значение из диапазона ячеек по номеру строки и столбца. Например, формула =ИНДЕКС(A9:A12;2) вернет значение из ячейки А10 , т.е. из ячейки расположенной …

update Опубликовано: 29 декабря 2013

Замкнутая кривая в MS EXCEL


Построим замкнутую кривую, соединяющую несколько точек.

update Опубликовано: 29 марта 2015

Подсчет и выделение Уникальных ЧИСЛОвых значений в многостолбцовом диапазоне в MS EXCEL


Найдем число уникальных числовых значений в заданном многостолбцовом диапазоне.

update Опубликовано: 24 апреля 2013

Объединение 2-х списков в MS EXCEL


Иногда для удобства требуется объединить два списка значений в один.

update Опубликовано: 24 апреля 2013

Выбор в MS EXCEL из строки последнего слова


Если строка в ячейке содержит несколько слов, например, «Василий Иванович Петров», то можно создать формулу для вывода последнего слова.

update Опубликовано: 04 апреля 2013

Печать разных групп данных на отдельных страницах MS EXCEL (Промежуточные итоги)


Часто EXCEL используют не только для расчетов и анализа данных, но и для подготовки всевозможных отчетов. Иногда требуется, чтобы данные относящиеся к разным разделам таблицы попадали на разные страницы отчета. …

update Опубликовано: 26 апреля 2013

Округление времени в MS EXCEL с заданной точностью


Округлим время с точностью до 15 минут.

update Опубликовано: 07 апреля 2013

Отображение формата ВРЕМЯ в ТЕКСТовой строке в MS EXCEL


При составлении формул для отображения в ячейке фразы содержащей текст и время, например, «Сейчас 10:35», могут возникнуть сложности с правильным отображением времени. Решим задачу путем предварительного преобразования времени в текстовое …

update Опубликовано: 25 апреля 2013

Склоняем правильно в MS EXCEL - 1 год, 2 года, 5 лет


Учимся корректно записывать число лет: 1 год, 2 года, 5 лет, 11 лет.

update Опубликовано: 07 апреля 2013

Отображение в MS EXCEL текстовой строки в ячейке, содержащей ЧИСЛО


Используя в Формате ячеек символ @, можно отобразить в ячейке, предназначенной для ввода чисел, текстовую строку. На вычисления эта строка не повлияет, т.к. мы будем применять пользовательский формат. Этот подход …

update Опубликовано: 25 апреля 2013

Отображение в MS EXCEL вместо 0 другого символа


Для отображения вместо 0 символа ? (перечеркнутый 0) используем пользовательский формат # ##0,00;-# ##0,00;?.

update Опубликовано: 25 апреля 2013

Округление и точность до 15 значащих цифр в MS EXCEL


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

update Опубликовано: 03 апреля 2013

Сортированный список в MS EXCEL (ЧИСЛОвые значения)


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

update Опубликовано: 22 января 2013

Функция ЯЧЕЙКА() в MS EXCEL


Функция ЯЧЕЙКА( ) , английская версия CELL() , возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях …

update Опубликовано: 16 декабря 2013

MS EXCEL: В какой рабочий день закончится выполнение работы


При планировании, длительность выполнения работ обычно задают в рабочих днях. Дату окончания работы можно рассчитать с помощью функции РАБДЕНЬ() .

update Опубликовано: 08 апреля 2013

Нумеруем дубликаты в MS EXCEL


Имея список с повторяющимися значениями, можно пронумеровать повторы этих значений.

update Опубликовано: 18 апреля 2013

Функция ВПР() в MS EXCEL


Функция ВПР() , английский вариант VLOOKUP(), ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого столбца таблицы.

update Опубликовано: 09 апреля 2013

Связанный список в MS EXCEL


Создадим выпадающий список, содержимое которого зависит от значений другой ячейки.

update Опубликовано: 21 января 2013

Нумерация столбцов буквами в MS EXCEL


Заполним заголовки таблицы буквами в алфавитном порядке.

update Опубликовано: 18 апреля 2013

Чрезстрочное выделение таблиц в MS EXCEL с помощью Условного форматирования


Используем Условное форматирование для выделения каждой второй строки таблицы.

update Опубликовано: 25 апреля 2013

Поле со списком - элемент управления формы в MS EXCEL


Поле со списком представляет собой сочетание текстового поля и раскрывающегося списка. Поле со списком компактнее обычного списка, однако для того чтобы отобразить список элементов, пользователь должен щелкнуть стрелку. Поле со …

update Опубликовано: 10 ноября 2014

Поиск решения MS EXCEL (1.8). Транспортная задача


Создадим модель для решения Транспортной задачи (Transportation Problem, Shipping Routes). Решение Транспортной задачи позволяет определить самые недорогие маршруты для перевозки товаров от производителей на склад. Расчет будем проводить с помощью …

update Опубликовано: 26 марта 2015

Поиск решения MS EXCEL (2.2). EOQ – экономичный размер заказа (с учетом ограничений)


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

update Опубликовано: 26 марта 2015

Функция Гаусса (колоколообразная кривая) в MS EXCEL


Построим график функции Гаусса имеющий форму Колокообразной кривой (Gaussian function, Bell Curve Shape).

update Опубликовано: 02 апреля 2015

Разрезка на мерные длины


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

update Опубликовано: 25 марта 2015

Поиск решения MS EXCEL (4.3). Выбор места открытия нового представительства


Построим модель для принятия решения: компания планирует открыть в нескольких регионах свои представительства, куда будут поступать платежи от ее клиентов. Требуется определить оптимальный вариант открытия представительств, при котором суммарные расходы …

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (6.6). Минимальные затраты транспортной сети


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

update Опубликовано: 28 марта 2015

Квадратное уравнение (строим график функции в MS EXCEL)


Построим график функции y=a*x^2+b*x+с (квадратное уравнение). Также рассчитаем дискриминант, найдем корни уравнения, координаты точки экстремума (максимума или минимума). Сделаем форму для сдвига и отражения графика с помощью элементов управления формы.

update Опубликовано: 01 апреля 2015

Преобразование в MS EXCEL ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами)


Если в ячейке числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. Преобразуем числа, сохраненные как текст, в числовой формат.

update Опубликовано: 04 октября 2012

Выделение ячеек, содержащих и НЕ содержащих формулы в MS EXCEL


Применим средства EXCEL для поиска и выделения ячеек, содержащих и НЕ содержащих формулы.

update Опубликовано: 26 октября 2012

Проверка данных в MS EXCEL


При вводе большого количества информации в ячейки таблицы легко допустить ошибку. В EXCEL существует инструмент для проверки введенных данных сразу после нажатия клавиши ENTER – Проверка данных.

update Опубликовано: 26 октября 2012

Сравнение числовых столбцов


Сравним 2 столбца значений. Сравнение будем производить построчно: если значение во втором столбце больше, чем в первом, то оно будет выделено красным, если меньше - то зеленым. Выделять ячейки будем …

update Опубликовано: 11 октября 2015

Скалярное произведение векторов в MS EXCEL, ортогональные векторы


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

update Опубликовано: 19 декабря 2015

Решение Системы Линейных Алгебраических Уравнений (СЛАУ) методом обратной матрицы в MS EXCEL


Решим Систему Линейных Алгебраических Уравнений (СЛАУ) методом обратной матрицы в MS EXCEL. В этой статье нет теории, объяснено только как выполнить расчеты, используя MS EXCEL.

update Опубликовано: 12 ноября 2015

Отбор значений из заданной строки вне зависимости от фильтра в MS EXCEL


Вывести значение из заданной строки таблицы не представляет труда - используйте функцию ИНДЕКС() . Но, если в таблице действет Автофильтр , то задача усложняется.

update Опубликовано: 29 сентября 2016

Доверительный интервал для оценки среднего (дисперсия известна) в MS EXCEL


Построим в MS EXCEL доверительный интервал для оценки среднего значения распределения в случае известного значения дисперсии.

update Опубликовано: 27 ноября 2016

Проверка статистических гипотез в MS EXCEL о равенстве среднего значения распределения (дисперсия известна)


Рассмотрим использование MS EXCEL при проверке статистических гипотез о среднем значении распределения в случае известной дисперсии. Вычислим тестовую статистику Z 0 , рассмотрим процедуру «одновыборочный z-тест», вычислим Р-значение (Р- value …

update Опубликовано: 27 ноября 2016

Двухвыборочный t-тест с одинаковыми дисперсиями в MS EXCEL


Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (дисперсии этих 2-х распределений одинаковы). Вычислим значение тестовой статистики t 0 , …

update Опубликовано: 11 декабря 2016

Сложение с несколькими критериями в MS EXCEL (Часть 3.Условие ИЛИ)


Произведем сложение значений, которые удовлетворяют хотя бы одному из 2-х критериев (Условие ИЛИ). Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце …

update Опубликовано: 02 апреля 2013

Функция СЧЁТЗ() в MS EXCEL


Подсчитаем количество ячеек содержащих хоть какие-нибудь значения с помощью функции СЧЁТЗ( ) , английская версия COUNTA() . Предполагаем, что диапазон содержит числа, значения в текстовом формате, значения ошибки, пустые ячейки, …

update Опубликовано: 26 мая 2014

Извлекаем в MS EXCEL число из начала текстовой строки


Извлечем число из начала текстовой строки, например, из строки «123Филатова» получим «123».

update Опубликовано: 04 апреля 2013

Поиск решения MS EXCEL (1.2). Распределение ресурсов (ограничение по количеству оборудования)


Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты (Allocation Problem). Расчет будем проводить с помощью надстройки Поиск решения.

update Опубликовано: 24 марта 2015

Скрываем один из графиков на диаграмме в MS EXCEL


Построим на диаграмме 2 графика. С помощью элемента управления Флажок скроем один из графиков.

update Опубликовано: 09 декабря 2013

Число оставшихся дней в году в MS EXCEL


Найдем количество дней, оставшихся до конца года.

update Опубликовано: 07 апреля 2013

Первый вторник месяца в MS EXCEL (обратная задача)


Пусть дана дата 01/03/2011. Напишем формулу для определения - какой по порядку это день недели в данном месяце. Ответ: первый вторник марта.

update Опубликовано: 07 апреля 2013

Динамическая сортировка таблицы в MS EXCEL


Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим …

update Опубликовано: 24 апреля 2013

Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке


Если значение в ячейке удовлетворяет определенному пользователем условию, то с помощью Условного форматирования можно выделить эту ячейку (например, изменить ее фон). В этой статье пойдем дальше - будем выделять всю …

update Опубликовано: 10 октября 2013

Нормальное распределение. Непрерывные распределения в MS EXCEL


Рассмотрим Нормальное распределение. С помощью функции MS EXCEL НОРМ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по нормальному закону, произведем оценку параметров распределения, среднего значения …

update Опубликовано: 23 октября 2016

Сколько осталось дней до конца месяца в MS EXCEL


Найдем количество дней, оставшихся до конца месяца.

update Опубликовано: 11 декабря 2013

Применение инструмента MS EXCEL Консолидация


В этой статье приводится перечень контрольных заданий по теме Консолидация. За основу взяты реальные Контрольные задания, которые  предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по теме MS …

update Опубликовано: 03 июня 2014

Связанный список в MS EXCEL и кавычки в заголовках


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

update Опубликовано: 18 ноября 2014

Удаляем элементы управления вместе со строкой в MS EXCEL


При удалении строки Элементы управления формы (Флажок, Полоса прокрутки, Счетчик) остаются на листе. Если нужно удалять их вместе со строкой, то используйте группировку Элементов управления и/или Фигур.

update Опубликовано: 15 мая 2015

Переворачиваем столбец в MS EXCEL


Научимся с помощью формул переворачивать в MS EXCEL столбец вверх ногами (чтобы данные расположенные в последних строках стали отображаться первыми).

update Опубликовано: 11 марта 2017

Убираем в MS EXCEL из текстовой строки числа справа


Пусть текстовая строка содержит буквы и текст. Например, «Филатова623». Уберем числа из текстовой строки и превратим его в просто текст «Филатова».

update Опубликовано: 04 апреля 2013

Подсчет отработанных месяцев в MS EXCEL


Подсчитаем количество отработанных месяцев в году. Месяц считается отработанным, если человек проработал в нем более 15 рабочих дней.

update Опубликовано: 08 апреля 2013

Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТра) в MS EXCEL


Выделяем ячейки, содержащие искомый текст с учетом РЕгиСТра. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в …

update Опубликовано: 16 апреля 2013

Динамические диаграммы в MS EXCEL. Часть3: Выделение данных на диаграмме цветом


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

update Опубликовано: 03 декабря 2012

Функция ИЛИ() в MS EXCEL


Функция ИЛИ( ) , английский вариант OR(), проверяет на истинность условия и возвращает ИСТИНА если хотя бы одно условие истинно или ЛОЖЬ если все условия ложны.

update Опубликовано: 11 апреля 2013

Функция ЕСЛИ() в MS EXCEL


Функция ЕСЛИ() , английский вариант IF(), используется при проверке условий. Например, =ЕСЛИ(A1>100;"Бюджет превышен";"ОК!") . В зависимости от значения в ячейке А1 результат формулы будет либо "Бюджет превышен" либо "ОК!".

update Опубликовано: 11 апреля 2013

Использование Пакета анализа MS EXCEL для выполнения однофакторного дисперсионного анализа


Выполним однофакторный дисперсионный анализ с помощью надстройки MS EXCEL Пакет анализа .

update Опубликовано: 26 января 2019

Парный двухвыборочный t-тест для средних MS EXCEL


Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (парный тест). Вычислим значение тестовой статистики t 0 , рассмотрим соответствующую процедуру …

update Опубликовано: 11 декабря 2016

Какие элементы у 2-х списков в MS EXCEL общие?


Пусть дано 2 списка значений. Определим, какие элементы первого списка содержатся во втором.

update Опубликовано: 25 апреля 2013

Сложные проценты в MS EXCEL. Переменная ставка


Рассмотрим Сложный процент (Compound Interest) – начисление процентов как на основную сумму долга, так и на начисленные ранее проценты, в случае переменной ставки.

update Опубликовано: 03 февраля 2015

Расчет Эффективной ставки в MS EXCEL


Рассчитаем в MS EXCEL эффективную годовую процентную ставку и эффективную ставку по кредиту.

update Опубликовано: 03 февраля 2015

Двухфакторный дисперсионный анализ без повторений в MS EXCEL


Решим задачу о сравнении средних значений нескольких выборок с использованием дисперсионного анализа в случае двух факторов без повторений (Two Factor ANOVA without Replication). Подход используемый для решения данной задачи имеет …

update Опубликовано: 26 января 2019

Расчет амортизации в MS EXCEL


Рассмотрим способы расчета амортизации с использованием функций MS EXCEL. В этой статье мы будем отталкиваться не от самих функций АПЛ (SLN), АСЧ (SYD), ФУО (DB), ДДОБ (DDB), ПУО (VDB), АМОРУВ …

update Опубликовано: 03 февраля 2015

Изолинии в случае фигуры-плоскости в MS EXCEL


Пусть имеется функция двух переменных Z=f(X;Y). Изолинии (contour line) - это линии, в которой величина Z=const, т.е. изолинии соединяют точки, в которых функция сохраняет одинаковое значение. Часто изолинии используют для …

update Опубликовано: 29 января 2018

Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL


Для подсчета значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ() . Если критерий единственный, то ее функциональности  вполне достаточно для подсчета и текстовых и числовых значений. А возможность …

update Опубликовано: 18 апреля 2013

Имеют ли списки в MS EXCEL общие элементы?


Пусть дано 2 списка значений. Определим, есть ли пересечение этих двух списков (есть ли такие элементы, которые встречаются в обоих списках?)

update Опубликовано: 25 апреля 2013

Типы ссылок MS EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация


В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1*5). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы …

update Опубликовано: 18 ноября 2012

Подбор параметра в MS EXCEL


Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить …

update Опубликовано: 18 ноября 2012

Разделяем пробелами Фамилию, Имя и Отчество


Если фамилия, имя и отчестсво написаны слитно, например, «ПетровИванИванович», то можно создать формулу их разделения, чтобы получить «Петров Иван Иванович» .

update Опубликовано: 09 июля 2014

Восстанавливаем последовательности из списка без повторов в MS EXCEL


В статье Отбор уникальных значений (убираем повторы из списка) в MS EXCEL было показано как из списка с повторами отобрать только уникальные значения. В этой статье покажем как решить обратную …

update Опубликовано: 28 декабря 2017

Отображаем в MS EXCEL в одной ячейке "два значения"


В одной ячейке можно отобразить 2 значения. На самом деле, это конечно будет одно текстовое значение, но визуально выглядит как два.

update Опубликовано: 04 апреля 2013

Нумерация строк в MS EXCEL


Используем различные подходы для нумерации строк.

update Опубликовано: 10 апреля 2013

Поиск решения MS EXCEL (1.4). Распределение ресурсов (ограничение - требование к качеству продукции)


Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к качеству продукции. Расчет будем проводить с помощью …

update Опубликовано: 25 марта 2015

Сколько значений в массиве больше или меньше заданного?


Вычислим в MS EXCEL количество значений в массиве, которые больше (или меньше) заданного числа.

update Опубликовано: 06 января 2016

Подсчет в списках содержащих значение Пустой текст ("") в MS EXCEL


Ячейка, содержащая значение Пустой текст (""), обладает замечательным свойством: ячейка выглядит пустой. К сожалению, значение Пустой текст несколько усложняет подсчет значений.

update Опубликовано: 18 апреля 2013

Функция СТОЛБЕЦ() в MS EXCEL


Задача функции СТОЛБЕЦ( ) , английский вариант COLUMN(), - возвращать номер столбца. Формула = СТОЛБЕЦ(B1) вернет 2, т.к. столбец B - второй столбец на листе.

update Опубликовано: 17 декабря 2013

Функция НД() в MS EXCEL


Функция НД( ) , английский вариант NA(), возвращает значение ошибки #Н/Д. Значение ошибки #Н/Д означает, что значение недоступно. Рассмотрим случаи, когда эта функция может пригодиться.

update Опубликовано: 11 апреля 2013

Динамическое разнесение данных по столбцам


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

update Опубликовано: 19 октября 2014

Спарклайны - отслеживаем тенденции в рядах данных в MS EXCEL


Тенденции в рядах значений (например, колебания цен, объемов продаж) можно отслеживать в EXCEL с помощью диаграмм или условного форматирования . В этой статье рассмотрим спарклайны (sparklines), которые появились в MS …

update Опубликовано: 13 ноября 2014

Аннуитет. Расчёт в MS EXCEL погашение основной суммы долга


Рассчитаем в MS EXCEL остаток основной суммы долга, который требуется погасить после заданного количества периодов. Выплата кредита производится равными ежемесячными платежами (аннуитетная схема). Процентная ставка и величина платежа - известны, …

update Опубликовано: 02 февраля 2015

Автоматическая сетевая диаграмма проекта с критическим путем в MS EXCEL


Построим автоматическую сетевую диаграмму проекта. Сетевую диаграмму изобразим на диаграмме MS EXCEL типа Точечная. На этой диаграмме выведем работы проекта в виде точек, стрелками изобразим связи между работами. Также изобразим …

update Опубликовано: 11 января 2018

Динамический диапазон в MS EXCEL


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

update Опубликовано: 21 января 2013

Дисперсия и стандартное отклонение в MS EXCEL


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

update Опубликовано: 04 октября 2016

Проблемы округления в MS EXCEL


Выясняем, к чему может привести тот факт, что результат вычисления формулы =1,324-1,319 равен 0,00500000000000012, а не 0,005.

update Опубликовано: 03 апреля 2013

Выделение Условным форматированием в MS EXCEL Чисел принадлежащих различным диапазонам


Используем Условное форматирование для выделения строк таблицы, в которых числа принадлежат к определенному диапазону. Например, если число в определенном столбце таблицы меньше 0, то вся строка будет выделена красным.

update Опубликовано: 02 апреля 2014

Склоняем правильно время в MS EXCEL - 1 сутки 2 часа; 3 суток 6 часов


Учимся корректно склонять сутки и часы: 1 сутки 2 часа; 3 суток 6 часов.

update Опубликовано: 04 апреля 2014

Функция ЗНАЧЕН() в MS EXCEL


Функция ЗНАЧЕН( ) , английский вариант VALUE(), преобразует строку текста в число (там где это возможно).

update Опубликовано: 28 мая 2014

Расчет Приведенной (настоящей, текущей) стоимости в MS EXCEL


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

update Опубликовано: 03 февраля 2015

Объединение нескольких списков в MS EXCEL (без формул массива))


Объеденим значения, расположенные в нескольких столбцах, в один столбец.

update Опубликовано: 28 мая 2015

Построение в MS EXCEL диаграммы типа "Термометр"


Построим диаграмму типа термометр.

update Опубликовано: 03 декабря 2012

Простейшая диаграмма Ганта в MS EXCEL


Построим диаграмму, напоминающую диаграмму Ганта.

update Опубликовано: 03 декабря 2012

Выделение в MS EXCEL незаполненных ячеек


Используем Условное форматирование для выделения на листе MS EXCEL незаполненных пользователем ячеек.

update Опубликовано: 05 декабря 2012

Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону


Используем Условное форматирование для подачи сигнала пользователю MS EXCEL о принадлежности даты к определенному диапазону.

update Опубликовано: 05 декабря 2012

Вычисление в MS EXCEL среднего по условию (один ТЕКСТовый критерий)


Найдем среднее всех ячеек, значения которых соответствуют определенному условию. Для этой цели в MS EXCEL существует простая и эффективная функция СРЗНАЧЕСЛИ() , которая впервые появилась в EXCEL 2007. Рассмотрим случай, …

update Опубликовано: 05 января 2013

Функция СРЗНАЧ() - Среднее арифметическое в MS EXCEL


Функция СРЗНАЧ( ) , английский вариант AVERAGE(), возвращает среднее арифметическое своих аргументов. Также рассмотрена функция СРЗНАЧА( ) , английский вариант AVERAGEA()

update Опубликовано: 05 января 2013

Именованный диапазон в MS EXCEL


Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.

update Опубликовано: 21 января 2013

Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список


Найдем в таблице наибольшую дату, которая меньше или равна заданной (ближайшая снизу). Причем из всех дат в таблице будем учитывать только те, которые относятся к определенному товару (условие). Список может …

update Опубликовано: 22 марта 2015

update Опубликовано: 19 ноября 2016

Ранг и Персентиль в надстройке Пакет анализа MS EXCEL


Рассмотрим инструмент Ранг и Персентиль надстройки Пакет анализа.

update Опубликовано: 19 ноября 2016

Вложенный связанный список в MS EXCEL


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

update Опубликовано: 21 января 2013

Разнесение в MS EXCEL текстовых строк по столбцам


Разнесем Фамилию, Имя и Отчество (ФИО), содержащихся в одной ячейке, по разным столбцам.

update Опубликовано: 04 апреля 2013

Отчеты в MS EXCEL


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

update Опубликовано: 21 января 2013

Сортированный список в MS EXCEL (ТЕКСТовые значения)


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

update Опубликовано: 21 февраля 2013

Формулы массива в MS EXCEL, возвращающие несколько значений


Формулы массива могут возвращать как отдельное значение, так и несколько значений. В первом случае для отображения результата потребуется одна ячейка, во втором – диапазон. В этой статье рассмотрим формулы массива, …

update Опубликовано: 31 марта 2013

Пользовательский ЧИСЛОвой формат в MS EXCEL (Функция ТЕКСТ())


Числовой пользовательский формат – это формат отображения числа задаваемый пользователем. Например, число 5647,22 можно отобразить как 005647 или, вообще в произвольном формате, например, +(5647)руб.22коп. Пользовательские форматы также можно использовать в …

update Опубликовано: 31 марта 2013

Пользовательский формат ДАТЫ и ВРЕМЕНИ в MS EXCEL


Пользовательский формат – это формат отображения значения задаваемый пользователем. Например, дату 13/01/2010 можно отобразить как: 13.01.2010 или 2010_01_13 или 13-Январь-10 .

update Опубликовано: 31 марта 2013

Текст-по-столбцам (мастер текстов) в MS EXCEL


Часто необходимо «разнести» текстовою строку из одной ячейки по нескольким. Это может быть или полное имя: «Иванов Иван Иванович», либо адрес «г.Москва, Северный бульвар, д.133», либо паспортные данные. Используйте для …

update Опубликовано: 31 марта 2013

Отбор повторяющихся значений в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Надстройка Пакет анализа MS EXCEL


Использование надстройки «Пакет анализа», поможет упростить расчеты при проведении статистического или инженерного анали за.

update Опубликовано: 13 октября 2016

Функция СУММ() и операция сложения (+) в MS EXCEL


Есть ли разница между сложением значений с помощью функции СУММ( ) , английская версия SUM(), и операцией сложения (+)? Оказывается разница есть.

update Опубликовано: 02 апреля 2013

Сложение значений в MS EXCEL в каждой третьей строке


Найдем сумму значений, расположенных в каждой третьей строке, используя функции БДСУММ() и СУММПРОИЗВ() .

update Опубликовано: 02 апреля 2013

Сумма трех наибольших значений в MS EXCEL


Сложим три (четыре, пять и т.д.) наибольших значений из диапазона.

update Опубликовано: 03 апреля 2013

Функция СУММПРОИЗВ() - Сложение и подсчет с множественными условиями в MS EXCEL


Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе …

update Опубликовано: 03 апреля 2013

Извлекаем в MS EXCEL число из конца текстовой строки


Извлечем число из конца текстовой строки, например, из строки «Филатова123» получим «123».

update Опубликовано: 04 апреля 2013

Поиск решения MS EXCEL (1.1). Оптимальная структура выпускаемой продукции


Создадим модель для определения Оптимальной структуры выпускаемой продукции (Product Mix), при котором выручка максимальна. Расчет будем проводить с помощью надстройки Поиск решения. За основу возьмем пример из файла solvsamp.xls, поставляемый …

update Опубликовано: 24 марта 2015

Мода в MS EXCEL


Для вычисления моды выборки в MS EXCEL существует специальная функция МОДА() . Вычислим моду для заданного распределения случайной величины.

update Опубликовано: 22 октября 2016

Медиана в MS EXCEL


Для вычисления медианы в MS EXCEL существует специальная функция МЕДИАНА() . В этой статье дадим определение медианы и научимся вычислять ее для выборки и для заданного закона распределения случайной величины.

update Опубликовано: 23 октября 2016

Равномерное дискретное распределение в MS EXCEL


Рассмотрим Равномерное дискретное распределение, построим график функции распределения, вычислим среднее значение и дисперсию. Сгенерируем случайные значения (выборку) с помощью функции MS EXCEL СЛУЧМЕЖДУ() . На основании выборки оценим среднее и …

update Опубликовано: 27 октября 2016

Числовые последовательности в MS EXCEL (порядковые номера 1,2,3... и др.)


Создадим числовые последовательности различных видов: 1, 2, 3, ... 1, 3, 5, 7, ... I, II, III, IV, .... 100, 200, 300,...00-01 , 00-02, ... 1, 1, 1, 2, 2, …

update Опубликовано: 10 апреля 2013

Поиск решения MS EXCEL (1.6). Распределение ресурсов (ограничение – параметры заказа)


Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к параметрам заказа (количество каждого вида продукции и …

update Опубликовано: 25 марта 2015

Поиск решения MS EXCEL (1.7). Разрезка лент на части


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

update Опубликовано: 25 марта 2015

Взаимосвязь некоторых распределений в MS EXCEL


Рассмотрим взаимосвязь Биномиального распределения, распределения Пуассона, Нормального распределения и Гипергеометрического распределения. Определим условия, когда возможна аппроксимация одного распределения другим, приведем примеры и графики.

update Опубликовано: 06 ноября 2016

Распределение Пуассона. Дискретные распределения в MS EXCEL


Рассмотрим распределение Пуассона, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ПУАССОН.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения, его математического ожидания и …

update Опубликовано: 06 ноября 2016

Равномерное непрерывное распределение в MS EXCEL


Рассмотрим равномерное непрерывное распределение. Вычислим математическое ожидание и дисперсию. Сгенерируем случайные значения с помощью функции MS EXCEL СЛЧИС() и надстройки Пакет Анализа, произведем оценку среднего значения и стандартного отклонения.

update Опубликовано: 08 ноября 2016

Поиск в MS EXCEL текстового значения первого или последнего по алфавиту


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

update Опубликовано: 07 апреля 2013

Функция ТРАНСП() - Транспонирование диапазонов ячеек в MS EXCEL


Функция ТРАНСП() , в анлийском варианте TRANSPOSE(), преобразует вертикальный диапазон ячеек в горизонтальный и наоборот. Научимся транспонировать (поворачивать) столбцы, строки и диапазоны значений.

update Опубликовано: 09 апреля 2013

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск


Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в …

update Опубликовано: 14 апреля 2013

Является ли в MS EXCEL значение ДАТОЙ


Попробуем преобразовать заданное значение в дату. Если это удастся, то будем считать значение датой.

update Опубликовано: 25 апреля 2013

Поиск решения MS EXCEL (3.3). Минимизация оставшегося места на диске (задача о сумме подмножеств)


Создадим модель для решения прикладной задачи: разместить файлы на DVD оптимальным образом, чтобы осталось как можно меньше пустого места. Эта задача является одной из разновидностей задачи о рюкзаке. Расчет будем …

update Опубликовано: 27 марта 2015

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки


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

update Опубликовано: 14 апреля 2013

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL


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

update Опубликовано: 23 апреля 2013

Функция ЕСЛИОШИБКА() в MS EXCEL


Функция ЕСЛИОШИБКА() , английский вариант IFERROR(), п роверяет выражение на равенство значениям #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! Если проверяемое выражение или значение в ячейке содержит ошибку, то …

update Опубликовано: 11 апреля 2013

Экспоненциальное распределение. Непрерывные распределения в MS EXCEL


Рассмотрим Экспоненциальное распределение, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ЭКСП.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметра …

update Опубликовано: 08 ноября 2016

Гамма распределение. Непрерывные распределения в MS EXCEL


Рассмотрим Гамма распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ГАММА.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров …

update Опубликовано: 09 ноября 2016

Сравнение реальных списков в MS EXCEL


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

update Опубликовано: 25 апреля 2013

Бета распределение. Непрерывные распределения в MS EXCEL


Рассмотрим Бета-распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БЕТА.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров распределения.

update Опубликовано: 10 ноября 2016

Функция НОМНЕДЕЛИ() в MS EXCEL


Функция НОМНЕДЕЛИ( ) , английский вариант WEEKNUM(), возвращает число, которое указывает, на какую неделю года приходится указанная дата.

update Опубликовано: 12 апреля 2013

О классификации задач Поиска в EXCEL


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

update Опубликовано: 13 апреля 2013

Значение в MS EXCEL на пересечении строки и столбца


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

update Опубликовано: 14 апреля 2013

Доверительный интервал для разницы средних значений 2-х распределений (дисперсии неизвестны, но равны) в MS EXCEL


Построим доверительный интервал для разницы средних значений 2-х распределений в случае неизвестных, но равных дисперсий.

update Опубликовано: 09 декабря 2016

Доверительный интервал для разницы средних значений 2-х распределений (дисперсии неизвестны и не равны) в MS EXCEL


Построим доверительный интервал для разницы средних значений 2-х распределений в случае неизвестных и неравных дисперсий.

update Опубликовано: 10 декабря 2016

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца


Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие …

update Опубликовано: 14 апреля 2013

Выделение пяти последних заполненных ячеек в MS EXCEL


Выделим диапазон, содержащий 5 последних заполненных ячеек в списке. Если столбец со значениями постоянно заполняется, то эта задача перестает быть тривиальной.

update Опубликовано: 16 апреля 2013

Поиск в MS EXCEL третьего (n-го) повтора в списках с ЧИСЛАми


Рассмотрим поиск чисел в списке с повторами. Задав в качестве критерия для поиска нужное значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а …

update Опубликовано: 17 апреля 2013

Автоматическая нумерация при добавлении строк в MS EXCEL


При добавлении в таблицу новых строк приходится вручную восстанавливать нумерацию строк. Использование таблиц в формате Excel 2007 позволяет автоматизировать этот процесс.

update Опубликовано: 18 апреля 2013

Автоматическая перенумерация строк при применении фильтра в MS EXCEL


При применении фильтра строки скрываются целиком. Если в таблице имелась нумерация строк, то она будет сбита. Но, есть способ обойти эту проблему.

update Опубликовано: 18 апреля 2013

Подсчет значений с множественными критериями (Часть 4. Два Условия И) в MS EXCEL


Произведем подсчет значений, удовлетворяющих сразу трем критериям, которые образуют 2 Условия И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты …

update Опубликовано: 18 апреля 2013

Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения


Рассмотрим использование MS EXCEL при проверке статистических гипотез о дисперсии нормального распределения. Вычислим тестовую статистику χ 2 и Р-значение (Р- value ).

update Опубликовано: 11 декабря 2016

Двухвыборочный z-тест для средних в MS EXCEL


Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае известных дисперсий. Вычислим значение тестовой статистики Z 0 , рассмотрим процедуру «двухвыборочный z-тест», вычислим …

update Опубликовано: 11 декабря 2016

Проверка сложных гипотез критерием хи-квадрат Пирсона в MS EXCEL


Рассмотрим применение в MS EXCEL критерия хи-квадрат Пирсона для проверки сложных гипотез.

update Опубликовано: 13 декабря 2016

Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL


Произведем подсчет строк, удовлетворяющих сразу двум критериям, которые образуют Условие И.

update Опубликовано: 18 апреля 2013

Функция СЛУЧМЕЖДУ() - Случайное число из заданного интервала в MS EXCEL


Ф ункция СЛУЧМЕЖДУ( ) , английский вариант RANDBETWEEN(), возвращает случайное ЦЕЛОЕ число в заданном интервале.

update Опубликовано: 19 апреля 2013

Гистограмма в MS EXCEL с накоплением


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

update Опубликовано: 17 октября 2013

ABC-анализ с помощью надстройки MS EXCEL ABC Analysis Tool


Выполним ABC-анализ для определения ключевых клиентов и ранжирования номенклатуры товаров, используя надстройку MS EXCEL Fincontrollex® ABC Analysis Tool.

update Опубликовано: 07 мая 2018

Поиск позиции предпоследней заполненной ячейке в MS EXCEL


Найдем значение и позицию предпоследней заполненной ячейке в диапазоне ячеек MS EXCEL.

update Опубликовано: 05 августа 2018

Проверка значимости регрессии с помощью дисперсионного анализа (F-тест)


Проведем проверку значимости простой линейной регрессии с помощью процедуры F -тест.

update Опубликовано: 26 января 2019

Сортировка в случайном порядке списка ТЕКСТовых значений в MS EXCEL


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

update Опубликовано: 19 апреля 2013

Выбор 2-х случайных значений из списка в MS EXCEL


Выберем 2 случайных значения из списка значений. Также получим формулу для выбора 1 случайного значения.

update Опубликовано: 19 апреля 2013

Создание таблиц с неповторяющимися строками в MS EXCEL


Создадим таблицу, состоящую только из неповторяющихся строк.

update Опубликовано: 20 апреля 2013

Классификация значений по повторяемости в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам


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

update Опубликовано: 24 апреля 2013

Промежуточные итоги в MS EXCEL


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

update Опубликовано: 20 октября 2013

Детерминированный факторный анализ с помощью надстройки MS EXCEL Variance Analysis Tool


Выполним детерминированный факторный анализ на примере модели, описывающей связь финансовых показателей предприятия. Рассмотрим наиболее общий способ цепных подстановок. Для проведения факторного анализа используем надстройку MS EXCEL Variance Analysis Tool от …

update Опубликовано: 20 февраля 2018

Аннуитет. Расчет периодического платежа в MS EXCEL. Срочный вклад


Рассчитаем в MS EXCEL сумму регулярного платежа в случае накопления определенной суммы. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу регулярных …

update Опубликовано: 02 февраля 2015

Распределение ХИ-квадрат. Распределения математической статистики в MS EXCEL


Рассмотрим Распределение ХИ-квадрат. С помощью функции MS EXCEL ХИ2.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.

update Опубликовано: 12 ноября 2016

Преобразование в MS EXCEL ТЕКСТовых значений в ДАТУ


Бывает, что при экспорте значений в EXCEL , даты записываются в незнакомом для EXCEL формате, например 20081223 (т.е. 2008г, 23 декабря). Для дальнейшей работы с такими датами выполним преобразование в …

update Опубликовано: 25 апреля 2013

Отображение в MS EXCEL формата ДАТЫ в ТЕКСТовой строке


При составлении формул для отображения в ячейке фразы содержащей текст и дату, например, «Сегодня 02.10.10», можно получить вот такой результат: «Сегодня 40453», т.е. дата будет отражена в числовом виде. Решим …

update Опубликовано: 25 апреля 2013

Скрытие значений в MS EXCEL равных 0


Иногда требуется скрыть значения равные 0. Сделаем это несколькими способами.

update Опубликовано: 25 апреля 2013

Аннуитет. Расчет в MS EXCEL количества периодов


Рассчитаем в MS EXCEL сколько времени потребуется для погашения кредита в случае равных ежемесячных платежей (по аннуитетной схеме). Процентная ставка и величина платежа - известны, начисление процентов за пользование кредитом …

update Опубликовано: 02 февраля 2015

Аннуитет. Определяем процентную ставку в MS EXCEL


Пусть известна сумма и срок кредита, а также величина регулярного аннуитетного платежа. Рассчитаем в MS EXCEL под какую процентную ставку нужно взять этот кредит, чтобы полностью его погасить за заданный …

update Опубликовано: 02 февраля 2015

Аннуитет. Определяем в MS EXCEL Будущую Стоимость


Определим Будущую стоимость инвестиции в случае аннуитета. Под инвестицией будем понимать как регулярные взносы, так и начальный взнос. Для этого будем использовать функцию БС() . Также выведем альтернативную формулу для …

update Опубликовано: 02 февраля 2015

Скрытие в MS EXCEL ошибки в ячейке


Иногда требуется скрыть в ячейке значения ошибки: #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? Сделаем это несколькими способами.

update Опубликовано: 25 апреля 2013

Применение в MS EXCEL cводных таблиц


В этой статье приводится перечень контрольных заданий по теме Сводные таблицы в MS EXCEL. За основу взяты реальные Контрольные задания, которые  предлагается решить в ВУЗах, техникумах, школах и других обучающих …

update Опубликовано: 21 октября 2013

Функция БИЗВЛЕЧЬ() в MS EXCEL


Функция БИЗВЛЕЧЬ() , английский вариант DGET(), извлекает из столбца (списка) или таблицы отдельное значение, удовлетворяющее заданным условиям.

update Опубликовано: 21 октября 2013

Функция ДМАКС() - нахождение максимального значения по условию в MS EXCEL


Функция ДМАКС() , английский вариант DMAX(), находит в столбце максимальное значение, удовлетворяющее заданным условиям.

update Опубликовано: 09 декабря 2013

Функция ДМИН() - нахождение минимального значения по условию в MS EXCEL


Функция ДМИН() , английский вариант DMIN(), находит в столбце минимальное значение, удовлетворяющее заданным условиям.

update Опубликовано: 09 декабря 2013

Функция КОДСИМВ() в MS EXCEL


Функция КОДСИМВ( ) , английский вариант CODE(), возвращает числовой код первого знака в текстовой строке.

update Опубликовано: 29 мая 2014

Расчет Будущей стоимости в MS EXCEL


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

update Опубликовано: 03 февраля 2015

Гистограмма распределения в MS EXCEL


Гистограмма распределения - это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и …

update Опубликовано: 15 ноября 2016

Использование Пакета анализа MS EXCEL для построения множественной линейной регрессионной модели


Проведем множественный регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .

update Опубликовано: 26 января 2019

Подсчет максимального количества подряд идущих значений в MS EXCEL


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

update Опубликовано: 21 мая 2014

Прибавляем месяцы к дате в MS EXCEL


Найдем дату, отстоящую от начальной даты на заданное количество месяцев.

update Опубликовано: 19 июня 2014

Выделение строк, относящихся к разным месяцам


С помощью Условного форматирования можно выделить данные, принадлежащие разным месяцам.

update Опубликовано: 08 ноября 2014

Флажок - элемент управления формы в MS EXCEL


Флажок возвращает ИСТИНА (если Флажок установлен) или ЛОЖЬ (если Флажок снят).

update Опубликовано: 09 ноября 2014

Чистая приведенная стоимость NPV (ЧПС) и внутренняя ставка доходности IRR (ВСД) в MS EXCEL


Рассчитаем Чистую приведенную стоимость и Внутреннюю норму доходности с помощью формул MS EXCEL.

update Опубликовано: 03 февраля 2015

Распределение Фишера (F-распределение). Распределения математической статистики в MS EXCEL


Рассмотрим распределение Фишера (F-распределение). С помощью функции MS EXCEL F .РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.

update Опубликовано: 12 ноября 2016

Использование Пакета анализа MS EXCEL для выполнения двухфакторного дисперсионного анализа (без повторений)


Выполним двухфакторный дисперсионный анализ без повторений с помощью надстройки MS EXCEL Пакет анализа .

update Опубликовано: 26 января 2019

Использование Пакета анализа MS EXCEL для выполнения двухфакторного дисперсионного анализа (с повторениями)


Выполним двухфакторный дисперсионный анализ с повторениями с помощью надстройки MS EXCEL Пакет анализа .

update Опубликовано: 26 января 2019

Справочник с отчетом в MS EXCEL


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

update Опубликовано: 02 марта 2019

Вычисляемое поле в Сводных таблицах в MS Excel


Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.

update Опубликовано: 02 марта 2019

Подсчет подряд идущих значений в MS EXCEL


Подсчитаем количество подряд идущих значений в столбце. Рассмотрим неповторяющиеся серии значений (111222234444...) и повторяющиеся (110111000010 и 110221010112).

update Опубликовано: 22 мая 2014

Функция СЧЁТЕСЛИМН() в MS EXCEL


Функция СЧЁТЕСЛИМН( ) , английская версия COUNTIFS() , предназначена для подсчета строк, поля которых удовлетворяют двум критериям и больше.

update Опубликовано: 21 апреля 2014

Функция НЕЧЁТ() в MS EXCEL


Функция НЕЧЁТ( ) , английский вариант ISODD(), округляет до ближайшего нечетного целого, которое больше исходного значения (округляет вверх до ближайшего нечетного).

update Опубликовано: 22 мая 2014

Функция МИН() в MS EXCEL


Найдем с помощью функции МИН( ) , английский вариант MIN(), минимальное значение в списке аргументов. Предполагаем, что диапазон может содержать числа, числовые значения в текстовом формате, значения ошибки, пустые ячейки.

update Опубликовано: 26 мая 2014

Функция МАКС() в MS EXCEL


Найдем с помощью функции МАКС( ) , английский вариант MAX(), максимальное значение в списке аргументов. Предполагаем, что диапазон может содержать числа, числовые значения в текстовом формате, значения ошибки, пустые ячейки.

update Опубликовано: 26 мая 2014

Функция НАИБОЛЬШИЙ() в MS EXCEL


Функция НАИБОЛЬШИЙ( ) , английский вариант LARGE(), возвращает k-ое по величине значение из массива данных. Например, формула =НАИБОЛЬШИЙ(A2:B6;1) вернет максимальное значение (первое наибольшее) из диапазона A2:B6 .

update Опубликовано: 26 мая 2014

Блочная диаграмма в MS EXCEL


Блочная диаграмма (диаграмма размаха, ящик с усами) является наглядным и компактным способом представить распределение данных в выборке.

update Опубликовано: 22 ноября 2016

Функция РАНГ() в MS EXCEL


Функция РАНГ( ) , английский вариант RANK(), возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 …

update Опубликовано: 27 мая 2014

Функция СЧИТАТЬПУСТОТЫ() в MS EXCEL


Функция СЧИТАТЬПУСТОТЫ( ) , английская версия COUNTBLANK() , подсчитывает количество пустых ячеек в заданном диапазоне. Также она подсчитывает ячейки с формулами, результатом вычисления которых является значение Пустой текст "" (например, …

update Опубликовано: 27 мая 2014

Квартили и интерквартильный интервал (IQR) в MS EXCEL


Для вычисления квартилей в MS EXCEL существует специальная функция КВАРТИЛЬ() . В этой статье дадим определение квартилей и научимся их вычислять для выборки и для непрерывного распределения. Также вычислим интерквартильный …

update Опубликовано: 20 ноября 2016

Функция ПРОПНАЧ() в MS EXCEL


Функция ПРОПНАЧ( ) , английский вариант PROPER(), делает первую букву в тексте ПРОПИСНОЙ (ЗАГЛАВНОЙ), например =ПРОПНАЧ("ааа") вернет "Ааа". =ПРОПНАЧ("ааа аа") вернет "Ааа Аа". В статье также показано как из "ааа …

update Опубликовано: 29 мая 2014

Одномерные массивы MS EXCEL


В этой статье приводится контрольное задание по теме Одномерный массив MS EXCEL. За основу взято реальное Контрольное задание, которое предлагается решить по информатике студентам обучающимся на инженера-строителя. Желающие могут самостоятельно …

update Опубликовано: 29 мая 2014

Расчет параметров кредитов (процент, выплаты, сроки, сумма) в MS EXCEL


В этой статье приводится примеры расчета параметров кредита: процентных ставок, сумм ежемесячных выплат, сроков и т.д. За основу взяты реальные Контрольные задания, которые  предлагается решить в ВУЗах, техникумах, школах и …

update Опубликовано: 03 июня 2014

Сравнение 2-х таблиц в MS EXCEL


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

update Опубликовано: 30 июля 2014

Счетчик - элемент управления формы в MS EXCEL


Элемент Счетчик позволяет изменять значения в определенном диапазоне с определенным шагом (1, 2, 3, ...). По умолчанию диапазон изменения значений определен от 0 до 30000, шаг =1.

update Опубликовано: 09 ноября 2014

Сравнение отчетов за месяц (отображение изменений по столбцам в MS EXCEL)


Показатели деятельности компании часто контролируются на периодической основе, что порождает соответствующие отчеты. Например, объем продаж по различным группам товаров ежемесячно вносится в таблицу. Для быстрого анализа изменений, произошедших за последний …

update Опубликовано: 12 ноября 2014

Отбор уникальных значений в MS EXCEL с условиями


Продолжим идеи, изложенные в статье Отбор уникальных значений в MS EXCEL . Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем только уникальные …

update Опубликовано: 13 января 2015

Проверка распределения на нормальность в MS EXCEL


Построение графика проверки распределения на нормальность ( Normal Probability Plot ) является графическим методом определения соответствия значений выборки нормальному распределению.

update Опубликовано: 22 ноября 2016

Поиск решения MS EXCEL (2.3). Оптимальное размещение рекламы


Создадим модель для оптимального выбора канала распространения рекламы. Расчет будем проводить с помощью надстройки Поиск решения.

update Опубликовано: 26 марта 2015

Поиск решения MS EXCEL (2.4). Оптимизация затрат на приобретение и транспортировку товаров


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

update Опубликовано: 26 марта 2015

Поиск решения MS EXCEL (3.1). Оптимизация транспортных затрат


Создадим модель для оптимизации транспортных затрат. Расчет будем проводить с помощью надстройки Поиск решения.

update Опубликовано: 26 марта 2015

Поиск решения MS EXCEL (4.1). Управление оборотным капиталом


Управление капиталом — модель для поиска схемы получения максимальной прибыли при краткосрочных и долгосрочных вложениях. Расчет будем проводить с помощью надстройки Поиск решения. За основу возьмем пример из файла solvsamp.xls, …

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (4.2). Выбор варианта инвестирования


Компания имеет на выбор 6 проектов с различным уровнем прибыльности и инвестиций. Определить оптимальный вариант инвестирования, при котором суммарный доход от проектов будет максимальным. Расчет будем проводить с помощью надстройки …

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (6.2). Задача коммивояжера (полный граф, нелинейная модель)


Решим один из вариантов задачи коммивояжера для небольшого количества городов (5-10) с помощью надстройки Поиск решения. Построим нелинейную модель.

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (6.3). Задача коммивояжера (полный граф, линейная модель)


Решим один из вариантов задачи коммивояжера для небольшого количества городов (5-10) с помощью надстройки Поиск решения. Построим линейную модель.

update Опубликовано: 27 марта 2015

Поиск последнего повтора в MS EXCEL


Рассмотрим таблицу продаж, состоящую из столбцов Дата продажи и Сумма. Т.к. в день может быть несколько продаж, то столбец с датами содержит повторы. Задав в качестве критерия поиска дату, найдем …

update Опубликовано: 18 апреля 2013

Поиск решения MS EXCEL (4.4). Открывать завод или нет?


Компания перевозит изделия со своих заводов в складские комплексы. Общая мощность заводов достаточна для удовлетворения существующего спроса, однако уровень транспортных затрат слишком высок. Желая оптимизировать расходы на транспортировку, Компания рассматривает …

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (5.2). График занятости с учетом предпочтений сотрудников


Компания распределяет новых сотрудников по офисам. Каждый сотрудник высказал свое предпочтение по каждому из офисов. Необходимо распределить сотрудников по офисам так, чтобы было как можно меньше недовольных. Расчет будем проводить …

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (5.3). График занятости. Распределение по сменам


Рабочие распределяются по сменам. Каждая смена длится 1 неделю, в которой 5 рабочих дней и 2 выходных. Необходимо обеспечить, определенное количество рабочих каждый день недели. Определить минимальное количество рабочих. Расчет …

update Опубликовано: 27 марта 2015

Основы построения диаграмм в MS EXCEL


Рассмотрим основы создания и настройки диаграмм в MS EXCEL 2010. Материал статьи также будет полезен пользователям MS EXCEL 2007 и более ранних версий. Здесь мы не будем рассматривать типы диаграмм …

update Опубликовано: 16 мая 2015

Решение Системы Линейных Алгебраических Уравнений (СЛАУ) методом Крамера в MS EXCEL


Решим Систему Линейных Алгебраических Уравнений (СЛАУ) методом Крамера в MS EXCEL. В этой статье нет теории, объяснено только как выполнить расчеты, используя MS EXCEL.

update Опубликовано: 10 ноября 2015

Комбинации элементов из нескольких множеств: Комбинаторика в MS EXCEL


Пусть имеется несколько множеств — {A1, A2, A3, А4 ...}, {B1, B2, B3, ...}, {C1, C2, ...}, количество элементов в которых может быть различно. Требуется составить все возможные комбинации элементов …

update Опубликовано: 02 февраля 2016

Вывод итогов столбцов по строкам в MS EXCEL


Пусть имеется 5 столбцов с данными. В каждом столбце по 10 чисел, необходимо найти сумму чисел в каждом столбце. Обычно итоговое значение выводится внизу столбца или над его заголовком, поэтому …

update Опубликовано: 21 мая 2016

Среднее и Математическое ожидание в MS EXCEL


Вычислим среднее значение выборки и математическое ожидание случайной величины в MS EXCEL.

update Опубликовано: 04 октября 2016

Проверка статистических гипотез в MS EXCEL


Здесь даны ссылки на статьи о проверке статистических гипотез о среднем значении и дисперсии распределения. Также рассмотрены двухвыборочный z -тест, t -тест и F -тест.

update Опубликовано: 26 ноября 2016

Доверительный интервал для оценки среднего (дисперсия неизвестна) в MS EXCEL


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

update Опубликовано: 27 ноября 2016

Разбор текстовых строк в MS EXCEL


Часто текстовая строка может содержать несколько значений. Например, адрес компании: "г.Москва, ул.Тверская, д.13", т.е. название города, улицы и номер дома. Если необходимо определить все компании в определенном городе, то нужно …

update Опубликовано: 18 ноября 2017

Вращение фигур в трехмерном пространстве (3D Rotation) в MS EXCEL


Научимся вращать в MS EXCEL трехмерные фигуры вокруг координатных осей Х, Y, Z, а также поворачивать плоскости вокруг произвольно заданной оси. Для этого используем соответствующие матрицы вращения. Также покажем, что …

update Опубликовано: 21 июля 2017

Оптимизация графика работ по проекту с помощью Поиска решений в MS EXCEL


Решим задачу об оптимизации плана-графика работ по проекту с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника "Методы оптимизации управления и принятия решений" авторы Зайцев …

update Опубликовано: 07 ноября 2017

Подписи для точечной диаграммы в MS EXCEL


В качестве подписей данных в точечной диаграмме (XY Scatter) можно установить: имя ряда, значения Х и значения Y. Однако, часто требуется присвоить индивидуальную подпись для каждой точки. Если у Вас …

update Опубликовано: 19 ноября 2017

Сложение значений в зависимости от цвета ячеек в MS EXCEL


Просуммируем значения ячеек в зависимости от цвета их заливки.

update Опубликовано: 08 сентября 2018

МНК: Экспоненциальная зависимость в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью экспоненциальной функции.

update Опубликовано: 11 ноября 2018

Автофильтр в MS EXCEL


Рассмотрим стандартный фильтр ( Данные/ Сортировка и фильтр/ Фильтр ) – автофильтр. Это удобный инструмент для отбора в таблице строк, соответствующих условиям, задаваемым пользователем.

update Опубликовано: 19 октября 2013

Функция ДАТАЗНАЧ() в MS EXCEL


Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), - преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ("11.09.2009") возвращает число 40067, соответствующее 11 …

update Опубликовано: 10 декабря 2013

Сценарии в MS EXCEL


Сценарии - это инструмент MS EXCEL из группы Анализ "что-если" ( Вкладка Данные/ Группа Работа с данными ). Диспетчер сценариев позволяет создавать и подставлять различные значения исходных данных в модель, …

update Опубликовано: 03 июня 2014

Построчное сравнение таблиц в MS EXCEL


Сравним две таблицы имеющих одинаковую структуру (одинаковое количество строк и столбцов). Таблицы будем сравнивать построчно: выделим те значения из строки1 таблицы1, которые содержатся в строке1 таблицы2, а также значения из …

update Опубликовано: 23 сентября 2014

Аннуитет. Обзор функций MS EXCEL


В статье рассмотрены финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , ПС() , БС() , а также ОБЩДОХОД() и ОБЩПЛАТ() , которые используются для расчетов параметров …

update Опубликовано: 02 февраля 2015

Критерий независимости хи-квадрат в MS EXCEL


Критерий независимости хи-квадрат используется для определения связи между двумя категориальными переменными. Примерами пар категориальных переменных являются: Семейное положение vs. Уровень занятости респондента; Порода собак vs. Профессия хозяина, Уровень з/п vs. …

update Опубликовано: 14 декабря 2016

Сумма трех наибольших значений (без учета повторов) в MS EXCEL


Сложим три (четыре, пять и т.д.) наибольших значений из диапазона. Если в диапазоне имеются повторы, то учитывать их не будем.

update Опубликовано: 03 апреля 2013

Транспонирование столбца в MS EXCEL в таблице с повторами


Пусть имеется таблица наименований обуви. Каждое наименование обуви (столбец №1) повторяется столько раз, сколько у него имеется различных размеров (столбец №2) . Иногда требуется «перевернуть» не всю таблицу, а только …

update Опубликовано: 09 апреля 2014

Функция ДВССЫЛ() в MS EXCEL


Функция ДВССЫЛ() , английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой . Например, формула = ДВССЫЛ("Лист1!B3") эквивалентна формуле = Лист1!B3 . Мощь этой функции состоит в том, что …

update Опубликовано: 10 апреля 2014

Отбор строк таблицы MS EXCEL по их номеру


Для извлечения из таблицы только нужных строк можно использовать формулы, а не только Автофильтр . Покажем преимущества и недостатки обеих подходов.

update Опубликовано: 11 апреля 2014

Функция ТЕКСТ() в MS EXCEL


Функция ТЕКСТ( ) , английская версия TEXT() , преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(100;"0,00 р.") вернет текстовую строку 100,00 …

update Опубликовано: 28 мая 2014

Функция СЕГОДНЯ() в MS EXCEL


Задача функции СЕГОДНЯ( ) , английский вариант TODAY(), - вернуть текущую дату. Записав, формулу =СЕГОДНЯ() получим 21.05.2011 (если конечно сегодня этот день).

update Опубликовано: 11 декабря 2013

Сортировка списка в MS EXCEL по длине строки


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

update Опубликовано: 24 апреля 2013

Условное форматирование в MS EXCEL


Условное форматирование – один из самых полезных инструментов EXCEL. Умение им пользоваться может сэкономить пользователю много времени и сил.

update Опубликовано: 26 октября 2012

Советы по построению таблиц в MS EXCEL


Планируйте размещение данных в книге, на листе, в таблице, так как этого ожидает EXCEL . Похоже, что разработчики EXCEL хорошо знают типичные задачи, стоящие перед пользователями и создали такую среду, …

update Опубликовано: 02 ноября 2012

Формулы массива в MS EXCEL. Знакомство


Вводная статья для тех, кто никогда не использовал формулы массива.

update Опубликовано: 11 ноября 2012

Формулы массива в MS EXCEL, возвращающие одно значение


Формулы массива могут возвращать как единственное значение, так и несколько значений. В первом случае для отображения результата потребуется одна ячейка, во втором – диапазон. В этой статье рассмотрим формулы массива, …

update Опубликовано: 11 ноября 2012

Элементы управления формы в MS EXCEL


Элементы управления формы (Поле со списком, Флажок, Счетчик и др.) помогают быстро менять данные на листе в определенном диапазоне, включать и выключать опции, делать выбор и пр. В принципе, без …

update Опубликовано: 18 ноября 2012

Простые проценты в MS EXCEL


Рассмотрим простые проценты - метод начисления, при котором сумма начисленных процентов определяется исходя только из первоначальной величины вклада (или долга). Процент на начисленные проценты не начисляется (проценты не капитализируются).

update Опубликовано: 03 февраля 2015

Подсчет Уникальных значений с условиями в MS EXCEL


Подсчитаем Уникальные значения с одним и двумя условиями.

update Опубликовано: 18 июля 2016

Функция ЕТЕКСТ() в MS EXCEL


Задача функции ЕТЕКСТ() , английский вариант ISTEXT(), - проверять является ли содержимое ячейки текстовым значением или нет. Формула = ЕТЕКСТ(5) вернет ЛОЖЬ, а =ЕТЕКСТ("Привет!") вернет ИСТИНА.

update Опубликовано: 12 декабря 2013

Функция ПЛТ() в MS EXCEL


Функция ПЛТ( ) , английский вариант PMT(), позволяет рассчитать месячную сумму платежа по кредиту в случае аннуитетных платежей (когда за кредит платится равными частями).

update Опубликовано: 16 декабря 2013

Функция АДРЕС() в MS EXCEL


Функция АДРЕС() , английский вариант ADDRESS(), возвращает адрес ячейки на листе, для которой указаны номера строки и столбца. Например, формула АДРЕС(2;3) возвращает значение $C$2 .

update Опубликовано: 10 января 2014

Простая гистограмма в MS EXCEL (без использования Диаграмм)


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

update Опубликовано: 03 декабря 2012

Функция ВЫБОР() в Excel


Функция ВЫБОР() , английский вариант CHOOSE(), возвращает значение из заданного списка аргументов-значений в соответствии с заданном индексом. Например, формула =ВЫБОР(2;"ОДИН";"ДВА";"ТРИ") вернет значение ДВА. Здесь 2 - это значение индекса, а …

update Опубликовано: 06 апреля 2014

Квантили распределений MS EXCEL


Рассмотрим вычисление квантилей для некоторых функций распределений, представленных в MS EXCEL .

update Опубликовано: 19 ноября 2016

Переводим число прописью в число цифрами в Excel


Преобразуем число из текстовой формы в обычное число из цифр: например, "сто пятьдесят три" преобразуем в 153.

update Опубликовано: 06 марта 2017

Контроль ввода ОШИБочных значений в MS EXCEL. Часть 2 - с применением Проверки данных


Используем инструмент Проверка данных для предотвращения ввода пользователем ошибочного значения в MS EXCEL.

update Опубликовано: 04 декабря 2012

Ввод данных из списка значений. Часть 1: Выпадающий список в MS EXCEL


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

update Опубликовано: 04 декабря 2012

Функция распределения и плотность вероятности в MS EXCEL


Даны определения Функции распределения случайной величины и Плотности вероятности непрерывной случайной величины. Эти понятия активно используются в статьях о статистике сайта <noindex> www.excel2.ru </noindex> . Рассмотрены примеры вычисления Функции распределения и Плотности …

update Опубликовано: 13 октября 2016

Выделение в MS EXCEL телефонных номеров, принадлежащих определенным абонентам


Используем Условное форматирование для выделения номеров принадлежащих абонентам г.Москва.

update Опубликовано: 05 декабря 2012

Средневзвешенная цена в MS EXCEL


Рассчитаем средневзвешенную цену товара без использования вспомогательных столбцов. Также рассчитаем средневзвешенную цену с условием.

update Опубликовано: 05 января 2013

Поиск решения MS EXCEL. Знакомство


Поиск решения - это надстройка Microsoft Excel, с помощью которой  можно найти оптимальное решение задачи с учетом заданных пользователем ограничений.

update Опубликовано: 23 марта 2015

Связанный список в MS EXCEL на основе элемента управления формы


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

update Опубликовано: 21 января 2013

Многоуровневый связанный список в MS EXCEL


Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

update Опубликовано: 21 января 2013

Функция БДСУММ() - Сложение с множественными условиями в MS EXCEL


Функция БДСУММ() , английский вариант DSUM(), суммирует числа в таблице данных, которые удовлетворяют заданным условиям.

update Опубликовано: 02 апреля 2013

Функция СУММЕСЛИ() - Сложение с одним критерием в MS EXCEL (Часть1.ЧИСЛОвой критерий)


Для суммирования значений, удовлетворяющих заданному критерию (условию), используется функция СУММЕСЛИ() , английская версия SUMIF().

update Опубликовано: 02 апреля 2013

Расширенный фильтр в MS EXCEL


Главный недостаток стандартного фильтра ( Данные/ Сортировка и фильтр/ Фильтр ) – это отсутствие визуальной информации о примененном в данный момент фильтре: необходимо каждый раз лезть в меню фильтра, чтобы …

update Опубликовано: 31 марта 2013

Сложение с одним критерием в MS EXCEL (Часть3.Критерий - ДАТА)


Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону с датами .

update Опубликовано: 02 апреля 2013

Сложение с несколькими критериями в MS EXCEL (Часть 4.Два Условия (ИЛИ и И))


Произведем подсчет строк таблицы, удовлетворяющих сразу трем критериям, которые образуют Условие ИЛИ и Условие И, затем просуммируем числовые значения, этих строк. Например, в таблице с перечнем Фруктов и их количеством …

update Опубликовано: 02 апреля 2013

Число прописью в Excel (динамический вариант)


Запишем число прописью в Excel без использования VBA . Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 …

update Опубликовано: 03 апреля 2013

Минимальное положительное значение в диапазоне MS EXCEL


Найдем минимальное положительное значение в диапазоне с положительными, отрицательными числами и 0.

update Опубликовано: 03 апреля 2013

Наибольший по условию в MS EXCEL


Найти, например, второе наибольшее значение в списке можно с помощью функции НАИБОЛЬШИЙ() . В статье приведено решение задачи, когда наибольшее значение нужно найти не среди всех значений списка, а только …

update Опубликовано: 03 апреля 2013

Сложение значений в MS EXCEL на основании их повторяемости (Часть1.Сложение Уникальных)


Пусть диапазон суммирования содержит повторяющиеся значения. Произведем сложение ЧИСЕЛ без учета повторов.

update Опубликовано: 02 апреля 2013

Число прописью в MS EXCEL (статичный вариант)


Превратим число 256 в строку "двести пятьдесят шесть". Число прописью запишем без использования VBA .

update Опубликовано: 03 апреля 2013

Суммирование ЧИСЕЛ соответствующих неповторяющимся значениям в MS EXCEL


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

update Опубликовано: 03 апреля 2013

Есть ли в слове в MS EXCEL латинские буквы, цифры, ПРОПИСНЫЕ символы


Определим, имеется ли в текстовой строке буквы из латиницы, цифры или ПРОПИСНЫЕ символы. Научимся определять наличие нежелательных символов одной формулой.

update Опубликовано: 03 апреля 2013

Сложение в MS EXCEL каждого второго (третьего и т.п.) значения в списке


Все помнят, как утомительно писать в ручную формулу сложения каждого второго значения в списке: A1+A3+A5+… К счастью есть функция СУММПРОИЗВ() .

update Опубликовано: 03 апреля 2013

Поиск ЧИСЛА ближайшего к заданному. Сортированный список в MS EXCEL


Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существуют специальные функции, например, ВПР() , но они работают только если исходный список сортирован по возрастанию или убыванию.

update Опубликовано: 03 апреля 2013

Поиск ЧИСЛА ближайшего к заданному. Несортированный список в MS EXCEL


Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существует специальные функции, например, ВПР() , ПРОСМОТР() , ПОИСКПОЗ() , но они работают только если исходный список сортирован по возрастанию или убыванию. …

update Опубликовано: 03 апреля 2013

Максимальный и Минимальный по условию в MS EXCEL


Нахождение максимального/ минимального значения - простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию.

update Опубликовано: 03 апреля 2013

Перевод в MS EXCEL названия функций с английского на русский


Ручной перевод формул с английского языка на русский всегда утомителен. Например, если нужно перевести RIGHT(A1,LEN(A1)) в ПРАВСИМВ(A1;ДЛСТР(A1)) . Попробуем автоматизировать процесс.

update Опубликовано: 04 апреля 2013

Поиск решения MS EXCEL (1.3). Распределение ресурсов (ограничение по количеству оборудования, несколько периодов)


Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). Расчет будем проводить с помощью надстройки Поиск решения.

update Опубликовано: 25 марта 2015

Функция MS EXCEL ЛИНЕЙН()


Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, F -статистики и др.).

update Опубликовано: 26 января 2019

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть3. Поиск с учетом РЕГИСТРА


Найдем текстовые значения, удовлетворяющие заданному пользователем критерию с учетом РЕгиСТРА. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для …

update Опубликовано: 14 апреля 2013

Гипергеометрическое распределение. Дискретные распределения в MS EXCEL


Рассмотрим Гипергеометрическое распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ГИПЕРГЕОМ.РАСП() построим графики функции распределения и плотности вероятности. Приведем пример аппроксимации гипергеометрического распределения биномиальным.

update Опубликовано: 07 ноября 2016

Подсчет символов в диапазоне в MS EXCEL


Найдем количество вхождений определенного символа в диапазоне ячеек.

update Опубликовано: 03 апреля 2013

Маркер заполнения в MS EXCEL


Маркер заполнения - небольшой черный квадратик, который появляется в правом нижнем углу выделенной ячейки или выделенного диапазона. Маркер заполнения используется для заполнения соседних ячеек на основе содержимого выделенных ячеек.

update Опубликовано: 10 апреля 2013

Биномиальное распределение. Дискретные распределения в MS EXCEL


Рассмотрим Биномиальное распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БИНОМ.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения p, математического ожидания распределения …

update Опубликовано: 29 октября 2016

Быстрый анализ таблиц в программе NeoNeuro PIVOT TABLE


Рассмотрим анализ таблиц в программе NeoNeuro PIVOT TABLE. Эта программа позволяет создать сводную таблицу, диаграмму и отчет буквально за несколько секунд.

update Опубликовано: 06 ноября 2016

Нахождение в MS EXCEL позиции n-го вхождения символа в слове


Находим позицию третьего, четвертого и т.д. вхождения символа в слове (в текстовой строке).

update Опубликовано: 03 апреля 2013

Импортируем (вставляем) ТЕКСТ из WORD в ячейку EXCEL


Рассмотрим подробнее вставку в одну ЯЧЕЙКУ листа текстовых строк из WORD, содержащих несколько абзацев и символы табуляции.

update Опубликовано: 04 апреля 2013

Определяем имя листа в MS EXCEL


Определим имя листа с помощью функции ЯЧЕЙКА() .

update Опубликовано: 04 апреля 2013

Выбор в MS EXCEL из текстовой строки n-го слова


Если текстовая строка в ячейке содержит несколько слов, например, «Василий Иванович Петров», то можно создать формулу для вывода, например, первого (второго, третьего и т.д.) слова.

update Опубликовано: 04 апреля 2013

Отрицательное Биномиальное распределение. Дискретные распределения в MS EXCEL


Рассмотрим Отрицательное Биномиальное распределение, вычислим его математическое ожидание и дисперсию. С помощью функции MS  EXCEL ОТРБИНОМ.РАСП() построим графики функции распределения и плотности вероятности.

update Опубликовано: 07 ноября 2016

Выбор в MS EXCEL из строки первого слова


Если строка в ячейке содержит ФИО, например, «Петров Василий Иванович», то можно создать формулу для вывода первого слова (фамилии).

update Опубликовано: 04 апреля 2013

Массив последовательных чисел в MS EXCEL


Для построения формул массива иногда используют числовую последовательность, например {1:2:3:4:5:6:7}, вводимую непосредственно в формулу. Эту последовательность можно сформировать вручную, введя константу массива , или с использованием функций, например СТРОКА() . …

update Опубликовано: 08 апреля 2013

Подсчет рабочих дней при нестандарной рабочей неделе в MS EXCEL


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

update Опубликовано: 08 апреля 2013

Геометрическое распределение. Дискретные распределения в MS EXCEL


Рассмотрим Геометрическое распределение, вычислим его математическое ожидание и дисперсию. С помощью функции MS EXCEL ОТРБИНОМ.РАСП() построим графики функции распределения и плотности вероятности.

update Опубликовано: 07 ноября 2016

Есть ли слово в списке MS EXCEL


Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.

update Опубликовано: 07 апреля 2013

Поиск в MS EXCEL ДАТЫ ближайшей к заданной


Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .

update Опубликовано: 07 апреля 2013

Функция СТРОКА() в MS EXCEL


Функция СТРОКА( ) , английский вариант ROW(), вычисляет номер строки, определяемой ссылкой: формула =СТРОКА(B5) вернет 5.

update Опубликовано: 09 апреля 2013

Отображаем в MS EXCEL дробное значение лет (6,9 лет) в годах и месяцах (6 лет 10 месяцев)


При расчете временных интервалов, результат может получиться в годах с дробной частью (6,9 лет). Определим, сколько месяцев составляет 0,9 лет. Результат запишем в виде: 6 лет / 10 месяцев.

update Опубликовано: 07 апреля 2013

Сводные таблицы в MS Excel


Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах . Рассмотрим процесс создания несложных Сводных таблиц.

update Опубликовано: 10 апреля 2013

Функция ЧАСТОТА() - Подсчет ЧИСЛОвых значений в MS EXCEL


Функция ЧАСТОТА( ) , английская версия FREQUENCY() , вычисляет частоту попадания значений в заданные пользователем интервалы и возвращает соответствующий массив чисел.

update Опубликовано: 09 апреля 2013

Таблицы в формате EXCEL 2007


Для упрощения управления логически связанными данными в EXCEL 2007 введен новый формат таблиц. Использование таблиц в формате EXCEL 2007 снижает вероятность ввода некорректных данных, упрощает вставку и удаление строк и …

update Опубликовано: 10 апреля 2013

Число дней в месяце в MS EXCEL


Определим число дней в заданном месяце с помощью функции КОНМЕСЯЦА() .

update Опубликовано: 07 апреля 2013

Понедельник или воскресенье текущей недели в MS EXCEL


Определим понедельник или воскресенье текущей недели. Неделю будем задавать через дату.

update Опубликовано: 07 апреля 2013

Вычисления с датами до 01.01.1900 в MS EXCEL


Если возникла необходимость выполнить вычисления с датами до 01.01.1900, то придется прибегнуть к некоторым ухищрениям, чтобы использовать встроенные функции EXCEL для работы с датами.

update Опубликовано: 08 апреля 2013

Возраст или стаж в годах в MS EXCEL


Определим возраст (или стаж) в полных годах.

update Опубликовано: 08 апреля 2013

Сколько лет, месяцев, дней прошло с конкретной даты в MS EXCEL


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

update Опубликовано: 08 апреля 2013

Поиск ячеек содержащих в MS EXCEL спецсимволы


В MS WORD спецсимволы (конец абзаца, разрыв строки и т.п.) легко найти с помощью стандартного поиска ( CTRL+F ), т.к. им соответствуют специальные комбинации символов (^v, ^l). В окне стандартного …

update Опубликовано: 10 апреля 2013

Функция РАЗНДАТ() - Вычисление разности двух дат в днях, месяцах, годах в MS EXCEL


Для вычислений длительности временных интервалов удобней всего использовать недокументированную функцию РАЗНДАТ( ) , английский вариант DATEDIF().

update Опубликовано: 08 апреля 2013

Функция СМЕЩ() в MS EXCEL


Функция СМЕЩ( ) , английский вариант OFFSET(), возвращает ссылку на диапазон ячеек. Размер диапазона и его положение задается в параметрах этой функции.

update Опубликовано: 09 апреля 2013

Автоподбор в MS EXCEL ширины столбца и высоты строки по содержимому ячеек


Наиболее быстрый способ добиться, чтобы содержимое ячеек отображалось полностью – это использовать механизм автоподбора ширины столбца/ высоты строки по содержимому.

update Опубликовано: 10 апреля 2013

Подсчет ТЕКСТовых значений с учетом РЕгиСТра в MS EXCEL


Буквы могут находиться в ВЕРХНЕМ и нижнем регистре (ПРОПИСНЫЕ и строчные). Текстовые строки, соответственно, могут состоять целиком из строчных или ПРОПИСНЫХ букв, а также состоять из букв находящихся в разном …

update Опубликовано: 19 апреля 2013

Статистики, их выборочные распределения и точечные оценки параметров распределений в MS EXCEL


В статье напомним некоторые понятия математической статистики: выборка, статистика, точечная оценка, выборочное распределение. Продемонстрируем в MS EXCEL сходимость некоторых распределений статистик к нормальному распределению, распределению ХИ-квадрат, распределению Стьюдента и F …

update Опубликовано: 26 ноября 2016

Функция ВРЕМЯ() в MS EXCEL


Рассмотрим функцию ВРЕМЯ() , у которой 3 аргумента: часы, минуты, секунды. Записав формулу =ВРЕМЯ(10;30;0) , получим в ячейке значение 10:30:00 в формате Время. Покажем, что число 0,4375 соответствует 10:30 утра.

update Опубликовано: 11 апреля 2013

Проверка простых гипотез критерием хи-квадрат Пирсона в MS EXCEL


Рассмотрим применение в MS EXCEL критерия хи-квадрат Пирсона для проверки простых гипотез.

update Опубликовано: 13 декабря 2016

Поиск нескольких значений с максимальной длиной строки в MS EXCEL


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

update Опубликовано: 16 апреля 2013

Двухвыборочный тест для дисперсии: F-тест в MS EXCEL


Рассмотрим использование MS EXCEL при проверке статистических гипотез о равенстве дисперсий 2-х нормальных распределений. Вычислим значение тестовой статистики F 0 , рассмотрим процедуру «двухвыборочный F -тест», вычислим Р-значение (Р- value …

update Опубликовано: 12 декабря 2016

Поиск ЧИСЛОвых значений и вывод соответствующих значений в отдельный список в MS EXCEL


Найдем числовые значения, равные заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в …

update Опубликовано: 16 апреля 2013

Функция ДАТА() в MS EXCEL


Функция ДАТА() , английский вариант DATE(), в озвращает целое число, представляющее определенную дату. Формула =ДАТА(2011;02;28) вернет число 40602. Если до ввода этой формулы формат ячейки был задан как Общий, то …

update Опубликовано: 11 апреля 2013

Среднее пяти последних значений в MS EXCEL


Найдем, среднее последних 5 последних значений в списке. Если столбец со значениями постоянно заполняется, то эта задача перестает быть тривиальной.

update Опубликовано: 16 апреля 2013

Двумерная гистограмма в MS EXCEL


Гистограмма - это инструмент, позволяющий визуально оценить величину и характер разброса данных в выборке. С помощью диаграммы MS EXCEL создадим двумерную гистограмму для сравнения 2-х наборов данных.

update Опубликовано: 03 декабря 2016

Распределение Вейбулла. Непрерывные распределения в MS EXCEL


Рассмотрим распределение Вейбулла, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ВЕЙБУЛЛ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров …

update Опубликовано: 09 ноября 2016

Функция ДАТАМЕС() в MS EXCEL


Функция ДАТАМЕС() , английский вариант EDATE(), в озвращает дату, отстоящую на заданное количество месяцев вперед или назад от заданной даты.

update Опубликовано: 12 апреля 2013

Вертикальная линия на гистограмме в MS EXCEL


Чтобы совместить вертикальную линию с гистограммой потребуется объединить 2 типа диаграмм: точечную и гистограмму. Покажем как это сделать.

update Опубликовано: 25 ноября 2017

Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом


Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот …

update Опубликовано: 17 апреля 2013

Проверка статистических гипотез в MS EXCEL о равенстве среднего значения распределения (дисперсия неизвестна)


Рассмотрим использование MS EXCEL при проверке статистических гипотез о среднем значении распределения в случае неизвестной дисперсии. Вычислим тестовую статистику t 0 , рассмотрим процедуру «одновыборочный t -тест», вычислим Р-значение (Р- …

update Опубликовано: 10 декабря 2016

Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца


Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца . Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего …

update Опубликовано: 14 апреля 2013

Последняя заполненная ячейка в MS EXCEL


Найдем номер строки последней заполненной ячейки в столбце и списке. По номеру строки найдем и само значение.

update Опубликовано: 17 апреля 2013

Создание списка неповторяющихся значений с использованием ранее определенного списка в MS EXCEL


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

update Опубликовано: 21 апреля 2013

Поиск решения MS EXCEL (6.5). Пропускная способность сетевого трубопровода


Необходимо найти максимальную пропускную способность сетевого трубопровода. Построим линейную модель и с помощью надстройки Поиск решения решим задачу.

update Опубликовано: 28 марта 2015

Функция БСЧЁТ() - Подсчет с множественными условиями в MS EXCEL


Функция БСЧЁТ() , английский вариант DCOUNT(), подсчитывает строки в таблице данных, которые удовлетворяют заданным условиям.

update Опубликовано: 18 апреля 2013

Подсчет значений с множественными критериями (Часть 3. Условия (ИЛИ и И)) в MS EXCEL


Произведем подсчет строк таблицы, удовлетворяющих сразу трем критериям, которые образуют Условие ИЛИ и Условие И. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых …

update Опубликовано: 18 апреля 2013

Сводная таблица для отбора Уникальных значений из списка MS EXCEL


Для отбора уникальных значений можно использовать формулы , расширенный фильтр или можно воспользоваться меню Данные/ Работа с данными/ Удалить дубликаты . В этой статье используем Сводные таблицы .

update Опубликовано: 14 января 2018

Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL


Имеется таблица, состоящая из двух столбцов: из столбца с повторяющимися текстовыми значениями и столбца с числами. Создадим таблицу, состоящую только из строк, с уникальными текстовыми значениями. По числовому столбцу произведем …

update Опубликовано: 22 апреля 2013

Выделение неповторяющихся значений в MS EXCEL


Настроим Условное форматирование для выделения только неповторяющихся значений в списке.

update Опубликовано: 22 апреля 2013

Транспонирование таблиц в MS EXCEL


Иногда требуется «перевернуть» таблицу, т.е. поменять столбцы и строки местами.

update Опубликовано: 26 апреля 2013

Подсчет ЧИСЕЛ попадающих в интервал в MS EXCEL


Подсчет чисел, попадающих в интервал – стандартная задача: используйте функцию СЧЕТЕСЛИМН() . Усложним задачу, сделаем интервал легко настраиваемым.

update Опубликовано: 19 апреля 2013

Подсчет пустых ячеек в MS EXCEL


Подсчет пустых ячеек? Имеется в виду ничего не содержащих ячеек? А есть разница? Разбираемся подробнее.

update Опубликовано: 19 апреля 2013

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Распределение Стьюдента (t-распределение). Распределения математической статистики в MS EXCEL


Рассмотрим Распределение Стьюдента (t-распределение). С помощью функции MS EXCEL СТЬЮДЕНТ.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.

update Опубликовано: 10 ноября 2016

Справочник в MS EXCEL


Справочник состоит из двух таблиц: справочной таблицы, в строках которой содержатся подробные записи о некоторых объектах (сотрудниках, товарах, банковских реквизитах и пр.) и таблицы, в которую заносятся данные связанные с …

update Опубликовано: 19 апреля 2013

Создание списка неповторяющихся значений в MS EXCEL


Разрешим ввод в столбец только неповторяющихся  значений.

update Опубликовано: 20 апреля 2013

Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL


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

update Опубликовано: 23 апреля 2013

Отбор неповторяющихся значений со значениями из соседнего столбца в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Отбор уникальных значений из двух диапазонов в MS EXCEL


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

update Опубликовано: 22 апреля 2013

Отбор неповторяющихся значений из двух диапазонов в MS EXCEL


Имея два списка с однотипными повторяющимися значениями, создадим список, состоящий только из неповторяющихся значений.

update Опубликовано: 22 апреля 2013

Отбор уникальных СТРОК в MS EXCEL


Для извлечения из таблицы только уникальных строк (строк без повтора) можно использовать формулы.

update Опубликовано: 22 апреля 2013

Есть ли в MS EXCEL повторы в списке?


Ответим на вопрос: Есть ли в списке значений повторы? Список может содержать пустые ячейки.

update Опубликовано: 22 апреля 2013

Выделение повторяющихся значений в MS EXCEL


Настроим Условное форматирование для выделения только повторяющихся значений в списке.

update Опубликовано: 22 апреля 2013

Выделение в MS EXCEL дубликатов, встречающихся 3 или 4 раза


Настроим Условное форматирование для выделения повторяющихся значений, которые повторяются 3 или 4 раза.

update Опубликовано: 22 апреля 2013

Подробный разбор задачи (примера) множественной регрессии в MS EXCEL


Рассмотрим пример построения модели множественной регрессии в случае 2-х регрессоров в MS EXCEL.

update Опубликовано: 26 января 2019

Подсчет ЧИСЕЛ которые имеют повторы в MS EXCEL


Произведем подсчет ЧИСЛОвых значений, которые имеют повторы.

update Опубликовано: 23 апреля 2013

Подсчет ТЕКСТовых значений, которые имеют повторы в MS EXCEL


Произведем подсчет ТЕКСТовых значений, которые имеют повторы.

update Опубликовано: 23 апреля 2013

Применение автофильтра в MS EXCEL


В этой статье приводится перечень контрольных заданий по теме Автофильтр . За основу взяты реальные Контрольные задания, которые  предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по теме …

update Опубликовано: 21 октября 2013

Подсчет Уникальных и Неповторяющихся ТЕКСТовых значений в заданном интервале в MS EXCEL


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

update Опубликовано: 24 апреля 2013

Извлекаем в MS EXCEL дробную часть числа


Извлечем из числа 5,324 дробную часть, т.е. 0,324, а также его целую часть.

update Опубликовано: 24 апреля 2013

Изменяем в MS EXCEL денежный формат с российского на американский


В России принято записывать денежный формат в виде 123 456 789,00р. В США десятичная часть отделяется от целой не запятой, точкой, а разряды не пробелом, а запятой. Если требуется отобразить …

update Опубликовано: 25 апреля 2013

Совпадают ли 2 списка в MS EXCEL? (попарное совпадение)


Пусть дано 2 списка ЧИСЛОвых значений. Определим, совпадают ли эти 2 списка (критерий сравнения: попарное совпадение элементов).

update Опубликовано: 25 апреля 2013

Аннуитет. Расчет в MS EXCEL выплаченных процентов за период


Рассчитаем в MS EXCEL сумму процентов, которую необходимо выплатить за определенное количество периодов по кредиту. Выплата кредита производится равными ежемесячными платежами (аннуитетная схема). Процентная ставка и величина платежа - известны, …

update Опубликовано: 02 февраля 2015

Использование Пакета анализа MS EXCEL для построения простой линейной регрессионной модели


Проведем простой регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .

update Опубликовано: 26 января 2019

Объединение 3-х и более списков в MS EXCEL


Иногда для удобства требуется объединить три и более списка значений в один.

update Опубликовано: 24 апреля 2013

Отображение в MS EXCEL ЧИСЛОвого формата в ТЕКСТовой строке


При составлении формул для отображения в ячейке фразы содержащей текст и число могут возникнуть сложности с правильным отображением формата числа. Например, требуется составить фразу «Масса груза 66,00 кг», но получается …

update Опубликовано: 25 апреля 2013

Преобразование в MS EXCEL ЧИСЕЛ в формат ВРЕМЯ


EXCEL хранит ВРЕМЯ в числовой форме (в дробной части числа). Например, 0,75 соответствует 18:00, 0,5 - 12:00. Если, по какой-то причине, значения ВРЕМЕНИ сохранены в десятичной форме, например, 10,5 часов, …

update Опубликовано: 25 апреля 2013

Отображение широты и долготы в MS EXCEL


Если Вам нужно отобразить широту или долготу в формате 56°54'31" (56 градусов 54 минуты 31 секунда), то этот пример для Вас.

update Опубликовано: 25 апреля 2013

Отображение надстрочных символов в MS EXCEL


В EXCEL легко отформатировать шрифт, чтобы отобразить надстрочные (x 2 ) и подстрочные (Al 2 O 3 ) символы. Это можно сделать выделив часть текста в ячейке и через диалоговое …

update Опубликовано: 25 апреля 2013

Безопасное отражение в MS EXCEL номеров счетов с помощью шаблона


Если у вас есть таблица с важными данными (номера кредитных карт, номера личных телефонов, номера страховых полисов), то для сторонних лиц вы можете настроить отображение только последних цифр номера.

update Опубликовано: 25 апреля 2013

Множественная регрессия в MS EXCEL


Рассмотрим использование MS EXCEL для прогнозирования переменной Y на основании нескольких переменных Х, т.е. множественную регрессию.

update Опубликовано: 26 января 2019

Удаляем в MS EXCEL пустые строки в таблице


Часто, особенно при импорте данных в EXCEL , на листе могут формироваться таблицы с ПОЛНОСТЬЮ пустыми строками. Научимся быстро удалять эти ненужные строки, которые в дальнейшем могут затруднить работу с …

update Опубликовано: 25 апреля 2013

Выделение в таблице MS EXCEL групп однотипных данных


С помощью Условного форматирования можно выделить данные, принадлежащие разным группам.

update Опубликовано: 26 апреля 2013

MS EXCEL: Сколько полных месяцев прошло с начала года


Определим сколько полных (календарных) месяцев прошло с начала года. Если сегодня 5/8/2009, то с начала года прошло 7 полных месяцев.

update Опубликовано: 03 июня 2013

Отбор повторяющихся значений с помощью фильтра MS EXCEL


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

update Опубликовано: 15 июля 2013

Выделяем дни недели цветом в MS EXCEL


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

update Опубликовано: 30 июля 2013

Применение Расширенного фильтра в MS EXCEL


В этой статье приводится перечень контрольных заданий по теме Расширенный фильтр . За основу взяты реальные Контрольные задания, которые  предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по …

update Опубликовано: 21 октября 2013

График vs Точечная диаграмма в MS EXCEL


Покажем разницу между 2-мя популярными типами диаграмм: графиком и точечная диаграмма.

update Опубликовано: 29 марта 2015

Применение Промежуточных Итогов в MS EXCEL


В этой статье приводится перечень контрольных заданий по теме Промежуточные Итоги . За основу взяты реальные Контрольные задания, которые  предлагается решить в ВУЗах, техникумах, школах и других обучающих заведениях по …

update Опубликовано: 21 октября 2013

Случайная выборка из генеральной совокупности в MS EXCEL


Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.

update Опубликовано: 14 ноября 2016

Функция СЛЧИС() в MS EXCEL


Функция СЛЧИС( ) , английский вариант RAND(), возвращает случайное вещественное число ( равномерно распределенное ), которое большее или равно 0 и меньше 1 (например, 0,797285074257933).

update Опубликовано: 23 мая 2014

Функция НАИМЕНЬШИЙ() в MS EXCEL


Функция НАИМЕНЬШИЙ( ) , английский вариант SMALL(), возвращает k-ое наименьшее значение из массива данных. Например, если диапазон A1:А4 содержит значения 2;10;3;7, то формула = НАИМЕНЬШИЙ (A1:А4;2) вернет значение 3 (второе …

update Опубликовано: 27 мая 2014

Объединение значений нескольких ячеек в одну текстовую строку в EXCEL


Объединим значения из нескольких ячеек в одной ячейке.

update Опубликовано: 09 июня 2014

Microsoft Excel 2007 (основные отличия от EXCEL 2003)


Статья посвящается тем пользователям, которые продолжают работать в версии MS EXCEL 2003.

update Опубликовано: 27 июня 2014

Выборочно скрываем подписи данных на диаграмме в MS EXCEL


Подписи данных на диаграмме часто необходимы, но могут ее загромождать. Отобразим только те подписи данных (значения), которые больше определенного порогового значения.

update Опубликовано: 31 июля 2014

Генерация случайных чисел в MS EXCEL


Рассмотрим генерацию случайных чисел с помощью надстройки Пакет Анализа и формул MS EXCEL .

update Опубликовано: 23 ноября 2016

Переключатель - элемент управления формы в MS EXCEL


Элементы Переключатель позволяют делать выбор значений из заранее заданного диапазона 1, 2, 3, ... Этот элемент имеет много общего с Флажком .

update Опубликовано: 09 ноября 2014

Список - элемент управления формы в MS EXCEL


Элемент управления формы Список выводит список нескольких элементов, которые может выбрать пользователь. Этот элемент имеет много общего с элементом Поле со списком.

update Опубликовано: 10 ноября 2014

Уровень значимости и уровень надежности в MS EXCEL


Дадим определение терминам уровень надежности и уровень значимости. Покажем, как и где они используется в MS EXCEL .

update Опубликовано: 23 ноября 2016

Ошибка второго рода и кривые оперативной характеристики в MS EXCEL


Определим выражение для вычисления ошибки второго рода и мощности теста, построим в MS EXCEL кривые оперативной характеристики (Operating-characteristic curves).

update Опубликовано: 24 ноября 2016

Ближайшая суббота


Определим дату ближайшей субботы перед заданным днем. Т.е. если сегодня 25.11.2014 (вторник), то ближайшая прошедшая суббота - 22.11.2014. Также определим дату ближайшей субботы после заданного дня и просто дату ближайшей …

update Опубликовано: 25 ноября 2014

Многоуровневый связанный список в MS EXCEL на основе таблицы


В статье Многоуровневый связанный список рассмотрен вариант 3-х уровневого списка. Элементы каждого уровня в нем располагаются на отдельных листах. Это не всегда удобно: при создании 4-х и 5-и уровневых списков …

update Опубликовано: 04 декабря 2014

Выделение трех наибольших значений


Выделим с помощью Условного форматирования три наибольших значения в списке.

update Опубликовано: 09 марта 2015

Расчет страхового (трудового) стажа в MS EXCEL


Рассчитаем страховой (трудовой) стаж в годах, месяцах и днях.

update Опубликовано: 22 марта 2015

Поиск решения MS EXCEL (6.4). Кратчайший путь (неполный граф, линейная модель)


Необходимо найти кратчайший путь между 2-мя заданными городами. Построим линейную модель и с помощью надстройки Поиск решения решим задачу.

update Опубликовано: 28 марта 2015

Поиск решения MS EXCEL (2.1). Оптимизация закупок


Создадим модель для определения наиболее оптимального варианта закупок. Расчет будем проводить с помощью надстройки Поиск решения.

update Опубликовано: 26 марта 2015

Поиск решения MS EXCEL (3.2). Минимизация потерь (задача о рюкзаке)


Создадим модель для одной из модификаций классической задачи о рюкзаке: укладка как можно большего числа вещей в рюкзак при условии, что общий объём (или вес) всех предметов, способных поместиться в …

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (3.4). Закрыть завод или нет?


Создадим модель для решения следующей задачи: компания желает минимизировать затраты, стоит ли ей закрыть один из своих заводов? Расчет будем проводить с помощью надстройки Поиск решения.

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (5.1). График занятости


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

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (6.1). Задача линейного программирования (ЛП)


Решим задачу линейного программирования с помощью надстройки Поиск решения.

update Опубликовано: 27 марта 2015

Поиск решения MS EXCEL (6.7). Определение критического пути


Определим критический путь проекта с помощью надстройки Поиск решения.

update Опубликовано: 28 марта 2015

Диаграмма Водопад в MS EXCEL


Построим диаграмму Водопад, которая используется для визуализации динамики показателя (за одинаковые периоды), а также для демонстрации влияния различных факторов на показатель . Диаграмма Водопад также часто называют "каскадной диаграммой".

update Опубликовано: 29 марта 2015

Вертикальная и горизонтальная прямая (проекции на оси х и y) в MS EXCEL


Построим вертикальную и горизонтальную линию (прямую) на диаграмме MS EXCEL, например для отображения проекции точки на оси х и y.

update Опубликовано: 29 марта 2015

Построение диаграмм с несколькими рядами данных в MS EXCEL


Рассмотрим построение в MS EXCEL 2010 диаграмм с несколькими рядами данных, а также использование вспомогательных осей и совмещение на одной диаграмме диаграмм различных типов.

update Опубликовано: 16 мая 2015

Отбор уникальных значений с сортировкой в MS EXCEL


Из исходной таблицы отберем только уникальные значения и выведем их в отдельный диапазон с сортировкой по возрастанию. Отбор и сортировку сделаем с помощью одной формулой массива. Формула работает как для …

update Опубликовано: 03 апреля 2015

Основные типы диаграмм в MS EXCEL


Рассмотрим основные типы диаграмм в MS EXCEL 2010: Гистограмма, График, С областями, Круговая, Точечная, Линейчатая.

update Опубликовано: 16 мая 2015

Трехмерные диаграммы (поверхности и изолинии) в MS EXCEL


Рассмотрим трехмерные диаграммы в MS EXCEL 2010. С помощью трехмерных диаграмм отображают поверхности объемных фигур (гиперболоид, эллипсоид и др.) и изолинии.

update Опубликовано: 16 мая 2015

Максимальный по условию с выводом номера позиции в MS EXCEL


Нахождение максимального/ минимального значения - простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию. Эта …

update Опубликовано: 28 мая 2015

Распределения случайной величины в MS EXCEL


В статье приведен перечень распределений вероятности, имеющихся в MS EXCEL 2010 и в более ранних версиях.  Даны ссылки на статьи с описанием соответствующих функций MS EXCEL.

update Опубликовано: 23 октября 2016

Плоские алгебраические кривые в MS EXCEL


Построим в MS EXCEL несколько плоских алгебраических кривых: кардиоиду, эпициклоиду, логарифмическую спираль и лемнискату Бернулли.

update Опубликовано: 16 апреля 2015

Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Аналог ВПР()


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

update Опубликовано: 14 мая 2015

Условное форматирование в MS EXCEL. Правило Набор значков


Рассмотрим правило Условного форматирования - Набор значков.

update Опубликовано: 14 августа 2015

Поиск заданных строк в таблице MS EXCEL


Найдем заданные строки в таблице. Строки состоят из 2-х столбцов, значения могут быть любыми.

update Опубликовано: 11 октября 2015

Размещения без повторений: Комбинаторика в MS EXCEL


Подсчитаем в MS EXCEL количество Размещений из n по k и с помощью формул выведем на лист соответствующие варианты размещений (английский перевод термина: partial permutation или sequence without repetition).

update Опубликовано: 01 февраля 2016

Размещения c повторениями: Комбинаторика в MS EXCEL


Подсчитаем в MS EXCEL количество Размещений с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Размещений (английский перевод термина: sequence with …

update Опубликовано: 02 февраля 2016

Перестановки без повторений: Комбинаторика в MS EXCEL


Подсчитаем в MS EXCEL количество перестановок из n элементов. С помощью формул выведем на лист все варианты перестановок (английский перевод термина: permutation).

update Опубликовано: 02 февраля 2016

Сложение и вычитание матриц, умножение матриц на число в MS EXCEL


В этой статье рассмотрены операции сложения и вычитания над матрицами одного порядка, а также операции умножения матрицы на число. Примеры решены в MS EXCEL.

update Опубликовано: 25 ноября 2015

Умножение матриц в MS EXCEL


В этой статье рассмотрены операции умножения матриц с помощью функции МУМНОЖ() или англ.MMULT и с помощью других формул, а также свойства ассоциативности и дистрибутивности операции умножения матриц. Примеры решены в …

update Опубликовано: 26 ноября 2015

Вычисление определителя матрицы в MS EXCEL


Вычислим определитель (детерминант) матрицы с помощью функции МОПРЕД() или англ. MDETERM, разложением по строке/столбцу (для 3 х 3) и по определению (до 6 порядка).

update Опубликовано: 30 ноября 2015

Транспонирование матриц в MS EXCEL


Транспонирование матрицы - это операция над матрицей, при которой ее строки и столбцы меняются местами. Для этой операции в MS EXCEL существует специальная функция ТРАНСП() или англ. TRANSPOSE.

update Опубликовано: 06 декабря 2015

Вычисление обратной матрицы в MS EXCEL


Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() или англ. MINVERSE .

update Опубликовано: 07 декабря 2015

Вычисление длины (модуля) вектора в MS EXCEL


Найдем длину вектора по его координатам (в прямоугольной системе координат), по координатам точек начала и конца вектора и по теореме косинусов (задано 2 вектора и угол между ними).

update Опубликовано: 14 декабря 2015

Метод критического пути в MS EXCEL


Метод критического пути (МКП) - это метод планирования выполнения работ, в основе которого лежит математический алгоритм. Рассчитаем критический путь для связанных между собой работ проекта в MS EXCEL.

update Опубликовано: 02 декабря 2017

Автоматическая сетевая диаграмма проекта в MS EXCEL


Построим сетевую диаграмму проекта на диаграмме MS EXCEL. Сетевая диаграмма будет автоматически перестраиваться при изменении связей между работами. Для этого нам потребуется автоматически определить все пути проекта (не только критические).

update Опубликовано: 30 декабря 2017

Сочетания без повторений: Комбинаторика в MS EXCEL


Подсчитаем в MS EXCEL количество сочетаний из n элементов по k. С помощью формул выведем на лист все варианты сочетаний (английский перевод термина: Combinations without repetition).

update Опубликовано: 02 февраля 2016

Доверительные интервалы в MS EXCEL


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

update Опубликовано: 26 ноября 2016

Доверительный интервал для оценки дисперсии в MS EXCEL


Построим доверительный интервал для оценки дисперсии случайной величины, распределенной по нормальному закону, в MS EXCEL .

update Опубликовано: 27 ноября 2016

Доверительный интервал для разницы средних значений 2-х распределений (дисперсии известны) в MS EXCEL


Построим доверительный интервал для разницы средних значений 2-х распределений в случае известных дисперсий.

update Опубликовано: 27 ноября 2016

Многоуровневый (3+) связанный список (типа предок-родитель) в MS EXCEL


Для сложных иерархических структур с тремя и более уровнями создадим Многоуровневый связанный список типа Предок-Родитель. Теперь структуры типа: Регион-Страна-Город-Улица можно создавать в MS EXCEL.

update Опубликовано: 06 мая 2017

Оптимизация смен рабочих с помощью Поиска решений в MS EXCEL


Решим задачу об оптимизации смен рабочих с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника "Методы оптимизации управления и принятия решений" авторы Зайцев М.Г. и …

update Опубликовано: 06 ноября 2017

Ориентированный граф на диаграмме MS EXCEL


Ориентированный граф на диаграмме MS EXCEL построить можно, но это не просто для новичков. Рассмотрим процесс построения.

update Опубликовано: 21 ноября 2017

Сложение значений в зависимости от цвета ячеек в MS EXCEL


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

update Опубликовано: 08 сентября 2018

МНК: Метод Наименьших Квадратов в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + …

update Опубликовано: 11 ноября 2018

МНК: Логарифмическая зависимость в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью логарифмической функции.

update Опубликовано: 11 ноября 2018

МНК: Степенная зависимость в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью степенной функции.

update Опубликовано: 11 ноября 2018

МНК: Приближение полиномом в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).

update Опубликовано: 24 ноября 2018

МНК: Приближение тригонометрическим полиномом в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью тригонометрического полинома.

update Опубликовано: 24 ноября 2018

Диаграмма рассеяния в MS EXCEL


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

update Опубликовано: 25 ноября 2018

Простая линейная регрессия в MS EXCEL


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

update Опубликовано: 26 января 2019

Генерация данных для простой линейной регрессии в MS EXCEL


Сгенерируем массив данных для целей простой линейной регрессии. Линейный тренд зададим уравнением: Y = k * X + m .

update Опубликовано: 26 января 2019

Корреляция и ковариация в MS EXCEL


Вычислим коэффициент корреляции и ковариацию для разных типов взаимосвязей случайных величин.

update Опубликовано: 25 ноября 2018

Ускорение ввода значений в формате ВРЕМЯ в MS EXCEL


При частом вводе данных в формате времени (2:30), необходимость ввода двоеточия «:» серьезно снижает скорость работы. Возникает вопрос: Можно ли обойтись без ввода двоеточия?

update Опубликовано: 10 апреля 2013

Массивы констант в MS EXCEL


Массив значений (или константа массива или массив констант) – это совокупность чисел или текстовых значений, которую можно использовать в формулах массива . Константы массива необходимо вводить в определенном формате, например, …

update Опубликовано: 08 апреля 2013

Название месяца прописью в MS EXCEL


Если в ячейке содержится дата или номер месяца, то с помощью формул или Формата ячейки можно вывести название месяца. Также решим обратную задачу: из текстового значения названия месяца получим его …

update Опубликовано: 17 июля 2016

МНК: Квадратичная зависимость в MS EXCEL


Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью квадратичной функции y=ax 2 +bx+с .

update Опубликовано: 24 ноября 2018

Округление до первой значащей цифры в MS EXCEL


Напишем формулу для округления числа до первой значащей цифры. Например, 0,00234271 до 0,002; 0,01613 до 0,02. Та же формула округлит 233,64 до 200; 2563,6 до 3000.

update Опубликовано: 03 декабря 2016

Сложные проценты в MS EXCEL. Постоянная ставка


Рассмотрим Сложный процент (Compound Interest) – начисление процентов как на основную сумму долга, так и на начисленные ранее проценты.

update Опубликовано: 03 февраля 2015

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() MS EXCEL


Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ( ) , английский вариант SUBTOTAL(), используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.

update Опубликовано: 25 мая 2014

Центральная предельная теорема в MS EXCEL


Продемонстрируем основные выводы Центральной предельной теоремы с помощью MS EXCEL : построим выборочное распределение среднего, рассчитаем стандартную ошибку и сравним значения, полученные на основе выборки, с выводами ЦПТ.

update Опубликовано: 26 ноября 2016

Отображение в MS EXCEL ЧИСЕЛ в формате миллионов и тысяч


Если ячейка содержит значение больше 1000, то часто не требуется указывать точное значение, а достаточно указать число тысяч или миллионов, один-два знака после запятой и соответственно сокращение: тыс. или млн. …

update Опубликовано: 25 апреля 2013

Изолинии для частного случая поверхностей второго порядка в MS EXCEL


Научимся строить изолинии (contour line) в MS EXCEL для одного из частных случаев поверхностей второго порядка: 2*a 12 *x*y+2*a 14 *x+ 2*a 24 *y +2*a 34 *z+a 44 =0 . …

update Опубликовано: 14 мая 2018

Создание диаграммы Водопад с помощью надстройки MS EXCEL Waterfall Chart Studio


Диаграмма Водопад (или каскадная диаграмма) является стандартным средством визуализации изменения финансовых показателей компании. В этой статье для построения каскадной диаграммы воспользуемся надстройкой Waterfall Chart Studio от компании fincontrollex.com.

update Опубликовано: 27 февраля 2018

Логнормальное распределение. Непрерывные распределения в MS EXCEL


Рассмотрим Логнормальное распределение. С помощью функции MS EXCEL ЛОГНОРМ .РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по логнормальному закону, произведем оценку параметров распределения, среднего …

update Опубликовано: 08 ноября 2016

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved