Remontnouta.ru

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

Проблемы с формулами в таблице Excel

Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.

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

Формулы в ячейках Эксель в текстовом формате

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

Формулы в Эксель в формате даты

Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:

  1. Чтобы определить текущий формат ячейки (диапазон ячеек), выделяем ее и, находясь во вкладке “Главная”, обращаем вниманием на группу инструментов “Число”. Здесь есть специальное поле, в котором показывается формат, используемый сейчас.Формулы в ячейках Excel в текстовом формате
  2. Выбрать другой формат можно из списка, который откроется после того, как мы кликнем по стрелку вниз рядом с текущим значением.Выбор формата для выделенных ячеек в Эксель

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

  1. Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.Переход в окно форматирования ячеек через контекстное меню в Excel
  2. В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.Выбор формата в окне форматирования ячеек в Эксель
  3. Чтобы изменения отразились в таблице, по очереди активируем режим редактирования для всех ячеек, в которых формула не работала. Выбрав нужный элемент перейти к редактированию можно нажатием клавиши F2, двойным кликом по нему или щелчком внутри строки формул. После этого, ничего не меняя, жмем Enter.Редактирование формулы в Эксель

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

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

Ввод формулы, ссылающейся на значения в других ячейках

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

Введите знак равенства (=).

Щелкните первую ячейку, которую требуется включить в вычисление.

Пример использования ссылки на ячейку в формуле

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

Пример использования оператора в формуле

Щелкните следующую ячейку, которую нужно включить в вычисление. Теперь формула должна выглядеть так:

Читайте так же:
В excel дата меняется на число

Пример использования двух ссылок на ячейки в формуле

Нажмите клавишу RETURN.

В ячейке отобразится результат вычисления.

Пример использования ссылок на ячейки в формуле, показывающей вычисленный результат

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

Использование функций для вычислений

Excel предлагает большое количество функций, которые разбиты по категориям. Посмотреть полный перечень можно, нажав на кнопку Fx около строки формул или открыв раздел «Формулы» на панели инструментов.

Расскажем о некоторых функциях.

Как задать формулы «Если» в Excel

Эта функция позволяет задавать условие и проводить расчет в зависимости от его истинности или ложности. Например, если количество проданного товара больше 4 пачек, следует закупить еще.
Чтобы вставить результат в зависимости от условия, добавим еще один столбец в таблицу.

В первой ячейке под заголовком этого столбца установим указатель и нажмем пункт «Логические» на панели инструментов. Выберем функцию «Если».

Как и при вставке любой функции, откроется окно для заполнения аргументов.

Укажем условие. Для этого необходимо щелкнуть в первую строку и выбрать первую ячейку «Продано». Далее поставим знак «>» и укажем число 4.

Во второй строке напишем «Закупить». Эта надпись будет появляться для тех товаров, которые были распроданы. Последнюю строку можно оставить пустой, так как у нас нет действий, если условие ложно.

Нажмите ОК и скопируйте запись для всего столбца.

Чтобы в ячейке не выводилось «ЛОЖЬ» снова откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строки формул. Вставьте курсор на третью строку и поставьте пробел в кавычках.

Затем ОК и снова скопируйте.

Теперь мы видим, какой товар следует закупить.

Формула текст в Excel

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

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

В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».

Нажмем ОК и скопируем.

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

«ЗНАЧ» обозначает, что вычисления не могут быть произведены.
Примеры форматов вы можете видеть на скриншоте.

Формула даты в Excel

Excel предоставляет много возможностей по работе с датами. Одна из них, ДАТА, позволяет построить дату из трех чисел. Это удобно, если вы имеете три разных столбца – день, месяц, год.

Поставьте указатель на первую ячейку четвертого столбца и выберите функцию из списка «Дата и время».

Расставьте адреса ячеек соответствующим образом и нажмите ОК.

Скопируйте запись.

Читайте так же:
Можно ли ездить на вареных дисках

Автосумма в Excel

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

Перейдите на панель «Формулы» и нажмите «Автосумма».

Excel автоматически выделит ближайший числовой диапазон.

Вы можете выделить другой диапазон. В данном примере Excel все сделал правильно. Нажмите ОК. Обратите внимание на содержимое ячейки. Функция СУММ подставилась автоматически.

При вставке диапазона указывается адрес первой ячейки, двоеточие и адрес последней ячейки. «:» означает «Взять все ячейки между первой и последней. Если вам надо перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)

Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

  1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант: Исходный прайс-лист.
  2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями. Формула для стоимости.
  3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

Автозаполнение формулами.

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки аргументы.

Ссылки в ячейке соотнесены со строкой.

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

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

  1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9 Диапазон.
  2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование». Инструмент Сумма.
  3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.
Читайте так же:
Буквенная нумерация столбцов в excel

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

  1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной. Формула доли в процентах.
  2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5 Процентный формат.
  3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

  • $В$2 – при копировании остаются постоянными столбец и строка;
  • B$2 – при копировании неизменна строка;
  • $B2 – столбец не изменяется.

Создание формул в Excel

Для обработки значений и их диапазонов — от банальной суммы до методов математической статистики вроде медианы или моды — используются формулы. Это очень гибкий и при этом достаточно простой инструмент, который широко используется наряду с условным форматированием при создании таблиц с данными. В самом простом случае для записи формулы в ячейку используется обычный текстовый ввод. Чтобы провести, например, сложение значений из нескольких полей, можно прямо в ячейке написать «=СУММ()» и в скобках указать адреса значений через точку с запятой. Или, если они расположены рядом друг с другом, указать начальную и конечную через двоеточие. А чтобы закрепить в этой формуле ячейку, например, чтобы не менялись столбец или строка при копировании формулы в другую, поставьте перед нужным символом знак «$». Тогда Эксель не будет менять этот экранированный символ, при этом меняя другие.

Автосумма значений в нужном диапазоне

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

Вызов конструктора формул

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

Читайте так же:
Гугл плей пишет нет интернета

Выбор диапазона для расчётов

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

Закрепление ячеек в формуле

Когда ввод в конструкторе будет завершён, нажмите ОК. На этом этапе Excel проверяет введённые значения на соответствие типов и на другие ошибки, поэтому может выкинуть текст ошибки. По нему вы сможете понять, что именно нужно изменить, чтобы формула работала так, как вы от неё ожидаете. Если же ошибок не будет, окно закроется, а в ячейке появится значение. Иногда бывает так, что какой-то из указанных диапазонов ещё не заполнен, но присвоенный ячейке тип содержимого соответствует нужному. Тогда на этапе проверки ошибок не будет, но и значение будет подсчитано без него, так что будьте внимательны.

Пример ошибки выражения

Понятие формулы массива

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

Предположим, у нас есть формула СУММ, которая возвращает сумму значений определенного диапазона.

Давайте создадим такой простенький диапазон, записав в ячейки A1:A5 числа от одного до пяти. Затем укажем функцию =СУММ(A1:A5) в ячейке B1. В результате, там появится число 15.

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

=СУММ(A1:A5+1). Получается, что мы хотим к диапазону значений добавить единицу перед тем, как подсчитать их сумму. Но и в таком виде Excel не захочет этого делать. Ему нужно показать это, использовав формулу Ctrl + Shift + Enter. Формула массива отличается внешним видом и выглядит следующим образом:

После этого в нашем случае будет введен результат 20.

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

Читайте так же:
Можно ли звонить с ipad 2018

Внутри этой функции, тем временем, осуществлялись следующие действия. Сначала программа раскладывает этот диапазон на составляющие. В нашем случае – это 1,2,3,4,5. Далее Excel автоматически увеличивает каждую из них на единицу. Потом полученные числа складываются.

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

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

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

А вот если превратить ее в формулу массива, расклад может быстро измениться. Теперь наименьшее значение будет 1.

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

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

КАК СОСТАВИТЬ ТАБЛИЦУ В EXCEL С ФОРМУЛАМИ

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

Простейшие формулы заполнения таблиц в Excel:

    Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+» wp-block-image»>Как в Excel создаются формулы и таблицы. Пошагово

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

Как в Excel создаются формулы и таблицы. Пошагово

4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

Как в Excel создаются формулы и таблицы. Пошагово

Как в Excel создаются формулы и таблицы. Пошагово

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

Ну вот! Теперь мы умеем создавать формулы и таблицы в Excel.

Дорогой читатель! Добавьте этот сайт в закладки своего браузера и поделитесь с друзьями ссылкой на этот сайт! Мы стараемся показывать здесь всякие хитрости и секреты. Пригодится точно.

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