Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти значение и вернуть ячейку выше или ниже в Excel?

Как найти значение и вернуть ячейку выше или ниже в Excel?

Как правильно организовать в Excel проверку введенных данных

Пользователи Excel часто сталкиваются с ошибками ввода. В результате ошибочных данных ячейки с формулами на нескольких рабочих листах могут быть заполнены ошибками, такими как #NAME , #H/D и т.д., одна за другой. Поиск ошибок может занять много времени, поскольку первоначальный источник ошибки неясен. С чего начать? Даже если в ячейках есть ошибки, они отображаются нормально.

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

НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL

Excel поставляется со стандартной функцией проверки данных, введенных в ячейки. Она устанавливается через меню Данные -> Проверить данные. Таким образом, появляется диалоговое окно Проверка введенных значений с тремя вкладками.

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

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

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

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

1. Элементы управления вводом данных по умолчанию могут создать у пользователя вредную иллюзию, что они безотказны. В частности, если вы не сами ввели данные, а кто-то другой, и вы ожидаете, что все будет в порядке, значит, система уловила ошибки. Фактически, проверка работает только при ручном вводе данных, но не работает при вводе данных любым другим способом!

Читайте так же:
Как изменить цвет зачеркивания без изменения цвета текста в Excel?

Например, если данные вводятся в ячейку с помощью Copy->Paste, Copy->Paste-Special Values или «укладки» из соседних ячеек, проверка данных не производится. Аналогично, значения ячеек не проверяются, если они введены в ячейку с помощью макроса (если только проверка не выполняется в самом макросе).

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

Загрузка данных из других программ, таких как 1С, часто приводит к этой проблеме. Но особенно неприятно, когда ошибки незаметны или едва заметны. Например:

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

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

2. Стандартные способы проверки могут "тормозить"

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

В этом случае часто используются «тяжелые» функции СЧЕТЕСЛИ, СЧЕТЕСЛИМН, которые подсчитывают количество или сумму по уже введенным ранее данным и сравнивают, не превысит ли вводимое значение какой-то предел. Всякий раз, когда вы вводите или изменяете значение в ячейке, отрабатывает эта "тяжелая" формула. Если при этом с ячейкой связано много других ячеек, будут пересчитываться и они. Когда вы это делаете и тестируете в таблицах мало данных и задержек не ощущается.

Читайте так же:
Как изменить отрицательное число на ноль в Excel?

Когда таблицы растут, Excel может стать нереагирующим, то есть не успевать за вводом данных пользователем. А это очень раздражает. Для обеспечения согласованности следует очень осторожно использовать проверку вводимых данных с помощью типа «Другой» и «тяжелых» формул. Этого типа техники следует вообще избегать.

3. Бесполезный инструмент "Обвести неверные данные"

Для проверки правильности значений, введенных в Excel, существует встроенный инструмент «Заполнение ошибочных данных», доступ к которому можно получить через меню Данные -> Заполнение ошибочных данных.

Excel works!

menu

Самая полезная функция электронных таблиц Excel — автоматическое заполнение ячеек. Если вам нужно заполнить определенное значение (от 1 до 10), последовательность (например, дни) или формулу в большом количестве ячеек, строка за строкой, это можно сделать почти автоматически. Читать далее.

автозаполнение ячеек

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

1. Простое автозаполнение ячеек

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

автозаполнение ячеек

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

2. Создание собственного списка автозаполнения ячеек

Вы можете создать любую последовательность для индивидуального заполнения. Нажмите на круглую кнопку слева вверху (Файл) — Параметры — Дополнительно — Изменить списки. Добавьте свой список в появившемся окне. Здесь можно создавать текстовые, числовые или смешанные последовательности. После добавления последовательности она будет доступна для метода из шага 1.

3. Как быстро растащить автозаполнение

Если вы записали формулу в ячейку В1, и вам надо продлить ее до конца диапазона (в столбце А у вас уже есть значения, например в ячейках А1:А50), наведите на нижний правый угол ячейки до появления крестика и теперь кликните два раза левой кнопкой — ваша формула автоматически продлилась до ячейки В50, т.е. до конца диапазона в столбце А. Эта функция действует только если заполнен соседний столбец слева или справа, причем в этих диапазонах нет пустых ячеек (разрывов)! Особенно полезна эта возможность, если в вашей таблице тысяча и более строк!

Читайте так же:
Как запустить один и тот же макрос на нескольких листах одновременно в Excel?
4. Как быстро заполнить формулой ячейки в столбе в 10 тыс строк

Моя любимая хитрость. Предположим, что у нас пример из пункта 3, но необходимо заполнить формулу в столбце В для 50 000 ячеек, при этом ни в левом ни в правом диапазоне нет сплошных данных. Пункт 3 не поможет. Что делать? Хватать крестик и тащить вниз как в пункте 1? Неет, это долго. Сперва копируйте формулу из ячейки В2 (ctrl+c). Затем находите конец диапазона в соседнем столбец (для примера ячейка А50000), либо мышью, либо сочетанием клавиш ctrl + стрелка вниз. Выделяем ячейку в столбце, где нужно заполнить формулу т.е. В50000. Нажимаем ctrl + shift + стрелка вверх — таким образом выделятся все данные в столбце В выше ячейки В50000. Теперь вставляем формулу, которую мы скопировали в самом начале.

5. Сохранить форматы при протягивании формул

Чтобы сохранить формат при протягивании ячеек, существует специальная функция «Параметры автозаполнения». Делаем автозаполнение, жмем на появившуюся кнопку и выбираем Заполнить только форматы. Очень удобно!

Функция ЕСЛИ в Excel

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

Log_expressions может относиться к любому значению, формуле или выражению, которое при оценке выдает результат TRUE или FALSE.

Возвращает значение, если Log_expression имеет значение TRUE.

Значение_if_false — это значение, которое возвращается, если булево выражение имеет значение FALSE.

Например:
=ЕСЛИ(A1<>””;1;0)
<> – не равно,
“” – пустая.
То есть, если ячейка А1 не пустая, возвращается 1, если пустая, то 0.
При этом не важно будет ли в ячейке А1 число, текст или любые другие знаки.

Читайте так же:
Как затенять пустую ячейку, пока что-то не будет введено в Excel?

Допустим нам нужно, за заказ свыше 15000 руб применить скидку 10%.
В поле Лог_выражение напишем условие: сумма заказа(В2) больше 15000. Пропишем в Значениe_если_истина В2*0,9, а в Значениe_если_ложь вернем сумму заказа (В2).

Функция ЕСЛИ с 1 условием

Примените эту формулу к остальным заказам.

Пример использования функции ЕСЛИ

Неправильное построение вложенных функций ЕСЛИ

Часто бывает, что необходимо применить более 1 условия.
Допустим нам надо при сумме заказа свыше 15000 руб, сделать скидку 10%, а свыше 20000 руб 15%.

Excel принимает до 7 уровней встраивания функций и проверяет условия с первого.

Например, мы напишем формулу неправильно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

То есть, если мы напишем первое условие B2>15000, то скидка 15% не будет применяться к суммам более 20000.

При проверке в Excel выражения Log_expression (B4>15000) ячейки B4, Excel определит, что это выражение корректно, и применит скидку 10%.

Ошибка в построении функции ЕСЛИ

Наш первый заказ будет больше на 20%.

Как построить вложенные функции ЕСЛИ

С помощью мастера функций выберите функцию IF и заполните поля Логическое выражение и Значение_если_истинно.

Лог_выражение напишем В2>20000
Значение_если_истина – В2*0,85
В пустое поле Значение_если_ложь необходимо встать курсором и в поле имени (слева от строки формул) выбрать функцию ЕСЛИ.

Построение вложенных функций ЕСЛИ

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

Функция ЕСЛИ с 2-мя условиями

В новом окне введите следующее условие:

Построение функции ЕСЛИ с 2-мя условиями с помощью мастера функций

Теперь эту формулу можно распространить на остальные заказы.

Пример использования функции ЕСЛИ с 2-мя условиями

Функция ИЛИ (результат изменяется при выполнении одного из нескольких условий)

Добавим к нашему примеру еще одно условие:
Если заказ оплачен более чем на 50%, предоставляется скидка тоже 10%.
Для наглядности занесем все наши условия в таблицу

В первой функции IF ничего не изменится.

Нажмите кнопку Вставить функцию (слева от строки формул) рядом со второй функцией IF.

Появятся аргументы второго условия ЕСЛИ.
Стереть поле Лог_выражение (В2>15000)

Выберите функцию OR из поля Name в поле Log_expression.

В поле имени отображаются 10 последних использованных функций. Если она недоступна, нажмите кнопку Другие функции и выберите функцию ИЛИ из списка формул.

Читайте так же:
Как изменить отрицательные числа на положительные в Excel?

В появившемся окне заполните, пожалуйста, наши 2 условия:

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда вы начинаете печатать вторую строку, третья строка появляется автоматически. Оставьте его пустым и нажмите OK.

Проведем формулу для оставшихся заказов

Функция И (результат изменяется при выполнении нескольких условий)

Скидки за заказ оставим на прежнем уровне:
Заказ более 20000 – 15%
Заказ более 15000 – 10%

Но если заказ более 15000 и оплачен более чем на 50% – скидка 20%

Для наглядности давайте поместим все наши условия в таблицу

Функция первого ПЧ не изменяется.

В строке формул слева от второй функции IF нажмите кнопку Insert Function.

В появившемся окне стираем Лог_выражение: B2>15000.

Установите курсор в поле Имя пустого поля Log_expression и выберите функцию AND.

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

В появившемся окне введите наши 2 условия

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда вы начинаете печатать вторую строку, третья строка появляется автоматически. Оставьте его пустым и нажмите OK.

При нажатии кнопки OK окно закрывается.

Второе условие IF содержит только первую строку (Log_expression). Далее нажимаем кнопку Вставить функцию (слева от строки формул) второго условия IF.

В первой строке (Лог_выражение) уже прописаны 2 наших условия.
Значение_если_истина исправляем 0,9 на 0,8, чтобы при соблюдении 2-х условий Excel считал скидку 20%
Значение_если_ложь стираем B2 (будем прописывать в нее 3-е условие).

В поле имя выберите функцию ЕСЛИ под именем Value_if_lie.

В новом окне появится третье условие.

Вылейте оставшиеся заказы в смесь.

В следующем примере мы применим функции IF в следующем порядке.

Если мы напишем условие 2 как заказ больше 15000 — 10%, а условие 3 как выполнение двух условий, Excel определит условие 2 как истинное и не будет проверять условие 3.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector