Финансы в Excel
Условное форматирование, также как и стандартное, не влияет на расчеты, а отвечает только за внешний вид электронных таблиц. Но в отличие от простого формата ячеек, условное форматирование позволяет установить правила отображения информации в зависимости от произведенных вычислений. Что, в свою очередь, в разы сокращает время, необходимое для оформления отчетов.
Файл из приложения к статье создан для демонстрации различных примеров применения условного форматирования. Данные в файле не представляют собой какого-либо практического интереса. Формулы построения отчета о движении денежных средств в данной статье рассмотрены не будут, хотя и могут кому-то пригодится в своих экономических моделях.
Excel заменить значение в ячейке по условию — ПК Знаток
- Выделение цветом или шрифтом текущей ячейки в зависимости от ее же значения.
- Окраска текущей ячейки в зависимости от значения другой ячейки.
- Разделение блоков информации при помощи рамок.
- Скрытие неактуальных данных при помощи форматов.
- Графическое отображение данных – аналог диаграмм.
Подстановочный знак _ (символ подчеркивания) совпадает с любым отдельным символом. Например, если вы уверены, что название компании либо «Smithson», либо «Smithsen», то можете воспользоваться следующим запросом.
Проверка диапазона ячеек
Функция ЕПУСТО() проверяет содержимое только одной ячейки. Чтобы подсчитать количество пустых ячеек в диапазоне, то используйте функцию СЧИТАТЬПУСТОТЫ() , но если ячейки содержат значение Пустой текст (“”), то функция СЧИТАТЬПУСТОТЫ() будет подсчитывать также и эти ячейки наряду с действительно пустыми. Об этом читайте в статье Подсчет пустых ячеек.
Чтобы ответить на вопрос “Есть ли хотя бы 1 пустая ячейка в B6:B11?” используйте формулу массива
Чтобы ответить на вопрос “Есть ли хотя бы 1 заполненная ячейка в B6:B11?” используйте формулу массива
Иногда требуется проверить, пуста ли ячейка. Обычно это делается, чтобы формула не выводила результат при отсутствии входного значения.
В данном случае мы используем ЕСЛИ вместе с функцией ЕПУСТО:
Эта формула означает: ЕСЛИ(ячейка D2 пуста, вернуть текст “Пустая”, в противном случае вернуть текст “Не пустая”). Вы также можете легко использовать собственную формулу для состояния “Не пустая”. В следующем примере вместо функции ЕПУСТО используются знаки “”. “” — фактически означает “ничего”.
Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст “Пустая”, в противном случае вернуть текст “Не пустая”). Вот пример распространенного способа использования знаков “”, при котором формула не вычисляется, если зависимая ячейка пуста:
Если (D3 — Nothing, возвращается Nothing, в противном случае вычисляется формула).
Чтобы выполнить действие только тогда, когда ячейка не пуста (содержит какие-то значения), вы можете использовать формулу, основанную на функции ЕСЛИ.
В примере ниже столбец F содержит даты завершения закупок шоколада.
Поскольку даты для Excel – это числа, то наша задача состоит в том, чтобы проверить в ячейке наличие числа.
Как работает эта формула?
Согласитесь, что нельзя назвать этот способ определения наличия пустых ячеек удобным. Ведь число строк в таблице может измениться, и нужно будет менять формулу: вместо цифры 7 ставить другое число.
Давайте рассмотрим и другие варианты. В ячейке F6 записана большая формула –
Функция ЕПУСТО (английский вариант – ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.
Функция ИЛИ (английский вариант – OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. В противном случае – производим вычисления.
Все достаточно просто, но перечислять кучу ссылок на ячейки не слишком удобно. К тому же, здесь, как и в предыдущем случае, формула не масштабируема: при изменении таблицы она нуждается в корректировке. Это не слишком удобно, да и забыть можно сделать это.
В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант – COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.
И, наконец, еще одна формула Excel, которая позволит производить расчет только при наличии непустых ячеек.
Функция ЕЧИСЛО ( или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.
А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.
Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.
А теперь рассмотрим как проверить, что ячейки не пустые, если в них могут быть записаны не только числа, но и текст.
Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.
А вот третий вариант – к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.
А теперь вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.
Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями и функцией ЕСЛИ в Excel.
Урок 2. Составные условия — SQL для начинающих
Выполним — получили 1 товар в этом ценовом интервале. Рассмотрим на запрос. Как видите в этой конструкции мы снова написали двойное условие по одному полю — price. И с одной строны всё логично и понятно, а с другой стороны эту конструкцию также можно упростить.
Excel Проверка на Равенство Нескольких Значений
п.12 . Решение прикладных задач средствами EXCEL .
Процедуры описательной статистики . Параметрический критерий Стьюдента
В Excel для построения выборочных функций распределения используются специальная функция ЧАСТОТА и процедура пакета анализа ГИСТОГРАММА.
1. Функция ЧАСТОТА вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр. Функция задается в качестве формулы массива.
2. Процедура ГИСТОГРАММА используется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. Процедура выводит результаты в виде таблицы и гистограммы.
В мастере функций Excel имеется также ряд специальных функций, предназначенных для вычисления выборочных характеристик. Прежде всего, это функции, характеризующие «центр» распределения.
•Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число 1, число 2,… — это от 1 до 30 массивов, для которых вычисляется среднее. Например, если ячейки А1-А7 содержат числа 10, 14, 5, 6, 10, 12 и 13, то средним арифметическим СРЗНАЧ ( А1-А7 ) является 10.
•Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана — это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно. Например, МЕДИАНА (10;14;5;6;10;12;13) равняется 10.
•Функция МОДА вычисляет наиболее часто встречающееся значение в выборке. Например, МОДА (10;14;5;6;10;12,13) равняется 10.
К специальным функциям, вычисляющим выборочные характеристики, характеризующие рассеяние вариант, относятся ДИСП, СТАНДОТКЛОН, ПЕРСЕНТИЛЬ.
•Функция ДИСП позволяет оценить дисперсию по выборочным данным. Например, ДИСП(10;14;5;6;10;12;13) равняется 11,667.
•Функция СТАНДОТКЛОН вычисляет стандартное отклонение. Например, СТАНДОТКЛОН(10;14;5;6;10;12;13) равняется 3,416.
•Функция ПЕРСЕНТИЛЬ позволяет получить квантили заданной выборки. Например, если ячейки А1-А7 содержат числа 10, 14, 5, 6, 10, 12 и 13, то квантиль со значением 0,1 является ПЕРСЕНТИЛЬ (А 1-А7;0,1), равная 5,6.
Форму эмпирического распределения позволяют оценить специальные функции:
•Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным. Например, ЭКСЦЕСС(10;14;5;6;10;12;13) равняется -1,169.
•Функция СКОС позволяет оценить асимметрию выборочного распределения. Например, СКОС(10;14;5;6;10;12;13) равняется -0,527.
В пакете Excel помимо мастера функций имеется набор более мощных инструментов для работы с несколькими выборками и углубленного анализа данных, называемый Пакет анализа, который может быть использован для решения задач статистической обработки выборочных данных.
Для установки раздела «Анализ данных» в пакете Excel сделайте следующее:
•в появившемся списке установите флажок Пакет анализа.
В MS Excel для оценки достоверности отличий по критерию Стьюдента используются специальная функция ТТЕСТ и процедуры пакета анализа.
Функция ТТЕСТ (коэффициент Стьюдента) использует следующие параметры: ТТЕСТ (массив 1; массив 2; хвосты; тип). Здесь:
•хвосты — число хвостов распределения. Обычно число хвостов равно 2;
Рассмотрим на примере применение ф ункции ТТЕСТ для оценки статистической значимости различий по исследуемому признаку между двумя выборками.
Изучалось различие в показателях интеллекта студентов первого и пятого курсов технического вуза. Для этого случайным образом были отобраны 12 студентов первого курса и 13 студентов 5 курса, у которых интеллект определялся по одной и той же методике. Были получены следующие результаты:
1 группа — первый курс: 111, 104, 107, 90, 101, 107, 106, 107, 95, 106, 105, 115.
2 группа – пятый курс: 113, 107, 123, 122, 117, 112, 105, 108, 111, 114, 102, 104, 108.
Оценить с помощью критерия Стьюдента достоверность различий между группами.
1. Введите данные: откройте новую рабочую таблицу. Введите в ячейку А1 слово 1 группа — первый курс, затем в ячейки А2-А13 введите показатели интеллекта у студентов первой группы. В ячейку В1 введите слово 2 группа- пятый курс, а в В2-В14 введите показатели интеллекта у студентов второй группы.
В ходе проверки эффективности тренинга каждому из 8 членов группы задавался вопрос « Насколько часто твое мнение совпадает с мнением группы?» — дважды, до и после тренинга. Для ответов использовалась 10-балльная шкала: 1- никогда, …, 5 — в половине случаев, …, 10 – всегда.
Проверить с помощью критерия Стьюдента гипотезу о том, что в результате тренинга самооценка конформизма участников возросла.
H – различия между показателями до и после тренинга не достоверны.
H – различия между показателями до и после тренинга достоверны.
1. Введите данные: откройте новую рабочую таблицу. Введите в ячейку С1 слово До тренинга, затем в ячейки С2-С9 — соответствующие значения. В ячейку Д1 введите слова После тренинга, а в Д2-Д9 — значения.
Проверялась гипотеза о том, что мужчины агрессивнее женщин. По тесту Басса-Дарки опросили 17 мужчин и 20 женщин. Индексы общей агрессивности для каждого из опрошенных приведены в таблице.
20, 17, 13, 19, 22, 8, 10, 7, 10, 15, 10, 5, 8, 5, 6, 5, 5, 8, 10, 10
Члены команды спасателей участвовали в тренинге по повышению стрессоустойчивости. В таблице приведены результаты измерения стрессоустойчивости до тренинга (выборка А) и после тренинга (выборка В).
Можно ли утверждать, что после тренинга стрессоустойчивость испытуемых возросла?
Отбор по условию:, BETWEEN, LIKE, IN, NULL, OR, AND
Функция ПОДСТАВИТЬ в Excel выполняет динамическую замену определенной части строки на указанное новое значение и возвращает новую строку, содержащую замененную часть текста. Благодаря этой функции можно подставлять значения из другой ячейки. Рассмотрим возможности функции на конкретных примерах в Excel.
Особенности использования функции ПОДСТАВИТЬ в Excel
Функция ПОДСТАВИТЬ имеет следующую синтаксическую запись:
- текст – обязательный аргумент, характеризующий текстовую строку, в которой необходимо выполнить замену части текста. Может быть задан как текстовая строка («некоторый текст») или ссылка на ячейку, которая содержит текстовые данные.
- стар_текст – часть текстовой строки, принимаемой в качестве первого аргумента данной функции, которую требуется заменить. Аргумент обязателен для заполнения.
- нов_текст – обязательный для заполнения аргумент, содержащий текстовые данные, которые будут вставлены на место заменяемой части строки.
- [номер_вхождения] – числовое значение, характеризующее номер вхождения старого текста, который требуется заменить на фрагмент нового текста. Возможные варианты записи:
- Аргумент явно не указан. Функция ПОДСТАВИТЬ определит все части текстовой строки, соответствующие фрагменту текста стар_текст, и выполнит их замену на нов_текст;
- В качестве аргумента передано числовое значение. Функция ПОДСТАВИТЬ заменит только указанное вхождение. Отсчет начинается слева направо, число 1 соответствует первому вхождению. Например, функция =ПОДСТАВИТЬ(«текст №1, №2, №3»;«текст»;«новый»;1) вернет значение «новый_текст №1, №2, №3».
- Аргумент [номер_вхождения] должен быть задан из диапазон целых положительных чисел от 1 до n, где n определяется максимально допустимой длиной строки, содержащейся в объекте данных (например, в ячейке).
- Если в текстовой строке, представленной в качестве аргумента текст не содержится фрагмент, переданный в качестве аргумента стар_текст, функция ПОДСТАВИТЬ вернет строку текст без изменений.
- Если число вхождений заменяемого фрагмента в обрабатываемой строке меньше, чем числовое значение, переданное в качестве аргумент [номер_вхождения], функция ПОДСТАВИТЬ вернет текстовую строку в исходном виде. Например, аргументы функции («а 1 а 2 а 3»;«а»;«б»;4) вернут строку «а 1 а 2 а 3».
- Рассматриваемая функция чувствительная к регистру, то есть строки «Слово» и «слово» не являются тождественными.
- Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.
Быстрая замена значений по условию в Excel
Добрый день, уважаемые читатели блога! Сегодня поговорим о быстрой замене значений в таблице Excel. Зачем нам это нужно, когда есть формулы ЕСЛИ и условное форматирование? Ответ прост — для экономии времени. Но ведь можно ещё быстрее. Ниже будет пример с небольшой таблицей, где хочется сразу выделить или поместить нужное значение.
Есть две таблицы, одна оригинал, вторая для изменений. Все ученики кто получил 4 и выше имеют результат «Прошёл», у кого 3 и ниже — «Не прошёл».
Если будем использовать условное форматирование, придётся писать формулу каждый раз и если таблица очень большая и сразу хочется увидеть результат — лучше воспользоваться макросом.
Сразу оговорюсь в макросе будет прописан диапазон для замены, так что если таблица будет больше, нужно просто изменить диапазон.
В этом случае используется связка из команд IF и ElseIf, что позволяет нам несколько расширить понимание формулы ЕСЛИ. Напомню, что макрос будет применён только к ячейкам из диапазона I2:L10!
Запускаем выбор макросов с помощью сочетания клавиш Alt+F8 и нажимаем кнопку «Выполнить».
Получаем результат. Во второй таблице я сразу вижу кто из учеников прошёл дальше, а кто нет. Ну и кому что необходимо подтянуть:)
Таким образом решается некая ограниченность формулы ЕСЛИ, ведь условия в макросе можно продолжить и подобрать свои, всего лишь изменив значения.
Примечание: Компонент Power Query в Excel 2016 называется Скачать и преобразовать. Информация в этой статье относится к обоим компонентам. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.
Примечание: В конце этой статьи есть небольшое видео о том, как вывести редактор запросов.
В Power Query можно заменить одно значение другим в выбранных столбцах.
Введено недопустимое значение следует ввести число excel. Проверка данных в MS EXCEL. Использование формулы для подсчета допущений
- B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
- “NaN” – фрагмент текста, который будет заменен;
- 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.
Управляющие символы — распространенная практика в приложениях проверки на соответствие шаблону; именно поэтому они были включены и в стандарт ANSI/ISO. Однако они не входили в ранние реализации SQL и поэтому не очень распространены. Для обеспечения переносимости приложений следует избегать использования предложения ESCAPE .
Урок 2. Составные условия
В прошлом уроке мы научились выбирать совершеннолетних пользователей с помощью простого SQL запроса.
Теперь попробуем немного уточнить запрос. Например, выберем всех совершенолетних мужчин. В таблицу я добавил дополнительное строковое поле sex, которое хранит m для мужчин и w для женщин:
| id | first_name | last_name | birthday | age | sex |
|---|---|---|---|---|---|
| 1 | Дмитрий | Иванов | 1996-12-11 | 20 | m |
| 2 | Олег | Лебедев | 2000-02-07 | 17 | m |
| 3 | Тимур | Шевченко | 1998-04-27 | 19 | m |
| 4 | Светлана | Иванова | 1993-08-06 | 23 | w |
| 5 | Олег | Ковалев | 2002-02-08 | 15 | m |
| 6 | Алексей | Иванов | 1993-08-05 | 23 | m |
| 7 | Алена | Процук | 1997-02-28 | 18 | w |
Давайте добавим вывод столбца sex и оставим только мужчин. Для этого в блоке условий, который начинается со слова WHERE нужно добавить AND sex = ‘m’:
После выполнения SQL запроса получиться такая таблица:
Посмотрим на SQL запрос. Сейчас блок WHERE содержит составное условие: возраст больше или равен 18 годам и пол равен m. Это простое логическое выражение, которому соответствуют все записи для которых оба условия верны. То есть у которых одновременно и возраст от 18 лет и sex = «m».
Кстати, о sex = «m». Так как мы используем равенство, в результируеющей таблице в колонке sex для всех записей у нас выводится m. Это не логично, ведь мы и так знаем, что выбираем мужчин, поэтому смысла в том, что мы эту информацию выводим в таблице нет. А значит можно удалить sex из запроса. Удалим и посмотрим на результат выполнения SQL-запроса:
Строки выводятся те же, однако столбца sex больше нет.
Обратите внимание, что извлекаем мы столбцы last_name, first_name, birthday, а фильтрутем по age и sex. То есть не обязательно чтобы столбцы, которые мы получаем, совпадали со столбцами в условии. Главное, чтобы все они были в таблице.
Кроме операции AND (И), в условии можно применять OR (ИЛИ). Давайте заменим AND на OR, а также вернем колонки sex и age:
| id | last_name | first_name | birthday | sex | age |
|---|---|---|---|---|---|
| 1 | Иванов | Дмитрий | 1996-12-11 | m | 20 |
| 2 | Лебедев | Олег | 2000-02-07 | m | 17 |
| 3 | Шевченко | Тимур | 1998-04-27 | m | 19 |
| 4 | Иванова | Светлана | 1993-08-06 | w | 23 |
| 5 | Ковалев | Олег | 2002-02-08 | m | 15 |
| 6 | Иванов | Алексей | 1993-08-05 | m | 23 |
| 7 | Процук | Алена | 1997-02-28 | w | 18 |
Получили всех мужчин, а также женщин, которым исполнилось 18 лет. В частности в SQL-таблице две женщины старше 18 лет и все мужчины, даже те, которым меньше 18. Всё это соответствует условию ИЛИ. ИЛИ возраст от 18 лет, ИЛИ мужской пол.
Теперь переключимся на таблицу products. В ней появилось поле country, которое содержит данные о стране производителе:
Давайте выберем товары, произведененные в России, Белоруссии и на Украине. Напишем SQL-запрос:
После выполнения запроса мы получим следующую таблицу:
Разберем запрос: в блоке WHERE мы используем три условия, разделенные OR (или). Во всех трех условиях мы с помощью символа равенства сравниваем значение в столбце country с одной из стран: ИЛИ Россия, ИЛИ Украина, ИЛИ Белоруссия.
Если мы хотим получить товары еще каких-то стран, то нужно добавить еще условия OR. Это не очень удобно, так как запрос становится громоздиким.
Но его можно упростить. Кроме стандартных условий сравнения AND и OR в языке SQL есть условие принадлежности IN, которое в данном случае подходит лучше. Напишем после WHERE:
Конструкция получилась короче и понятней. И с помощью неё мы выбираем данные, в которых страна равна любом из значений перечисленных в скобках. После запуска запроса мы получим результат, аналогичный предыдущему.
Но давайте добавим к запросу еще одно условие. Например нам нужны не просто товары, а товары стоимостью до 10 000 рублей. Напишем:
Получили новую таблицу с тремя записями, которые удовлетовряют новому условию. И в этом условии мы совместили AND и IN. То есть в SQL-запросах можно совмещать логические операции AND и OR с оператором IN. Что делает их очень гибкими.
Теперь давай попробуем выбрать товары, стоимостью от 10000 до 20000.
Выполним — получили 1 товар в этом ценовом интервале. Рассмотрим на запрос. Как видите в этой конструкции мы снова написали двойное условие по одному полю — price. И с одной строны всё логично и понятно, а с другой стороны эту конструкцию также можно упростить.
Теперь запрос звучит так: ВЫБРАТЬ все столбцы из таблицы products, в которых цена между 10000 и 20000. Звучит более чем понятно.
Вообще AND, OR, IN и BETWEEN — это основные конструкции для построения условий в SQL запросах и используются они в блоке WHERE. Вы можете применять их как по одиночке, так комбинируя в самых разлчных вариантах.
Возможность скачать видео
Функция ПОДСТАВИТЬ в Excel выполняет динамическую замену определенной части строки на указанное новое значение и возвращает новую строку, содержащую замененную часть текста. Благодаря этой функции можно подставлять значения из другой ячейки. Рассмотрим возможности функции на конкретных примерах в Excel.
| id | first_name | last_name | birthday | age | sex |
|---|---|---|---|---|---|
| 1 | Дмитрий | Иванов | 1996-12-11 | 20 | m |
| 2 | Олег | Лебедев | 2000-02-07 | 17 | m |
| 3 | Тимур | Шевченко | 1998-04-27 | 19 | m |
| 4 | Светлана | Иванова | 1993-08-06 | 23 | w |
| 5 | Олег | Ковалев | 2002-02-08 | 15 | m |
| 6 | Алексей | Иванов | 1993-08-05 | 23 | m |
| 7 | Алена | Процук | 1997-02-28 | 18 | w |
