Формулы для xl: 15 формул в Excel, которые помогут в работе

Перевод чисел в различные системы счисления в Excel — TutorExcel.Ru

Перевод чисел в различные системы счисления в Excel

Изучим стандартные способы перевода чисел в различные системы счисления в Excel: двоичную, восьмеричную, десятичную и шестнадцатеричную.

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

Системы счисления в Excel

В Excel есть возможность стандартными средствами переводить данные в четырех системах счисления:

Давайте подробно остановимся на основных вариантах преобразования данных.

Перевод числа из десятичной в двоичную систему в Excel

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

ДЕС.В.ДВ(число; [разрядность])
Преобразует десятичное число в двоичное.

• Число (обязательный аргумент) — десятичное целое число, которое требуется преобразовать;
• Разрядность (необязательный аргумент) — количество знаков для использования в записи. Данный аргумент необходим если нужно приписать к двоичной записи данных ведущие нули. К примеру, число 1101 с разрядностью 7 будет иметь вид 0001101.

Обратите внимание, что Excel накладывает определенные ограничения на размер преобразуемых данных.
Двоичная запись не должна занимать более 10 знаков, поэтому десятичное число, соответственно, не должно быть больше 511 или меньше -512, иначе в качестве значения функция ДЕС.В.ДВ вернет ошибку.

Перевод числа из двоичной в десятичную систему в Excel

Для осуществления обратного перевода можно воспользоваться функцией ДВ.В.ДЕС:

ДВ.В.ДЕС(число)
Преобразует двоичное число в десятичное.

• Число (обязательный аргумент) — двоичное число, которое требуется преобразовать.

При этом разрядность в качестве аргумента функции для десятичной записи не используется.
Как и в случае с функцией ДЕС.В.ДВ при использовании ДВ.В.ДЕС существует ограничение на размер преобразуемых данных — не более 10 знаков в записи, в ином случае функция вернет значение ошибки.

Перевод в других системах счисления

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

Как и в примерах выше имена функций образуются по достаточно простому правилу — берутся первые буквы от названий систем в которых преобразуются данные и разделяются точками (ВОСЬМеричное В ШЕСТНадцатеричное и пр. )

Арифметические операции с данными

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

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

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:

Поиск по сайту:

Математика,Функции

• ← Пузырьковая диаграмма в Excel
• Фильтр и сортировка ячеек по цвету в Excel →

Формула разницы в Excel: 4 простых способа

Содержание

• Вычисление разницы
  • Способ 1: вычитание чисел
  • Способ 2: денежный формат
  • Способ 3: даты
  • Способ 4: время
• Вопросы и ответы

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

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

X=A-B

А теперь давайте рассмотрим, как производится вычитание значений различных форматов: числового, денежного, даты и времени.

Способ 1: вычитание чисел

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

1. Если вам нужно произвести обычное вычитание чисел, воспользовавшись Excel, как калькулятором, то установите в ячейку символ «=». Затем сразу после этого символа следует записать уменьшаемое число с клавиатуры, поставить символ «-», а потом записать вычитаемое. Если вычитаемых несколько, то нужно опять поставить символ «-» и записать требуемое число. Процедуру чередования математического знака и чисел следует проводить до тех пор, пока не будут введены все вычитаемые. Например, чтобы из числа 10 вычесть 5 и 3, нужно в элемент листа Excel записать следующую формулу:

   =10-5-3

   После записи выражения, для выведения результата подсчета, следует кликнуть по клавише Enter.

2. Как видим, результат отобразился. Он равен числу 2.

Но значительно чаще процесс вычитания в Экселе применяется между числами, размещенными в ячейках. При этом алгоритм самого математического действия практически не меняется, только теперь вместо конкретных числовых выражений применяются ссылки на ячейки, где они расположены. Результат же выводится в отдельный элемент листа, где установлен символ «=».

Посмотрим, как рассчитать разницу между числами 59 и 26, расположенными соответственно в элементах листа с координатами A3 и С3.

1. Выделяем пустой элемент книги, в который планируем выводить результат подсчета разности. Ставим в ней символ «=». После этого кликаем по ячейке A3. Ставим символ «-». Далее выполняем клик по элементу листа С3. В элементе листа для вывода результата должна появиться формула следующего вида:

   =A3-C3

   Как и в предыдущем случае для вывода результата на экран щелкаем по клавише Enter.

2. Как видим, и в этом случае расчет был произведен успешно. Результат подсчета равен числу 33.

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

=A3-23-C3-E3-5

Урок: Как вычесть число из числа в Экселе

Способ 2: денежный формат

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

1. Собственно можно провести операцию, как обычное вычитание чисел, и только потом отформатировать итоговый результат под денежный формат. Итак, производим вычисление. Например, вычтем из 15 число 3.
2. После этого кликаем по элементу листа, который содержит результат. В меню выбираем значение «Формат ячеек…». Вместо вызова контекстного меню можно применить после выделения нажатие клавиш Ctrl+1.
3. При любом из двух указанных вариантов производится запуск окна форматирования. Перемещаемся в раздел «Число». В группе «Числовые форматы» следует отметить вариант «Денежный». При этом в правой части интерфейса окна появятся специальные поля, в которых можно выбрать вид валюты и число десятичных знаков. Если у вас Windows в целом и Microsoft Office в частности локализованы под Россию, то по умолчанию должны стоять в графе «Обозначение» символ рубля, а в поле десятичных знаков число «2». В подавляющем большинстве случаев эти настройки изменять не нужно. Но, если вам все-таки нужно будет произвести расчет в долларах или без десятичных знаков, то требуется внести необходимые коррективы.

   Вслед за тем, как все необходимые изменения сделаны, клацаем по «OK».

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

Существует ещё один вариант отформатировать полученный итог вычитания под денежный формат. Для этого нужно на ленте во вкладке «Главная» кликнуть по треугольнику, находящемуся справа от поля отображения действующего формата ячейки в группе инструментов «Число». Из открывшегося списка следует выбрать вариант «Денежный». Числовые значения будут преобразованы в денежные. Правда в этом случае отсутствует возможность выбора валюты и количества десятичных знаков. Будет применен вариант, который выставлен в системе по умолчанию, или настроен через окно форматирования, описанное нами выше.

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

Урок: Как изменить формат ячейки в Экселе

Способ 3: даты

А вот вычисление разности дат имеет существенные нюансы, отличные от предыдущих вариантов.

1. Если нам нужно вычесть определенное количество дней от даты, указанной в одном из элементов на листе, то прежде всего устанавливаем символ «=» в элемент, где будет отображен итоговый результат. После этого кликаем по элементу листа, где содержится дата. Его адрес отобразится в элементе вывода и в строке формул. Далее ставим символ «-» и вбиваем с клавиатуры численность дней, которую нужно отнять. Для того, чтобы совершить подсчет клацаем по Enter.
2. Результат выводится в обозначенную нами ячейку. При этом её формат автоматически преобразуется в формат даты. Таким образом, мы получаем полноценно отображаемую дату.

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

1. Устанавливаем символ «=» в ячейку, где будет отображен результат. После этого клацаем по элементу листа, где содержится более поздняя дата. После того, как её адрес отобразился в формуле, ставим символ «-». Клацаем по ячейке, содержащей раннюю дату. Затем клацаем по Enter.
2. Как видим, программа точно вычислила количество дней между указанными датами.

Также разность между датами можно вычислить при помощи функции РАЗНДАТ. Она хороша тем, что позволяет настроить с помощью дополнительного аргумента, в каких именно единицах измерения будет выводиться разница: месяцы, дни и т.д. Недостаток данного способа заключается в том, что работа с функциями все-таки сложнее, чем с обычными формулами. К тому же, оператор РАЗНДАТ отсутствует в списке Мастера функций, а поэтому его придется вводить вручную, применив следующий синтаксис:

=РАЗНДАТ(нач_дата;кон_дата;ед)

«Начальная дата» — аргумент, представляющий собой раннюю дату или ссылку на неё, расположенную в элементе на листе.

«Конечная дата» — это аргумент в виде более поздней даты или ссылки на неё.

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

Итак, в нашем случае требуется вычислить разницу в днях между 27 мая и 14 марта 2017 года. Эти даты расположены в ячейках с координатами B4 и D4, соответственно. Устанавливаем курсор в любой пустой элемент листа, где хотим видеть итоги расчета, и записываем следующую формулу:

=РАЗНДАТ(D4;B4;"d")

Жмем на Enter и получаем итоговый результат подсчета разности 74. Действительно, между этими датами лежит 74 дня.

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

=РАЗНДАТ("14.03.2017";"27.05.2017";"d")

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

Урок: Количество дней между датами в Экселе

Способ 4: время

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

1. Итак, перед нами стоит задача узнать, сколько минут прошло с 15:13 по 22:55. Записываем эти значения времени в отдельные ячейки на листе. Что интересно, после ввода данных элементы листа будут автоматически отформатированы под содержимое, если они до этого не форматировались. В обратном случае их придется отформатировать под дату вручную. В ту ячейку, в которой будет выводиться итог вычитания, ставим символ «=». Затем клацаем по элементу, содержащему более позднее время (22:55). После того, как адрес отобразился в формуле, вводим символ «-». Теперь клацаем по элементу на листе, в котором расположилось более раннее время (15:13). В нашем случае получилась формула вида:

   =C4-E4

   Для проведения подсчета клацаем по Enter.

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

   Для того, чтобы получить минуты, нам следует предыдущий результат умножить на коэффициент 1440. Этот коэффициент получается путем умножения количества минут в часе (60) и часов в сутках (24).

Итак, устанавливаем символ «=» в пустой ячейке на листе. После этого производим клик по тому элементу листа, где находится разность вычитания времени (7:42). После того, как координаты данной ячейки отобразились в формуле, жмем на символ «умножить» (*) на клавиатуре, а затем на ней же набираем число 1440. Для получения результата клацаем по Enter.

3. Но, как видим, опять результат отобразился некорректно (0:00). Это связано с тем, что при умножении элемент листа был автоматически переформатирован в формат времени. Для того, чтобы отобразилась разность в минутах нам требуется вернуть ему общий формат.
4. Итак, выделяем данную ячейку и во вкладке «Главная» клацаем по уже знакомому нам треугольнику справа от поля отображения форматов. В активировавшемся списке выбираем вариант «Общий».

   Можно поступить и по-другому. Выделяем указанный элемент листа и производим нажатие клавиш Ctrl+1. Запускается окно форматирования, с которым мы уже имели дело ранее. Перемещаемся во вкладку «Число» и в списке числовых форматов выбираем вариант «Общий». Клацаем по «OK».

5. После использования любого из этих вариантов ячейка переформатируется в общий формат. В ней отобразится разность между указанным временем в минутах. Как видим, разница между 15:13 и 22:55 составляет 462 минуты.

Урок: Как перевести часы в минуты в Экселе

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

Как автозаполнять формулы в Excel?

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

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

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

Поговорим о структуре данных. Каждая строка в этих данных представляет собой отдельную неделю (в этом примере их 12), а каждый столбец, начинающийся в столбце B и заканчивающийся столбцом H, представляет день недели. Чтобы контекстуализировать этот пример, скажем, что это трекер ежедневных расходов. Так, например, ячейки с B3 по h4 представляют расходы, понесенные в течение недели 1.

Мы хотим суммировать ежедневные расходы за каждую неделю, чтобы получить представление о еженедельных расходах. Чтобы сделать это с помощью функции Excel, используйте формулу =СУММ. Как мы видим в приведенном выше примере, мы вводим =СУММ в ячейке I3, а затем выделяем диапазон ячеек, которые мы хотим суммировать (от B3 до h4). Результат этой функции представляет собой сумму ежедневных расходов в неделю 1 (т. е. наши еженедельные расходы).

Как применить формулу к каждой неделе, не переписывая формулу =СУММ для каждой строки (т.е. для каждой недели в нашем наборе данных)? Есть два способа сделать это!

Метод 1

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

Метод 2

Для тех, кто не хочет использовать мышь, вы можете использовать сочетание клавиш навигации и клавиш, чтобы скопировать формулу в последнюю строку набора данных. Скопируйте формулу (CTRL + C)> Стрелка к столбцу, который полностью заполнен> Перейти к последней строке этого столбца (CTRL + Стрелка вниз)> Стрелка к столбцу, который вы хотите заполнить> Удерживая SHIFT + CTRL, нажмите стрелку вверх > Вставьте формулу (CTRL + V).

Попробуйте любой метод (или оба) в следующий раз, когда столкнетесь с такой ситуацией! Метод 2 кажется более длинным из-за большого количества нажатий клавиш, но по мере того, как вы наращиваете мышечную память, он может быть даже более быстрым способом заполнения данных, чем метод 1.

Энрике получил степень бакалавра финансов и статистики в Школе Стерна Нью-Йоркского университета. Бизнеса и получил степень магистра делового администрирования в бизнес-школе Колумбийского университета, которую окончил с отличием декана. Энрике руководил консалтинговым стартапом по анализу данных из инкубатора Columbia Startup Lab Incubator с тех пор, как получил степень MBA.

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

Хотите узнать больше о бизнес-репетиторстве? Прочтите некоторые из наших других сообщений в блоге ниже!

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

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

Использование автозаполнения

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

Создание именованного диапазона

Другой способ корректировки значений с помощью формул в Excel — создание именованного диапазона. Это хороший вариант, если вы хотите иметь возможность ссылаться на диапазон в другом месте своей книги или если вы планируете использовать одну и ту же формулу в нескольких местах. Чтобы создать именованный диапазон, выберите ячейки, которые вы хотите включить в диапазон, затем нажмите «Формулы» > «Диспетчер имен». В диалоговом окне «Диспетчер имен» нажмите «Создать», введите имя диапазона в поле «Имя» и нажмите «ОК».

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

=SUM(MyRange)

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

Понимание формул в Excel

Формулы — это уравнения, которые выполняют вычисления над значениями на листе Excel. Их можно использовать для сложения, вычитания, умножения, деления и выполнения других математических операций над вашими данными. Формулы в Excel всегда начинаются со знака равенства (=) и могут включать ссылки на ячейки, значения и функции.

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

Одним из наиболее распространенных способов использования формул в Excel является создание ссылок на ячейки. Чтобы сослаться на ячейку в формуле, просто введите адрес ячейки (например, A1) в формулу. Вы также можете щелкнуть ячейку, чтобы автоматически вставить ее адрес в формулу. Например, если вы хотите сложить значения в ячейках A1 и A2, вы должны ввести формулу «=A1+A2» в другую ячейку. 9)

Чтобы выполнить математическую операцию в формуле, просто включите соответствующий оператор между значениями или ссылками на ячейки. Например, чтобы умножить значение в ячейке A1 на 10, введите формулу «=A1*10» в другую ячейку.

Использование функций в формулах

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

Чтобы использовать функцию в формуле, просто введите имя функции и соответствующие аргументы (например, «=СУММ(A1:A10)»). Excel автоматически рассчитает результат на основе значений в указанном диапазоне ячеек.

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

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

• Добавление или вычитание фиксированного значения
• Умножение или деление на фиксированное значение
• Использование процентного увеличения или уменьшения

Добавление или вычитание фиксированного значения

Чтобы добавить или вычесть фиксированное значение из ячейки, просто используйте оператор сложения или вычитания (+ или — ) в формуле. Например, если вы хотите добавить 10 к значению в ячейке A1, вы должны ввести формулу «=A1+10» в другую ячейку. Если вы хотите вычесть 5 из значения в ячейке A1, вы должны ввести формулу «=A1-5» в другую ячейку.

Умножение или деление на фиксированное значение

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