Функция ВПР в Excel (на английском — VLOOKUP) по некоторому ключевому полю «подтягивает» данные из одного диапазона в другой. Ключевое поле должно присутствовать в обоих диапазонах данных (и там, куда «подтягиваем», и там, откуда берем данные).

Функция ВПР в Экселе: пошаговая инструкция

Представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Однако данные могут быть представлены не в столь удобном виде. Исходная информация может находиться в совершенно разных таблицах и в другом порядке. В первой таблице указаны количества проданных товаров:

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки.

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

Особенности использования формулы ВПР в Excel

Функция ВПР имеет свои особенности, о которых следует знать.

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.

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

5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.

После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».

Это позволит удалить сразу все внешние ссылки.

Примеры функции ВПР в Excel

Для следующих примеров использования функции ВПР возьмем немного другие данные.

Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.

Вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. Для отсутствующих позиций ВПР выдает ошибку #Н/Д.

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

Конструкция с функцией ЕСЛИОШИБКА

Вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д и вместо них возвращает некоторое значение. Обычно это 0 или пусто.

Как видно, ошибок больше нет, а вместо них пустые ячейки.

Разные форматы критерия в таблицах

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

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

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

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

Автоматически изменить формат критерия во второй таблице нельзя, т.к. ссылка ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает обязательное указание формата. Достаточно задать формат #. Ниже картинка с готовой формулой.

Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.

Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.

Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.

Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом, ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.

Подсчет номера столбца в большой таблице

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

Здорово экономит время.

Интервальный просмотр в функции ВПР

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

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

Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.

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

=ВПР(B2;$E$2:$F$5;2;1)

и скопируем вниз.

На рисунке ниже изображена схема, как работает интервальный просмотр функции ВПР.

Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.

Предлагаю посмотреть видеоурок о работе ВПР из курса «Основные функции Excel».

Поделиться в социальных сетях:

Проблемы с вычислением формул в Microsoft Excel

​Смотрите также​Столкнулся с подобной​ из др. программы.​ на общий и​ на режим отображения​ результат автоматически. Пока​ Текст по столбцам​ настройки вычисления книги​ лучше подходят для​ — 0.001).​ открытые или внедрённые​ без особого труда,​ заменить» (мы его​). В случае если​ автоматически пересчитываться при​ отображаться результат.​ выбора форматирования, где​Одной из наиболее востребованных​

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

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

​ после изменения данных​ изменении любого связанного​Формула также может отображаться​ можно выбрать значение,​ возможностей Excel является​ 1С УПП в​ ячеек с цифрами,​ фигня.​ То есть на​ клавишу F9 (или​ выбрать столбец с​ -> Параметры ->​Урок подготовлен для Вас​ между тремя основными​ таблиц данных. Для​ Вам как.​ ячейки выделены остались),​ в связанных ячейках​ значения.​ как текст, если​ соответствующее сути формулы.​

1. ​ работа с формулами.​ Excel 2010 большой​ в правом нижнем​А файл, к​ других листах при​​ SFIFT+F9).​​ формулами, вызвать команду,​ Вычисления, а также​​ командой сайта office-guru.ru​​ режимами вычислений, используя​ пересчета таблиц данных,​Если Вы не хотите​​ в строке «Найти»​​ результат не обновляется,​Если же программа все-таки​
2. ​ в её синтаксисе​Но выбор типов формата​ Благодаря данной функции​ массив данных (100​ углу обычно выдна​ сожалению, не выйдет​ необходимости потребуется выполнять​
3. ​Внимание! Быстрая клавиша F9​ и в открывшемся​ изменить формат ячеек​Источник: http://www.howtogeek.com/162219/how-to-change-the-automatic-calculation-and-multi-threading-features-in-excel-2013/​ команду​ когда данная опция​ отключать режим автоматических​ ставим точку. А​ то тут нужно​ производит расчет, но​ были допущены ошибки,​​ через ленту не​​ программа самостоятельно производит​ 000 с лишним​ сумма, а в​​ прислать и большой​​ эти действия повторно.​
4. ​ – выполняет пересчет​ мастере выбрать формат​​ с формулами через​​ Антон Андронов​​Calculation Options​​ выбрана, воспользуйтесь командой​ вычислений, и Ваш​ в строке «Заменить»​ посмотреть, как настроено​ в результате показывает​ например, пропущена или​ такой обширный, как​ различного рода расчеты​ тыс. строк). Затем​​ моем случае пишет:​​ и «тайный» XD.​
5. ​у меня такая проблема​ во всех формулах​ «Общий» для этого​ команду Данные ->​ Антон Андронов​​(Параметры вычислений) в​​Calculate Now​

​ компьютер имеет несколько​ ставим запятую. Нажимаем​ автообновление в параметрах​ ошибку, то тут​ изменена буква. Если​

​ через специализированное окно.​ в таблицах. Но​ обнаружил, что в​ «Количество: 2», т.е​buchlotnik​ с экселем: в​ книги на всех​​ столбца).​​ Текст по столбцам​

1. ​Почему-то вдруг перестали в​ разделе​​(Пересчет), расположенной на​​ процессоров, тогда можете​ «Выделить всё».​​ книги. Также, нередко​​ вероятна ситуация, что​​ вы вводили её​​ Поэтому лучше применить​ иногда случается так,​
2. ​ файле программа не​ кол-во выделенных ячеек.​: А вы сначала​ ячейки вбита простейшая​

​ листах. А Комбинация​ytk5kyky​ (для этого необходимо​ экселевской таблице пересчитаваться​Calculation​ вкладке​ применить режим​Получилось так.​ вместо корректного результата​​ пользователь просто ошибся​​ вручную, а не​ второй вариант форматирования.​ что пользователь вписывает​ суммирует в диапазоне​ Почему так может​ скопируйте формулу, потом​​ формула, которая при​​ горячих клавиш SHIFT+F9​

​: Если стоит формат​ выбрать столбец с​ результаты при подстановке​(Вычисление) на вкладке​Formulas​многопоточных вычислений​

​Убираем окно «Найти и​ в ячейке отображается​ при вводе выражения.​ через​ Выделяем целевой диапазон.​ формулу в ячейку,​ числа, а показывает​ быть и как​ поменяйте формат ячейки​ замене определенных данных​ – выполняет пересчет​ ячейки текстовый, то​

1. ​ формулами, вызвать команду,​​ в ячейки новых​​Formulas​(Формулы) или клавишей​. Он поможет немного​​ заменить».​​ ошибка. Тут нужно​
2. ​ Ошибочными формулами считаются​Мастер функций​​ Кликаем по нему​​ но она не​​ количество. Перепробовал все​​ решить проблему?​ на общий и​ должна соотвественно тоже​​ только на текущем​​ эксель всё введенное​ и в открывшемся​​ цифровых значений. Не​​(Формулы). Однако, если​F9​ ускорить процесс пересчета​Третий этап.​ просмотреть все значения,​ те, при расчете​, то такое вполне​ правой кнопкой мыши.​ выполняет своего прямого​ то, что знаю,​​Hugo​​ снова формулу вставьте​

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

​ необходимо настроить параметры​.​ в зависимости от​Как выделить первое число​ на которые ссылается​ которых в ячейке​ вероятно. Очень распространенной​ В контекстном меню​ назначения – вычисления​ а помогла формулка,​: Нажмите на этом​

• ​ в строку формул​
• ​ я меняю данные​
• ​Если на листе не​
• ​ Нужно сменить формат​
• ​ «Общий» для этого​

​ колонки цифр. Другие​ вычислений, все же​Manual​ количества процессоров компьютера.​ в ячейке​ функция. В случае​ появляются следующие значения:​ ошибкой, связанной с​ выбираем пункт​ результата. Давайте разберемся,​ которую привела Нимферия,​ «Количество: 2» ПКМ​

​ и ентер нажмите​ в исходной ячейке,​ содержится множество формул,​ ячейки и заново​ столбца).​

1. ​ формулы тоже не​ придется обратиться к​​(Вручную) – пересчитывает​​ О том, как​Excel​​ обнаружения ошибки следует​​#ЧИСЛО!;​​ отображением выражения, как​​»Формат ячеек»​
2. ​ с чем это​ за что ей​ и выберите что​​ — помогло?​​ то в в​ пересчет которых может​ ввести формулу.​

​ytk5kyky​ выдают измененного результата​ вкладке​ открытые рабочие листы​ включить режим многопоточных​.​ устранить её.​#ЗНАЧ!;​ текста, является наличие​. Можно также после​ может быть связано,​ спасибо.​ угодно.​Roman777​ ячейке, которой вбита​ тормозить Excel, то​Excel-ok​​: Если стоит формат​​ при изменении значений.​Formulas​ и обновляет открытые​ вычислений в Excel,​В ячейке В1​ Максим Тютюшев​#ПУСТО!;​ пробела перед знаком​ выделения диапазона нажать​ и как решить​Но я хочу​Вообще вопрос не​:​ формула все остается​ нет смысла использовать​: …например (лучше всего)​

​ ячейки текстовый, то​

lumpics.ru>

Не работают формулы в Excel.

​ В чем причина​(Формулы) диалогового окна​ или внедрённые диаграммы​ мы расскажем дальше.​​ пишем такую формулу.​ ​Бывает, скопировали таблицу​​#ДЕЛ/0!;​»=»​ комбинацию клавиш​ данную проблему.​ подсказать еще один​ из этой «оперы».​buchlotnik​ как и было,​ выше описанный пример.​ на «общий».​ эксель всё введенное​ и как исправить?​
​Excel Options​​ только при нажатии​ ​Для отключения режима автоматических​​ =ЛЕВСИМВ(A1;НАЙТИ(СИМВОЛ(10);A1)-1)​
​ в Excel, а​#Н/Д.​.​Ctrl+1​Скачать последнюю версию​ способ решения данной​Нимферия​, Плин, точняк, Спасибо,​ пока не щелкнешь​ Но на будущее​а потом зайти​ воспринимает как текст.​ Проверил форматы подставляемых​(Параметры Excel).​
​ команды​
​ вычислений откройте Excel​Этой формулой мы​
​ числа в ней​В этом случае нужно​В таких случаях нужно​.​
​ Excel​ проблемы. Попробуйте выгружать​: это «Количество:2» на​ помогло!​ по ней дважды​​ все же стоит​ в режим редактирования​
​ Нужно сменить формат​
​ значений — все​Excel 2013 располагает режимом​Calculate Now​ и выберите вкладку​ выделили из ячейки​ не считаются,​ проверить, правильно ли​ внимательно пересмотреть синтаксис​Открывается окно форматирования. Переходим​Причины проблем с вычислением​
​ из 1С не​
​ сером поле окна​markbonk​
​ мышкой.​
​ знать о такой​ ячейки (двойной щелчек​ ​ ячейки и заново​​ правильно.​
​ многопоточных вычислений, который​(Пересчет) или клавиши​File​
​ первое число. Копируем​формулы не работают в​ записаны данные в​ тех формул, которые​ во вкладку​
​ формул в Экселе​ через выделить-копировать-вставить, а​ эксель, на него​
​: Подскажите пожалуйста, есть​То же самое​ возможности. Ведь со​
​ на ячейке или​ ввести формулу.​Дмитрий к​ позволяет сокращать время​F9​(Файл).​ формулу по столбцу.​Excel​ ячейках, на которые​ неправильно отображаются, и​»Число»​ могут быть совершенно​ через файл-сохранить-сохранить как…​ жать бесполезно.​

excel-office.ru>

Как управлять режимами автоматических и многопоточных вычислений в Excel 2013

​ файл, в нем​ и с другими​ временем придется сталкиваться​ же через F2​Excel-ok​: Какой версии Excel?​ пересчета формул. Если​, а так же​Выберите пункт​ Получилось так.​. Одной из причин​ ссылается выражение, нет​ внести в них​. В блоке​

​ разными. Они могут​ И в уже​Проблема у меня​ не работают формулы.​ формулами. К примеру​ со сложными таблицами​ и просто нажать​: …например (лучше всего)​​Для 2007: Вкладка​​ Вы предпочитаете не​ при использовании комбинации​Options​В ячейке В4 мы​ может быть то,​ ли в них​

​ соответствующие коррективы.​»Числовые форматы»​ быть обусловлены, как​ готовom Excel файле​ та же, что​ Использую excel 2007.​​ простые формулы не​​ с множеством формул.​ ввод (Enter).​ на «общий».​ Формулы-Параметры вычислений-Автоматически​ отключать автоматическое вычисление,​ клавиши​(Параметры).​ написали формулу сложения.​

1. ​ что значения ячеек​ ошибок в синтаксисе​Бывает и такая ситуация,​​выбираем тот формат,​​ настройками конкретной книги​
2. ​ числа будут суммироваться​​ и в теме,​​ Что может быть​
3. ​ считаются, пока так​​ Кроме того данная​​:-)​а потом зайти​​Для 2003: Сервис-Параметры-Вичисления-Автоматически​​ попробуйте использовать указанную​Ctrl+F9​
4. ​В диалоговом окне​ =B1+B2+B3​ в текстовом формате.​ или не заложено​ что формула вроде​ который нам нужен.​ или даже отдельного​​ не смотря на​​ но не не​​ такое? Файл я​​ же не щелкнешь​ функция может быть​​gont​​ в режим редактирования​

Параметры вычислений

​Илья /​ опцию (при наличии​(только для активного​​Excel Options​​Автосумма не работает,​

• ​ Вспоминаем, в Excel​​ ли в самой​ и отображает значение,​ Кроме того, в​ диапазона ячеек, так​ пробелы.​ решается известными мне​ прикрепил.​ по ячейке их​ включена случайно и​: Спасибо большое! Разобрался.​ ячейки (двойной щелчек​
• ​: спасибо п. с.​ у компьютера нескольких​​ листа).​(Параметры Excel) выберите​ но, формулы, написанные​ есть два формата​ формуле какое-либо некорректное​ но при изменении​ правой части окна​ и различными ошибками​uzer​ способами в т.ч​​Z​​ содержащей дважды. Что​ нужно знать где​​ Программа не глючила:)​​ на ячейке или​​ в более новых​​ процессоров), чтобы сократить​
• ​Recalculate workbook before saving​​ вкладку​ вручную работают.​ – формат ячеек​ действие (например, деление​ связанных с ней​ имеется возможность выбрать​​ в синтаксисе.​​: Всем доброй ночи!))​​ указанными здесь и​​: =СУММ(A2:A501) дает 500.​ может быть? Эксель​ ее выключить для​​tttev​​ же через F2​ версиях (2010,2013…) там​
• ​ время пересчета.​​(Пересчитывать книгу перед​Formulas​В Excel можно​ и формат значений.​ на 0).​ ячеек сама не​ тип представления конкретного​Одной из самых распространенных​​ А у меня​​ в др. форумах.​Других формул нет.​ 2007г.​ стандартного режима работы.​: В седьмом Excel​ и просто нажать​
• ​ же где и​​Чтобы включить режим многопоточных​ сохранением) – пересчитывает​(Формулы), расположенную в​ установить в ячейку​ Подробнее об этом​Если функция сложная, с​ меняется, то есть,​ формата. После того,​ причин, по которой​ не получается ничего​ Могу выслать файл​:?:​Алексей матевосов (alexm)​​ — Наверху закладка​
• ​ ввод (Enter).​​ в 2007 :)​ вычислений, нажмите на​ открытые рабочие листы​ меню слева.​
• ​ ссылку на другой​​ читайте в статье​ большим количеством связанных​ результат не пересчитывается.​ как выбор произведен,​ Эксель вообще не​

​ с этими формулами.Та​ на личную электронку​markbonk​: Зайдите в «Параметры»​​В ячейках Excel мы​​ «Формулы». В ней​:-)​​Ed zelinsky​​ вкладку​​ и обновляет открытые​​Чтобы отключить пересчет данных​ лист, диапазон, ячейку,​ «Преобразовать дату в​ ячеек, то легче​ Это означает, что​​ кликаем по кнопке​​ считает или не​​ же проблема, раньше​​ тому, кто готов​

Многопоточные вычисления в Excel

​: Например в ячейке​ на вкладку «Вычисления»​ видим только результат​ снять галку с​gont​: Дмитрий К Профи!​File​ или внедрённые диаграммы​ при каждом изменении​ сайт, т.д. При​ текст Excel».​

1. ​ проследить вычисления с​ у вас неправильно​»OK»​​ правильно считает формулы,​​ через спец. вставку​ помогать.​​ E2 пишу =G2+G3.​​Поствте точку вычисления​ вычислений. Сами же​​ «Показать формулы»​​: Спасибо большое! Разобрался.​ спасибо огромное! помогло​​(Файл) и выберите​​ при их сохранении​
2. ​ значений, формул, имен​​ нажатии на эту​​Итак, рассмотрим,​ помощью специального инструмента.​​ настроены параметры вычислений​​, размещенной внизу.​ является неверно выставленный​ просто умножить на​Z​ И не алё..​ «автоматически»​ формулы можно увидеть​​Юрий М​ Программа не глючила:)​​ Для 2003: Сервис-Параметры-Вычисления-Автоматически!​ пункт​

​ даже при включенной​ или же при​ ссылку, Excel переходит​почему в​Выделяем ячейку с ошибкой.​ в данной книге.​

​Выделите поочередно ячейки, в​ формат ячеек. Если​ 1 делала. А​: «Огласите весь список,​китин​Станислав мостовой​

​ в строке формул​: Оперативно — и​
​tttev​
​ но почему сама​

​Options​

office-guru.ru>

Почему Excel перестал считать?

​ опции​ открытии листа Excel,​ по ней. Но,​Excel не работает формула​ Переходим во вкладку​Перейдите во вкладку​ которых функция не​ диапазон имеет текстовый​ тут ну никак​ пжлста!» (с)​: у вас все​: На ячейке щелкните​ (по отдельности). Но​ трёх лет не​: В седьмом Excel​

​ по-себе убралась галочка?​​(Параметры), чтобы открыть​
​Manual​ содержащего формулы, выберите​
​ как выделить саму​

​.​​»Формулы»​»Файл»​ считалась, и для​ формат, то расчет​ не считает «зараза»​

​Чтобы не идти​​ эти ячейки отформатированы​ правой кнопкой мыши​ иногда нам нужно​ прошло…​ — Наверху закладка​

​Наташа сердюк​​ диалоговое окно​

​(Вручную). Если Вы​​ опцию​

​ ячейку, не гиперссылку?​​Нужно сложить верхние​

​. На ленте в​​. Находясь в ней,​

​ пересчета нажмите функциональную​​ выражений в нем​ и так и​ проторенными тропами…​ как текст поэтому​ и выберите формат​ одновременно просмотреть все​

Эксель не воспринимает формулы

​Excel умолчанию пересчитывает все​​ «Формулы». В ней​: спасибо​Excel Options​ не хотите, чтобы​Manual​ Об этом читайте​ числа в ячейках.​ блоке инструментов​ следует кликнуть по​ клавишу​ вообще не производится,​ сяк уже(( Посмотрите,​Hugo​ и​

​ ячеек. Выберите формат​​ функции в ячейках​ формулы во всех​ снять галку с​Сетьпрофессиональныхзоомаркетов​(Параметры Excel). Затем​ при каждом сохранении​(Вручную) в разделе​ статью «Как выделить​ В ячейках значения​»Зависимости формул»​ пункту​F2​ то есть, они​ пожалуйста. Файл выгружаю​: Нимферия, да Вы​Z​

​ «Числовой» и задайте​​ (например, чтобы их​ листах всех открытых​ «Показать формулы»​: Спасибо!​ нажмите​ зависимые формулы и​Calculation options​

​ ячейку с гиперссылкой​​ написаны в текстовом​кликаем по кнопке​
​»Параметры»​.​ отображаются как обычный​ из спец. программы.​ не знаете, что​: А то как​ количество знаков в​
​ сравнить и т.п.).​

​ книг после каждого​​Юрий М​Галина аршинова​

​Advanced​​ диаграммы пересчитывались, просто​(Параметры вычислений). Затем​ в Excel».​ формате с установкой​»Вычислить формулу»​

​.​​Теперь будет производиться расчет​ текст. В других​vikttur​

planetaexcel.ru>

​ такое ПКМ!​​ по-другому?!. Формулы пишут​ числе.​Чтобы наглядно отобразить пример​ введения данных. Если​: Оперативно — и​: спасибо!​(Дополнительно).​ отключите данную опцию.​ нажмите​По умолчанию Excel автоматически​ между строками непечатаемых​.​Откроется окно параметров. Нужно​ формулы в стандартном​

​ случаях, если формат​​: Видите закрашенные уголки​18 лет?​ в ячейках, в​Скорее всего у​ данного урока нам​ лист содержит сотни​ трёх лет не​Наталья​Опуститесь вниз до раздела​Enable iterative calculation​ОК​ пересчитывает все формулы​ знаков. Нам нужно​Открывается окно, в котором​ перейти в раздел​ порядке с выводом​

​ не соответствует сути​​ в ячейках?​Нимферия​ которых формат -​ Вас неправильный формат.​ потребуется лист содержащий​ или тысячи формул​ прошло…​

​: Спасибо, помогло!)​​Formulas​(Включить итеративные вычисления)​
​.​ при открытии рабочего​ преобразовать текстовый формат​ представляется полный расчет.​»Формулы»​ результата в указанную​ рассчитываемых данных, результат,​
​Выделить все чейки,​

​: мне 7 лет,​​ угадаем какой?! И​ Удачи.​

​ формулы:​​ автоматический пересчет начинает​gont​Наталья соснина​(Формулы) и установите​ – разрешает итеративные​

​Следующий список поясняет опции,​​ листа или при​ значений в числовой​ Нажимаем на кнопку​

planetaexcel.ru>

Автоматический пересчет формул в Excel и вручную

​. В блоке настроек​ ячейку.​ выводимый в ячейку,​ нажать появившуюся пикторграмму,​ если это имеет​ вообще — каждым​Roman777​Изменим настройки Excel так,​ заметно замедлять процесс​: Подскажите пожалуйста, какие​: Большое спасибо! Очень​ флажок возле пункта​ вычисления, т.е. позволяет​ которые доступны в​

​ изменении значений, формул​ и выделить первое​»Вычислить»​»Параметры вычислений»​Но возможно причина того,​

1. ​ может отображаться некорректно.​ выбрать «преобразовать в​ отношение к вопросу.​ данным желательно свой​
2. ​: Добрый день!​
3. ​ чтобы ячейки отображали​ работы с программой.​ бывают причины того,​ помогло! Сколько работаю,​Enable multi-threaded calculation​ задавать предельное количество​ разделе​ или имен в​ число в ячейке.​и просматриваем вычисление​, который расположен в​

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

​ такого еще не​(Включить многопоточные вычисления).​ итераций и относительную​Calculation options​ зависимых формулах. Процесс​ У нас такая​ пошагово. Ищем ошибку​ самом верху окна,​ расчета у вас​ решить эту проблему.​Да и​ пробелы. проблему решила​ps И некоторые​ =ВПР(;;;0), а он​ результат их вычисления.​ настроить Excel для​ реагировать на знак​ было. Но правда,​ Вы можете вручную​

​ погрешность вычислений, когда​

​Чтобы добиться данного результата,​ повышения его производительности​ =. Не считает,​ а почему исчезла​

​ установить количество потоков​ формулы будут пересчитываться​Automatic​ от нескольких секунд​

​ 1С.​Как видим, причины того,​»Вычисления в книге»​ в том, что​ какой формат имеет​помогает.​ которую нашла в​

​ таблице без «шапок»​ оставляет надпись в​ нужно выбрать инструмент:​ и беспрепятственной работы.​ а отображает в​ галочка?​ вычисления, которое необходимо​ при подборе параметра​(Автоматически) – пересчитывает​ до нескольких минут​

​Первый этап.​ что Эксель не​, переключатель не установлен​ в программе включен​ конкретная ячейка или​jakim​ др. форуме.​ — заголовков -​

exceltable.com>

Что с excel может быть? формулы в ячейках не считают.

​ ячейке, аналогично и​ «Формулы»-«Показать» (в разделе​Для книги, которая содержит​ ячейке написанную формулу(режим​gont​ использовать, или указать​ или при использовании​ все зависимые формулы​ в зависимости от​Преобразуем значение ячеек в​ считает или не​ в позицию​ режим​ диапазон, переходим во​:​не поняла как​
​ гарантия головной боли​ с другими формулами.​ «Зависимости формул»). Чтобы​ сотни сложных формул​ отображения формул отключен).​: Подскажите пожалуйста, какие​ Excel задействовать все​ циклических ссылок. Более​ и обновляет все​ количества и сложности​

​ числовой формат.​​ правильно считает формулы,​»Автоматически»​
​»Показать формулы»​ вкладку​

​Ctrl+h​​ здесь работает СУММПРОИЗВ,​ с фильтрами и​ Что не так,​ выйти из этого​ можно настроить пересчет​ Работаю-работаю, пишу, считаю,​ бывают причины того,​
​ процессоры компьютера, выбрав​ детальную информацию о​ открытые или внедрённые​

Эксель не считает, а тупо пишет формулу (Настройки Excel/Setup)

​ формул в рабочей​​Выделяем ячейки. На​
​ могут быть совершенно​, то это и​.​»Главная»​найти запятую и​ но без нее​ сортировкой…​ где стоит покопаться,​ режима нужно выбрать​ по востребованию пользователя.​

​ вдруг бах, начинаются​​ что эксель перестает​Use all processors on​ подборе параметров и​ диаграммы при любом​

​ книге.​​ закладке «Главная» в​​ различными. Если вместо​​ есть причина того,​Чтобы включить отображение итогов,​. На ленте в​ заменить все запятые​ не работает.​markbonk​
​ чтобы он вычислял​ данный инструмент повторно.​ Для этого:​ проблемы с восприятием​

​ реагировать на знак​​ this computer​ использовании циклических ссылок​ изменении значения, формулы​Пока формулы пересчитываются, указатель​ разделе «Редактирование» нажимаем​ расчета у пользователя​ что результат вычислений​ переходим во вкладку​

​ блоке инструментов​​Hugo​​спасибо сложные люди,​​: Спасибо​ формулы?​

excelworld.ru>

Не работают формулы в файле

​Так же можно использовать​​Введите формулу на чистый​ формул. То ли​ =. Не считает,​(Использовать все процессоры​ можно найти в​ или имени. Данная​ мыши меняется на​

​ на кнопку «Найти».​​ отображается сама функция,​
​ неактуальный. Переставляем переключатель​
​»Формулы»​

​»Число»​​: У меня сработало​ от которых я​Z​

​buchlotnik​​ комбинацию горячих клавиш​ лист (чтобы можно​ программа глюкает, то​ а отображает в​

​ данного компьютера).​​ справке Microsoft Excel.​ настройка установлена по​ песочные часы, и​В появившемся диалоговом​ тот в этом​ в нужную позицию.​. На ленте в​имеется поле отображения​
​ и стандартно «Данные​ крепчаю!​: Кроссмен, однако -​: а у ячейки​ CTRL+` (над клавишей​ было проверить как​ ли я туплю.​

​ ячейке написанную формулу(режим​​Если на компьютере уже​

​Maximum Iterations​​ умолчанию для каждого​ внести какие-либо изменения​

​ окне в строке​​ случае, скорее всего,​ После выполнения вышеуказанных​ блоке инструментов​ текущего формата. Если​ — ткст по​Юрий М​ …​ случаем не текстовый​ Tab). Данная комбинация​ работает данный пример).​voam​ отображения формул отключен).​ запущены другие программы,​(Предельное число итераций)​ нового рабочего листа​ на рабочий лист​ «Найти» ставим запятую».​ либо ячейка отформатирована​ настроек для их​

​»Зависимости формул»​​ там указано значение​ столбцам — готово»,​: Офигеть! )) Единичка​Нимферия​
​ формат? файлик бы​ работает в режиме​

​Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».​​: Попробуйте во-первых проверить​ Работаю-работаю, пишу, считаю,​ Вы можете ограничить​ – определяет максимальное​
​ Excel.​ невозможно. Для экономии​ В строке «Заменить»​ под текст, либо​ сохранения в нижней​, если кнопка​»Текстовый»​ т.к. у меня​ пропущена или со​: у меня такая​ посмотреть​ переключателя, то есть​

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

​Automatic except for data​​ времени можно временно​ ничего не ставим.​ включен режим просмотра​
​ части окна жмем​

​»Показать формулы»​​, то формула точно​ и так разделитель​столь​
​ же проблема, но​Roman777​ повторное нажатие возвращает​ ввода данных в​ в меню Сервис​
​ проблемы с восприятием​ для пересчета формул​ умолчанию — 100).​ tables​
​ отключить режим​Нажимаем кнопку «Заменить всё».​ выражений. Также, возможна​

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

​:​​ в обычный режим​ ячейку (например, число​
​ -> Параметры ->​ формул. То ли​ электронной таблицы Excel.​Maximum Change​(Автоматически, кроме таблиц​автоматических вычислений​ Получится так.​ ошибка в синтаксисе​»OK»​ по ней.​Для того, чтобы произвести​Только нужно выделять​ изучение Excel?​ в числовые значения​buchlotnik​ отображения результатов вычисления​ 7 вместо 1​
​ Вычисления, а также​ программа глюкает, то​Испытайте разные настройки режимов​(Относительная погрешность) –​ данных) – пересчитывает​в Excel до​Второй этап.​ (например, наличие пробела​.​После этих действий в​ смену формата достаточно​ конкретно диапазон с​

​Mandor​​ проблема не решается.​, Формат посмотрел -​ в ячейках.​ в ячейке A2​ изменить формат ячеек​ ли я туплю.​ автоматических и многопоточных​ устанавливает максимально допустимую​ все зависимые формулы​ завершения внесения данных.​В этом же​ перед знаком​Теперь все выражения в​ ячейках снова вместо​

​ кликнуть по данному​​ этими числами, не​: Вот нам и​
​ дело в том,​ был текстовый, я​Примечание. Все выше описанные​ как на рисунке),​
​ с формулами через​​voam​​ вычислений, чтобы определить,​

​ разницу между результатами​​ и обновляет все​​ Сделать это можно​​ окне «Найти и​»=»​

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

planetaexcel.ru>

​ пересчета (по умолчанию​

• Формула смещ в excel примеры
• Excel в формуле не равно
• Формулы для работы в excel
• Excel показывает формулу вместо значения
• Excel формула или
• Как поставить плюс в excel без формулы
• Составление формул в excel
• В excel минус формула
• Как в excel скопировать значение ячейки а не формулу
• Как в excel изменить вид формулы
• Формула в excel сумма минус сумма
• Формула расчета аннуитетного платежа в excel

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

В Microsoft Excel отображение значений в виде процентов делается очень просто. Для этого нужно выделить одну или несколько ячеек и нажать кнопку Percent Style (Процентный формат) в разделе Number (Число) вкладки Home (Главная):

Ещё быстрее это можно сделать, нажав сочетание клавиш Ctrl+Shift+%. Excel будет напоминать Вам эту комбинацию каждый раз, когда Вы наводите указатель мыши на кнопку Percent Style (Процентный формат).

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

• Форматирование существующих значений как процентов
• Применение процентного формата к пустым ячейкам
• Отображение чисел как процентов сразу при вводе
• Хитрости при отображении процентов в Excel

Форматирование существующих значений как процентов

Когда Вы применяете Процентный формат к ячейкам, которые уже содержат числовые значения, Excel умножает эти значения на 100 и добавляет в конце знак процента (%). С точки зрения Excel, это правильно, так как 1% это по своей сути одна сотая часть.

Однако иногда это приводит к неожиданным результатам. Например, если в ячейке A1 записано число 20 и Вы примените к этой ячейке Процентный формат, то в результате Вы получите 2000%, а не 20% как Вы, вероятно, хотели.

Как предотвратить ошибку:

• Если в ячейке Вашей таблицы записаны числа в обычном числовом формате, а Вам нужно превратить их в Процентный, предварительно разделите эти числа на 100. Например, если Ваши исходные данные записаны в столбце A, Вы можете в ячейку B2 ввести формулу =A2/100 и скопировать её во все необходимые ячейки столбца B. Далее выделите весь столбец B и примените к нему Процентный формат. Результат должен быть примерно такой:Далее Вы можете заменить формулы в столбце B значениями, затем скопировать их в столбец A и удалить столбец B, если он Вам больше не нужен.
• Если Вам нужно перевести в формат процентов только некоторые из значений, Вы можете ввести их вручную, для этого надо разделить число на 100 и записать его в виде десятичной дроби. Например, чтобы получить значение 28% в ячейке A2 (смотрите рисунок выше), введите число 0.28, а потом примените к ней Процентный формат.

Применение процентного формата к пустым ячейкам

Мы увидели, как изменяется отображение уже существующих данных в таблице Microsoft Excel при смене простого числового формата на Процентный. Но что получится, если сначала применить к ячейке Процентный формат, а затем ввести в неё число вручную? Вот тут Excel может повести себя по-разному.

• Любое число равное или большее 1 будет просто записано со знаком %. Например, число 2 будет записано как 2%; 20 – как 20%; 2,1 – как 2,1% и так далее.
• Числа меньше 1, записанные без 0 слева от запятой, будут умножены на 100. Например, если Вы введёте ,2 в ячейку с процентным форматированием, то в результате увидите значение 20%. Однако, если Вы наберёте на клавиатуре 0,2 в той же ячейке, то значение будет записано как 0,2%.

Отображение чисел как процентов сразу при вводе

Если Вы вводите в ячейку число 20% (со знаком процента), Excel поймёт, что Вы хотите записать значение в процентах и автоматически изменит формат ячейки.

Важное замечание!

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

Другими словами, 20% хранится как 0,2; 2% хранится как 0,02 и так далее. Когда производятся различные вычисления, Excel использует именно эти значения, т.е. десятичные дроби. Помните об этом, когда составляете формулы со ссылками на ячейки с процентами.

Чтобы увидеть реальное значение, содержащееся в ячейке, которая имеет Процентный формат:

1. Щелкните по ней правой кнопкой мыши и из контекстного меню выберите Format Cells (Формат ячеек) или нажмите комбинацию Ctrl+1.
2. В появившемся диалоговом окне Format Cells (Формат ячеек) взгляните в область Sample (Образец) на вкладке Number (Число) в категории General (Общий).

Хитрости при отображении процентов в Excel

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

Задайте отображение нужного количества десятичных знаков

Когда Процентный формат применяете к числам, Excel 2010 и 2013 отображает их округлённое до целого значение, и в некоторых случаях это может ввести в заблуждение. Например, задайте процентный формат для пустой ячейки и введите в ней значение 0,2%. Что получилось? Я в своей таблице вижу 0%, хотя я точно знаю, что должно быть 0,2%.

Чтобы увидеть реальное, а не округлённое значение, Вам нужно увеличить число десятичных знаков, которое Excel должен показать. Для этого:

1. Откройте диалоговое окно Format Cells (Формат ячеек), воспользовавшись контекстным меню, или нажмите комбинацию клавиш Ctrl+1.
2. Выберите категорию Percentage (Процентный) и установите число десятичных знаков, отображаемых в ячейке, по своему желанию.
3. Когда все будет готово – нажмите ОК, чтобы изменения вступили в силу.

Выделите отрицательные значения при помощи форматирования

Если Вы хотите, чтобы отрицательные значения отображались по-другому, например, красным шрифтом, можете настроить особый числовой формат. Откройте еще раз диалоговое окно Format Cells (Формат ячеек) и зайдите на вкладку Number (Число). Выберите категорию Custom (Все Форматы) и введите в поле Type (Тип) одну из следующих строк:

Более подробно о таком способе форматирования Вы можете узнать в справочнике Microsoft, в теме, посвящённой отображению чисел в процентном формате.

Форматирование отрицательных значений процентов в Excel с помощью условного форматирования

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

Простейший способ создать правило условного форматирования – зайти в меню Conditional formatting > Highlight cells rules > Less than (Условное форматирование > Правила выделения ячеек > Меньше…) и ввести 0 в поле Format cells that are LESS THAN (Форматировать ячейки, которые МЕНЬШЕ)

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

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

Оставайтесь с нами и приятного Вам чтения!

Оцените качество статьи. Нам важно ваше мнение:

Хитрости » 11 Январь 2015 Дмитрий 56603 просмотров

В который раз на одном из форумов увидел вопрос с описанием непонятного бага: при попытке изменить формат ячеек или вызывать диалоговое окно Формат ячеек(правая кнопка мыши-Формат ячеек (Cell Format) ничего не происходит. Что делает невозможным изменить визуальный стиль ячеек. При этом происходить данная проблема может не для всех ячеек, а выборочно для любой ячейки листа. Если кто-то не понял проблему: для некоторых ячеек невозможно назначить или изменить границы ячеек, цвет заливки, цвет и стиль шрифта и т.п.

Возможная причина: в какой-то момент при работе с файлом в ячейках возник конфликт форматов и стиль ячеек был заблокирован. Как правило это происходит при работе с файлом в версиях Excel 2007 и выше. Я предполагаю, что связано это с тем, что в новых форматах файлов данные о форматах ячеек хранятся в XML-схемах и порой при изменениях происходит конфликт стилей и Excel не может определить текущий формат ячейки и как следствие изменить. Не могу сказать 100% действительно ли в этом причина, но других вариантов у меня пока нет. Надо узнавать у Microsoft, но никто из «владельцев» проблемы не может сказать в какой момент причина появилась, а без этого вряд ли получиться что-то нарыть. Сам я смоделировать подобную ситуацию не смог.

Решение: я пока знаю два возможных варианта решения данной проблемы. Какой из них использовать, если вы столкнулись с данной проблемой — выбирать вам.

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

вариант 1

1. Если книга в формате .xls — сохраняете книгу в формате 2007(Сохранить как-Книга Excel (*.xlsx) (Save As- Excel Workbook (*.xlsx))
2. закрываете книгу
3. меняете расширение для книги на .zip или .rar
   Если не отображается расширение:
   Панель управления-Свойства папки(для Win 7 – Параметры папок)- вкладка Вид- Снять галочку с «Скрывать расширение для зарегистрированных типов файлов»
4. открываете любым архиватором
5. в архиве будет три папки: _rels, docProps и xl
6. открываете папку xl и удаляете файл Styles.xml
7. закрываете архив
8. меняете расширение архива на первоначальное — .xlsx
9. открываете книгу, согласившись с восстановлением содержимого:
   после чего получить небольшое сообщение об удаленных стилях, которые не удалось восстановить:

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

Вариант 2

1. копируете любую ячейку, формат для которой изменяется без проблем
2. выделяете «непослушные» ячейки-правая кнопка мыши-Специальная вставка (PasteSpecial) — Форматы (Formats)

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

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx

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

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

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; )

лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

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

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

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

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

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

Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

Как видно, запись формулы выглядит гораздо проще и понятнее.

Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.

Поделиться в социальных сетях: