Как в excel делать расчеты: Расчёты в Excel — Табличный процессор MS Excel

Содержание

Как сделать расчетную таблицу в excel?

Содержание

  • 1 Ручной режим
  • 2 Автоматический режим
  • 3 Сводная таблица
  • 4 Рекомендуемые сводные таблицы
  • 5 Готовые шаблоны в Excel 2016
  • 6 Оформление
    • 6.1 Создание заголовка
    • 6.2 Изменение высоты элементов
    • 6.3 Выравнивание текста
    • 6.4 Изменение стиля
    • 6.5 Как вставить новую строку или столбец
    • 6.6 Удаление элементов
    • 6.7 Заливка ячеек
    • 6.8 Формат элементов
    • 6.9 Формат содержимого
  • 7 Использование формул в таблицах
  • 8 Использование графики
  • 9 Экспорт в Word
  • 10 Онлайн-сервисы
  • 11 Способы печати
  • 12 Отличие версий продукции Майкрософт
    • 12.1 Ограничения и возможности разных версий
  • 13 Заключение
  • 14 Видеоинструкция
  • 15 Как создать таблицу в Excel для чайников
    • 15.1 Как выделить столбец и строку
    • 15.2 Как изменить границы ячеек
    • 15. 3 Как вставить столбец или строку
    • 15.4 Пошаговое создание таблицы с формулами
  • 16 Как создать таблицу в Excel: пошаговая инструкция
  • 17 Как работать с таблицей в Excel

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

В этой небольшой заметке речь пойдет об одном из аспектов использования таблиц Excel – для создания форм математических расчетов.

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

Три человека (Иван, Петр и Василий) ведут общую предпринимательскую деятельность, заключающуюся в оптовом приобретении овощей в одних регионах страны, их транспортировке в регионы с повышенным спросом и реализации товара по более высокой цене. Иван занимается закупкой, Василий — реализацией и оба они получают по 35 % от чистой прибыли. Петр – транспортирует товар, его доля – 30 %. По этой схеме партнеры работают постоянно и ежемесячно (или даже чаще) им приходится подсчитывать, сколько денег кому положено. При этом, необходимо каждый раз производить расчеты с учетом закупочной стоимости и цены реализации товара, его количества, стоимости топлива и расстояния транспортировки а также некоторых других факторов. А если в Excel создать расчетную таблицу, Иван, Петр и Василий смогут очень быстро поделить заработанные деньги, просто введя необходимую информацию в соответствующие ячейки. Все расчеты программа сделает за них всего за несколько мгновений.

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

Работа в Excel. Создание расчетной таблицы:

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

На рисунке видно, что в моем примере созданные поля имеют следующие координаты: закупочная стоимость товара (руб/кг) – b1 количество товара (кг) – b2 цена реализации товара (руб/кг) – b3 расстояние транспортировки (км) – b4 стоимость топлива (руб/л) – b5 расход топлива (л/100км) – b6 потрачено на закупку товара (руб) — b9 выручено от реализации товара (руб) – b10 стоимость транспортировки (руб) – b11 общая прибыль (руб) – b12 Иван –b15 Петр – b16 Василий – b17

Теперь необходимо установить взаимосвязи между ячейками таблицы. Выделяем ячейку «Потрачено на закупку товара» (b9), вводим в нее =b1*b2 (равно b1 «закупочная стоимость товара» умножить на b2 «количество товара») и жмем Enter. После этого в поле b9 появится 0. Теперь если в ячейки b1 и b2 ввести какие-то числа, в ячейке b9 будет отображаться результат умножения этих чисел.

Аналогичным образом устанавливаем взаимосвязи между остальными ячейками: B10 =B2*B3 (выручено от реализации товара равно количество товара, умноженное на цену реализации товара); B11 =B5*B6*B4/100 (стоимость транспортировки равна стоимости топлива умноженной на расход топлива на 100 км, умноженной на расстояние и разделенное на 100); B12 =B10-B9-B11 (общая прибыль равна вырученному от реализации товара за вычетом потраченного на его закупку и транспортировку); B15 =B12/100*35 B16 =B12/100*35 (ячейки Иван и Петр имеют одинаковые значение, поскольку по условиям задачи они получают по 35% от значения ячейки B11 (общей прибыли); B16 =B12/100*30 (а Василий получает 30% от общей прибыли).

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

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

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

План обучения будет прост:

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

Ручной режим

Данный метод самый простой. Делается это следующим образом.

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

Теперь можете приступать к заполнению данных.

Существует и другой способ ручного рисования таблицы.

  1. Снова нажмите на иконку «Границы». Но на этот раз выберите пункт «Нарисовать сетку».
  1. Сразу после этого у вас изменится внешний вид курсора.
  1. Сделайте левый клик мыши и перетаскивайте указатель в другое положение. В результате этого будет нарисована новая сетка. Верхний левый угол – начальное положение курсора. Правый нижний угол – конечное.

Размеры могут быть любыми. Таблица будет создаваться, пока вы не отпустите палец с кнопки мыши.

Автоматический режим

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

  1. Перейдите на вкладку «Вставка». Нажмите на кнопку «Таблицы» и выберите последний пункт.

Обратите внимание на то, что нам подсказывают о горячих клавишах. В дальнейшем для автоматического создания можно использовать сочетание кнопок Ctrl+T.

  1. Сразу после этого у вас появится окно, в котором нужно указать диапазон будущей таблицы.
  1. Для этого достаточно просто выделить любую область – координаты подставятся автоматом.
  1. Как только вы отпустите курсор, окно примет исходный вид. Нажмите на кнопку «OK».
  1. В результате этого будет создана красивая таблица с чередующимися линиями.
  1. Для того чтобы изменить название столбца, достаточно кликнуть на него. После этого начать редактирование можно прямо в этой ячейке или в строке формул.

Дальше можете делать, что душе угодно.

Сводная таблица

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

  1. Сначала делаем таблицу и заполняем её какими-нибудь данными. Как это сделать, описано выше.
  1. Теперь заходим в главное меню «Вставка». Далее выбираем нужный нам вариант.
  1. Сразу после этого у вас появится новое окно.
  1. Кликните на первую строчку (поле ввода нужно сделать активным). Только после этого выделяем все ячейки.
  1. Затем нажимаем на кнопку «OK».
  1. В результате этого у вас появится новая боковая панель, где нужно настроить будущую таблицу.
  1. На этом этапе необходимо перенести поля в нужные категории. Столбцами у нас будут месяцы, строками – назначение затрат, а значениями – сумма денег.

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

Только после этого (иконка курсора изменит внешний вид) палец можно отпустить.

  1. В результате этих действий у вас появится новая красивая таблица, в которой всё будет подсчитано автоматически. Самое главное, что появятся новые ячейки – «Общий итог».

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

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

Рекомендуемые сводные таблицы

Иногда не получается правильно подобрать поля для столбцов и строк. И в итоге ничего путного не выходит. Для таких случаев разработчики Microsoft подготовили свои варианты анализа данных.

Работает это очень просто.

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

Готовые шаблоны в Excel 2016

Для особо ленивых данная программа позволяет создавать по-настоящему «крутые» таблицы всего одним кликом.

При запуске Экселя вам на выбор предлагаются следующие варианты:

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

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

Кликаем на какой-нибудь понравившийся вариант.

Нажимаем на кнопку «Создать».

В результате этого вы получаете готовый вариант очень большой и сложной таблицы.

Оформление

Внешний вид – это один из важнейших параметров. Очень важно сделать акцент на каких-нибудь элементах. Например, шапка, заголовок и так далее. Всё зависит от конкретного случая.

Рассмотрим вкратце основные манипуляции с ячейками.

Создание заголовка

В качестве примера будем использовать простую таблицу.

  1. Сначала переходим на вкладку «Главная» и нажимаем на пункт меню «Вставить строки на лист».
  1. Выделяем появившуюся строчку и нажимаем на пункт меню «Объединить ячейки».
  1. Далее пишем любой заголовок.

Изменение высоты элементов

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

В результате этого высота строки будет больше.

Выравнивание текста

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

Кликаем на кнопку «По середине» и получаем желанный результат.

Теперь заголовок смотрится куда лучше.

Изменение стиля

Также рекомендуется изменить шрифт и увеличить кегль (размер по вертикали). Сделать это можно вручную при помощи панели инструментов.

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

Эффект будет очень красивым.

Как вставить новую строку или столбец

Для того чтобы изменить количество элементов в таблице, можно воспользоваться кнопкой «Вставить».

Вы можете добавить:

  • ячейки;
  • строки;
  • столбцы;
  • целый лист.

Удаление элементов

Уничтожить ячейку или что-нибудь еще можно точно так же. Для этого есть своя кнопка.

Заливка ячеек

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

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

Формат элементов

При желании можно сделать с таблицей что угодно. Для этого достаточно нажать на кнопку «Формат».

В результате этого вы сумеете:

  • вручную или автоматически изменить высоту строк;
  • вручную или автоматически изменить ширину столбцов;
  • скрыть или отобразить ячейки;
  • переименовать лист;
  • изменить цвет ярлыка;
  • защитить лист;
  • блокировать элемент;
  • указать формат ячеек.

Формат содержимого

Если нажать на последний из вышеописанных пунктов, то появится следующее:

Благодаря этому инструменту можно:

  • изменить формат отображаемых данных;
  • указать выравнивание;
  • выбрать любой шрифт;
  • изменить границы таблицы;
  • «поиграть» с заливкой;
  • установить защиту.

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

Именно благодаря возможности использовать функции автоподсчёта (умножение, сложение и так далее), Microsoft Excel и стал мощным инструментом.

Полную информацию о формулах в Экзеле лучше всего посмотреть на официальной странице справки.

Кроме этого, рекомендуется ознакомиться с описанием всех функций.

Рассмотрим самую простую операцию – умножение ячеек.

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

=C3*D3

  1. Теперь нажмите на клавишу Enter. После этого наведите курсор на правый нижний угол этой ячейки до тех пор, пока не изменится его внешний вид. Затем зажмите пальцем левый клик мыши и потяните вниз до последней строки.
  1. В результате автоподстановки формула попадёт во все ячейки.

Значения в колонке «Общая стоимость» будут зависеть от полей «Количество» и «Стоимость 1 кг». Это и есть прелесть динамики.

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

  1. Сначала выделяем значения. Затем нажимаем на кнопку «Автосуммы», которая расположена на вкладке «Главная».
  1. В результате этого ниже появится общая сумма всех чисел.

Использование графики

Иногда в ячейках вместо текста используют фотографии. Сделать это очень легко.

Выбираем пустой элемент. Переходим на вкладку «Вставка». Выбираем раздел «Иллюстрации». Кликаем на пункт «Рисунки».

  1. Указываем файл и кликаем на кнопку «Вставить».
  1. Результат вас не разочарует. Смотрится очень красиво (в зависимости от подобранного рисунка).

Экспорт в Word

Для того чтобы скопировать данные в «вордовский» документ, достаточно сделать пару простых действий.

  1. Выделите область данных.
  1. Нажмите на горячие клавиши Ctrl+C.
  2. Откройте документ
  3. Теперь используем кнопки Ctrl+V.
  4. Итог будет следующим.

Онлайн-сервисы

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

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

Способы печати

Распечатка документов Word, как правило, задача несложная. Но с таблицами в Excel всё иначе. Самая большая проблема заключается в том, что «на глаз» сложно определить границы печати. И очень часто в принтере появляются практически пустые листы, на которых находится всего 1-2 строки таблицы.

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

  1. Открываем документ. Он выглядит вполне обычно.
  1. Далее нажмите на горячие клавиши Ctrl+P. В появившемся окне мы видим, что информация не помещается на один лист. У нас исчезла графа «Общая стоимость». Кроме того, внизу нам подсказывают, что при печати будет использовано 2 страницы.

В версии 2007 года, для этого нужно было нажать на кнопку «Просмотр».

  1. Для отмены нажимаем горячую клавишу Esc. В результате появится вертикальная пунктирная линия, которая показывает границы печати.

Увеличить пространство при печати можно следующим образом.

  1. Первым делом уменьшаем поля. Для этого переходим на вкладку «Разметка страницы». Кликаем на кнопку «Поля» и выбираем самый «Узкий» вариант.
  1. После этого уменьшаем ширину столбцов, пока пунктирная линия не окажется за пределами последней колонки. Как это сделать, было описано выше.

Уменьшать нужно в разумных пределах, чтобы не страдала читабельность текста.

  1. Снова нажимаем на Ctrl+P. Теперь мы видим, что информация помещается на один лист.

Отличие версий продукции Майкрософт

Стоит понимать, что Эксель 2003 года уже давно морально устарел. Там отсутствует огромное количество современных функций и возможностей. Кроме этого, внешний вид различных объектов (графики, диаграммы и так далее) сильно уступает современным требованиям.

Пример рабочей области Excel 2003.

В современных 2007, 2010, 2013, а тем более 2016 версиях всё намного «круче».

Многие пункты меню находятся в разных разделах. Некоторые из них вовсе изменили своё название. Например, привычные нам «Формулы», в далёком 2003 назывались «Функциями». И они занимали не так уж много места.

Сейчас же для них отведена целая вкладка.

Ограничения и возможности разных версий

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

Пример самых основных параметров.

Этот список довольно длинный. Поэтому стоит перейти по ссылке и ознакомиться с остальными.

Обратите внимание, что версию 2003 года даже не рассматривают, так как её поддержка прекращена.

Но в некоторых бюджетных организациях этот офисный пакет используется и по сей день.

Заключение

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

Видеоинструкция

Тем, у кого остались какие-нибудь вопросы, ниже прилагается видеоролик, в котором прилагаются дополнительные комментарии к описанным выше инструкциям.

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

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

Как создать таблицу в Excel для чайников

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

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

  1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.
  2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.
  3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

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

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

  1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
  2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
  3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
  4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

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

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

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

Как создать таблицу в Excel: пошаговая инструкция

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

Сделаем «умную» (динамическую) таблицу:

  1. Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

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

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

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Здесь мы можем дать имя таблице, изменить размер.

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

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

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

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

Расчет значений на основе данных в ячейках таблицы в Pages на Mac

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

Для создания формул можно использовать любую из более чем 250 математических функций, встроенных в Pages. Вам доступны функции для различных видов расчетов, включая статистические, финансовые и инженерные, некоторые из которых получают информацию удаленно из интернета. Подробнее о каждой из этих функций можно узнать в браузере функций, который появляется при вводе знака равенства (=) в ячейке, а также в Справке по формулам и функциям в интернете.

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

Использование редактора формул в качестве калькулятора

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

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

  2. Введите уравнение, которое требуется вычислить, используя значения и арифметические операторы, такие как +, -, * и /).

    Например, если ввести знак равенства (=), а затем «1+1» в редакторе формул, ячейка вернет результат — 2. Можно также использовать скобки; если ввести 4+6*(3-1), ячейка вернет 16.

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

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

  1. Нажмите ячейку, в которой нужно отображать результат, и введите знак равенства (=).

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

  2. Выберите ячейку, которая будет служить первым аргументом формулы, или введите значение (например, число 0 или 5,20).

  3. Введите арифметический оператор (например, +, -, * или /), затем выберите ячейку, которая будет служить следующим аргументом в формуле, или введите значение.

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

  4. Продолжайте добавлять аргументы и операторы до получения требуемой формулы.

  5. По завершении нажмите клавишу Return или в редакторе формул.

    При нажатии работа редактора формул завершается без сохранения внесенных изменений.

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

Вставка функции

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

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

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

  2. Введите имя нужной функции (или атрибуты, связанные с функцией, например «адрес») в поле поиска в верхней части браузера функций или просмотрите список имеющихся функций, затем дважды нажмите на имя необходимой.

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

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

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

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

    • Выбор ячейки. Нажмите ячейку.

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

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

  4. По завершении нажмите клавишу Return или в редакторе формул.

    При нажатии работа редактора формул завершается без сохранения внесенных изменений.

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

Сравнение значений с помощью формулы

Операторы сравнения позволяют проверить, равны ли значения в двух ячейках или первое значение больше/меньше второго. Для этого в ячейке необходимо ввести оператор, например «A1 > A2» (значение в ячейке A1 больше значения в ячейке A2). Оператор сравнения возвращает результат «истина» или «ложь».

  1. Нажмите ячейку, в которой нужно отображать результат сравнения, и введите знак равенства (=).

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

  2. Нажмите ячейку, содержащую значение для сравнения, или введите значение для сравнения.

  3. Введите оператор сравнения (>, >=, =, <>, < или <=), затем выберите вторую ячейку, содержащую значение для сравнения, или введите значение для сравнения.

  4. По завершении нажмите клавишу Return или в редакторе формул.

    При нажатии работа редактора формул завершается без сохранения внесенных изменений.

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

Ссылки на ячейки в формулах

В формулы можно вставлять ссылки на ячейки, диапазоны ячеек и даже целые столбцы и строки с данными, в том числе ссылки на ячейки в других таблицах и на других страницах. Приложение Pages использует для вычисления формулы значения в ячейках, на которые даны ссылки. Например, если включить в формулу ссылку «А1», она будет указывать на значение в ячейке A1 (в столбце А и в ряду 1).

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

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

    СЧЕТ(A3:D7)

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

    Таблица 2::B2

    Обратите внимание на то, что заголовок таблицы и ссылка на ячейку разделяются двойным двоеточием (::). Если в формуле дается ссылка на ячейку из другой таблицы, имя таблицы добавляется в ссылку автоматически.

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

    СУММ(C)

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

    СУММ(1:1)

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

    СУММ(Доход)

В приведенной ниже формуле умножается сумма значений в столбце «Кол‑во гостей» на значение в ячейке B2, которая находится в другой таблице.

Закрепление ссылок на строки и столбцы в формулах

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

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

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

    Откроется редактор формул с функциями. Для перемещения редактора формул перетяните его левую часть.

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

  3. Выберите «Сохранить строку» или «Сохранить столбец» для начального или конечного адресов выбранного диапазона.

  4. По завершении нажмите клавишу Return или в редакторе формул.

    При нажатии работа редактора формул завершается без сохранения внесенных изменений.

Редактирование существующей формулы

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

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

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

  2. Выполните одно из указанных ниже действий.

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

    • Удаление ссылок на ячейки. Поместите точку вставки в область аргументов формулы, выберите ненужные ссылки на ячейки, затем нажмите клавишу Delete на клавиатуре.

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

  3. По завершении нажмите клавишу Return или в редакторе формул.

    При нажатии работа редактора формул завершается без сохранения внесенных изменений.

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

См. такжеФорматирование дат, значений валют и других типов данных в Pages на Mac

Расчет времени в Excel (разница во времени, количество отработанных часов, добавление/вычитание)

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

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

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

В этом руководстве рассматриваются:

Как Excel обрабатывает дату и время?

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

Например, значение 1 представляет 01 января 1900 года в Excel, что является отправной точкой, с которой Excel начинает учитывать даты.

Итак, 2 будет означать 02 января 1990, 3 будет означать 03 января 1900 года и т. д., а 44197 будет означать 01 января 2021 года.

Примечание. Excel для Windows и Excel для Mac имеют разные даты начала. 1 в Excel для Windows будет означать 1 января 1900 г., а 1 в Excel для Mac будет означать 1 января 1904 г.

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

Например, 44197,5 будет означать 01 января 2021 г., 12:00:00.

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

И Excel дает вам возможность конвертировать эту десятичную часть в различные форматы, такие как только часы, только минуты, только секунды или комбинацию часов, минут и секунд

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

Формулы для расчета разницы во времени между двумя значениями времени

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

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

Давайте рассмотрим пару примеров

Простое вычитание из расчета разницы во времени в Excel

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

 Время окончания – Время начала

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

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

Возможно, ваши результаты будут отображаться в формате времени (вместо десятичных дробей или часов/минут). В приведенном выше примере результат в ячейке C2 показывает 09.:30 утра вместо 9.5.

Совершенно нормально, поскольку Excel пытается скопировать формат из соседнего столбца.

Чтобы преобразовать это в десятичное число, измените формат ячеек на «Общий» (параметр находится на вкладке «Главная» в группе «Числа»)

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

Ниже приведены различные форматы, которые вы можете использовать:

400611111111111111111111111111111111.70061111111111111111111111111111111111.70061. или

Формат Что он делает
H показывает только часы, проведенные между двумя датами
Hhh111111111.70061. )
чч:мм Показывает часы и минуты, прошедшие между двумя датами, например 10:20
чч:мм:сс в 10:20:36

И если вам интересно, где и как применить эти настраиваемые форматы даты, выполните следующие шаги:

  1. Выберите ячейки, к которым вы хотите применить формат даты
  2. Удерживая клавишу Control, нажмите 1 (или Command + 1 при использовании Mac)
  3. В открывшемся диалоговом окне «Формат ячеек» щелкните вкладку «Число» (если она еще не выбрана)
  4. На левой панели щелкните «Пользовательский»
  5. Введите любой желаемый формат код в поле Тип (в этом примере я использую чч:мм:сс)
  6. Нажмите OK

Вышеуказанные шаги изменят форматирование и отобразят значение в зависимости от формата.

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

Совет для профессионалов: если общее количество часов превышает 24 часа, вместо этого используйте следующий настраиваемый числовой формат: [чч]:мм:сс

Вычислить разницу во времени в часах, минутах или секундах

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

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

Расчет разницы во времени в часах

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

Ниже приведена формула, которая даст вам разницу во времени в часах:

 =(B2-A2)*24

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

Иногда Excel пытается помочь и выдает результат в формате времени (как показано ниже).

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

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

 =INT((B2-A2)*24)

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

Расчет разницы во времени в минутах

Чтобы вычислить разницу во времени в минутах, нужно умножить полученное значение на общее количество минут в сутках (которое равно 1440 или 24*60).

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

Ниже приведена формула, которая это сделает:

 =(B2-A2)*24*60
Расчет разницы во времени в секундах

Чтобы вычислить разницу во времени в секундах, нужно умножить полученное значение на общее количество секунд в сутках (что равно 24*60*60 или 86400).

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

Ниже приведена формула, которая это сделает:

 =(B2-A2)*24*60*60

Расчет разницы во времени с помощью функции ТЕКСТ

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

Функция ТЕКСТ позволяет указать формат прямо в формуле.

 =ТЕКСТ(Дата окончания - Дата начала, Формат)

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

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

Вот несколько формул, которые дадут вам результат в разных форматах

Показать только количество часов: 

 =ТЕКСТ(B2-A2,"чч")

Приведенная выше формула даст вам только результат который показывает количество часов, прошедших между двумя временными значениями. Если ваш результат равен 9 часам и 30 минутам, он все равно покажет вам только 9.

Показать общее количество минут 

 =ТЕКСТ(B2-A2,"[мм]")

Показать общее количество секунд 

 =ТЕКСТ(B2-A2,"[сс]")

Показать часы и минуты 

 =ТЕКСТ(B2-A2,"[чч]:мм")

Показать часы, минуты и секунды 

 =ТЕКСТ(B2-A2,"чч:мм: ss")

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

Получить разницу во времени в одной единице (часы/минуты) и игнорировать другие

Если вы хотите рассчитать разницу во времени между двумя значениями времени только в часах, минутах или секундах, вы можете использовать специальные функции HOUR, MINUTE или SECOND.

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

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

Ниже приведены формулы для этого:

Подсчет часов, прошедших между двумя периодами времени 

 = ЧАСЫ (B2-A2)

Подсчет минут по результату значения времени (исключая полные часы)

3

 (B2-A2)

Вычисление секунд из результата значения времени (исключая завершенные часы и минуты) 

 =СЕКУНДЫ(B2-A2)

Несколько вещей, которые вам нужно знать при работе с этими ЧАСАМИ, МИНУТНАЯ и СЕКУНДНАЯ формулы:

  • Разница между временем окончания и временем начала не может быть отрицательной (что часто бывает при изменении даты). В таких случаях эти формулы будут возвращать #ЧИСЛО! error
  • Эти формулы используют только временную часть результирующего значения времени (и игнорируют дневную часть). Таким образом, если разница во времени окончания и времени начала составляет 2 дня, 10 часов, 32 минуты и 44 секунды, формула ЧАСов даст 10, формула МИНУТ даст 32, а формула СЕКУНДА даст 44 9.0100

Расчет времени, прошедшего до настоящего момента (от времени начала)

Если вы хотите рассчитать общее время, прошедшее между временем начала и текущим временем, вы можете использовать формулу СЕЙЧАС вместо времени окончания.

Функция СЕЙЧАС возвращает текущую дату и время в ячейке, в которой она используется. Это одна из тех функций, которые не принимают никаких входных аргументов.

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

 =СЕЙЧАС() - Время начала

Ниже приведен пример, где у меня есть время начала в столбце A, а время, прошедшее до настоящего момента, в столбце B.

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

Вы можете сделать это, используя приведенную ниже формулу ТЕКСТА:

 =ТЕКСТ(СЕЙЧАС()-A2,"дд чч:сс:мм")

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

В случае, если ваше начальное время содержит только временную часть, Excel будет рассматривать его как время 1 января 1990 года.

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

В таком случае вы можете использовать приведенную ниже формулу:

 =СЕЙЧАС()- INT(СЕЙЧАС())-A2

Вышеприведенная формула использует функцию ЦЕЛОЕ, чтобы удалить дневную часть из значения, возвращаемого сейчас функция, которая затем используется для вычисления разницы во времени.

Обратите внимание, что СЕЙЧАС — это изменчивая функция, которая обновляется при каждом изменении на листе, но не обновляется в режиме реального времени.

Вычислить время при изменении даты (вычислить и отобразить отрицательное время в Excel) пока работает хорошо, если ваше время окончания позже, чем время начала.

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

В таких случаях, если вы работаете в одну ночную смену и дата меняется, есть вероятность, что ваше время окончания будет раньше времени начала.

Например, если вы начинаете работу в 18:00 вечера и заканчиваете работу и перерыв в 9:00 утра.

Если вы работаете только со значениями времени, то вычитание времени начала из времени окончания даст отрицательное значение 9 часов (9–18).

И Excel не может обрабатывать отрицательные значения времени (и в этом отношении люди тоже не могут, если только вы не умеете путешествовать во времени)

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

К счастью, это легко исправить.

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

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

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

 =B2-A2

Вот формула ЕСЛИ, которая определяет, является ли значение разницы во времени отрицательным или нет, и в случае отрицательного значения возвращает правильный результат

 =ЕСЛИ((B2-A2)<0,1 -(A2-B2),(B2-A2))

Несмотря на то, что в большинстве случаев это работает хорошо, оно все же не работает, если время начала и время окончания отличаются более чем на 24 часа. Например, кто-то входит в 9:00 в первый день и выходит в 11:00 во второй день.

через 2 часа или через 26 часов.

Хотя лучшим способом решить эту проблему было бы убедиться, что записи включают дату, а также время, но если вы работаете только со временем, то приведенная выше формула должна позаботиться о большей части проблемы (учитывая, что маловероятно, что кто-то будет работать более 24 часов)

Добавление/вычитание времени в Excel

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

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

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

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

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

Например, если вы добавите 1 к дате в Excel, вы получите следующую дату. Это связано с тем, что 1 представляет целый день в Excel (который равен 24 часам).

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

Использование функции ВРЕМЯ

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

Например, если я хочу добавить 4 часа к существующему времени, я могу использовать следующую формулу:

 = Время начала + ВРЕМЯ(4,0,0)

Это полезно, если вы знаете, сколько часов, минут и секунд вы хотите добавить к существующему времени, и просто используете функцию ВРЕМЯ, не беспокоясь о правильном преобразовании времени в десятичное значение.

Также обратите внимание, что функция ВРЕМЯ будет учитывать только целую часть введенного значения часов, минут и секунд. Например, если я использую 5,5 часов в функции ВРЕМЯ, это добавит только пять часов и проигнорирует десятичную часть.

Также обратите внимание, что функция ВРЕМЯ может добавлять только значения, которые меньше 24 часов. Если ваше значение часа больше 24, это даст вам неправильный результат.

То же самое с минутами и секундами, где функция будет учитывать только значения, которые меньше 60 минут и 60 секунд

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

Использование базовой арифметики

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

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

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

Например, если вы хотите добавить 24 часа к существующему значению времени, вы можете использовать следующую формулу:

 =Start_time + 24/24

Это просто означает, что я добавляю один день к существующему значению времени.

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

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

 = Время_начала + (минуты для добавления)/24*60*60

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

Вы можете следовать той же концепции при вычитании времени. Просто измените знак + на отрицательный в приведенных выше формулах

Как СУММИТЬ время в Excel

Иногда вам может понадобиться быстро сложить все значения времени в Excel. Добавление нескольких значений времени в Excel довольно просто (все, что нужно, это простая формула СУММ)

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

Давайте посмотрим на пример.

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

В ячейке B9 я использовал простую формулу СУММ для расчета общего времени, которое займет выполнение всех этих задач, и оно дает мне значение 18:30 (что означает, что это займет 18 часов 20 минут). выполнить все эти задания)

Пока все хорошо!

Как СУММИТЬ за 24 часа в Excel

Теперь посмотрим, что произойдет, если я изменю время, которое займет выполнение задачи 2 с 1 часа до 10 часов.

В результате теперь указано 03:20, что означает, что выполнение всех этих задач должно занять 3 часа 20 минут.

Это неверно (очевидно)

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

И поскольку результирующее значение здесь превышает 24 часа, Excel решил преобразовать 24-часовую часть в день, удалить ее из значения, которое показывается пользователю, и отображать только оставшиеся часы, минуты и секунды.

Это, к счастью, легко исправить.

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

Ниже приведены некоторые форматы, которые вы можете использовать:

D ‘D ’hhhhh: 00

D‘ D ’hhhh: 00

1 9006

Формат Ожидаемый результат
[H]: MM 28:30
[M]: SS 1710: 00
D ’HH: 00
. :30
d «D» hh «Min» ss «Sec» 1 D 04 Min 00 Sec
d «Day» hh «Minute» ss «Seconds Days»

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

Вы можете использовать приведенную ниже формулу ТЕКСТ, чтобы показать время, даже если оно превышает 24 часа:

 =ТЕКСТ(СУММ(B2:B7),"[ч]:мм:сс")

или ниже формула, если вы хотите преобразовать часы, превышающие 24 часа, в дни:

 =ТЕКСТ(СУММ(B2:B7),"дд чч:мм:сс")

Результаты, показывающие хэш (###) вместо даты/времени (Причины + исправление)

В некоторых случаях вы можете обнаружить, что вместо отображения значения времени Excel отображает символы решетки в ячейке.

Вот некоторые возможные причины и способы их устранения:

Столбец недостаточно широк

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

Это легко исправить — изменить ширину столбца и сделать его шире.

Отрицательное значение даты

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

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

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

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

Надеюсь, этот урок был вам полезен.

Другие учебники по Excel, которые вам также могут понравиться:

  • Преобразование времени в десятичное число в Excel (часы, минуты, секунды)
  • Как удалить время из даты/временной метки в Excel
  • Как рассчитать количество дней Между двумя датами в Excel
  • Объединение даты и времени в Excel
  • Как рассчитать выслугу лет в Excel
  • Как преобразовать секунды в минуты в Excel

(Архив) Microsoft Excel 2007: Выполнение расчетов с помощью формул

Последнее обновление

Эта статья основана на устаревшем программном обеспечении.

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

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

Написание формул для рабочей тетради

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

Основные требования к формуле

При написании формулы необходимо включить три элемента, чтобы она работала правильно:

  • Знак равенства [=] в начале формулы.
  • Операнд(ы) (например, значения или ссылки на ячейки, которые будут использоваться для создания результата формулы).
  • Оперант(ы) (например, команды сложения, вычитания или выполнения других действий над операндами).

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

.

=А1 + А2

В этом примере ссылки на ячейки A1 и A2 являются операндами, [+] — оперантом, а выбранная ячейка будет содержать сумму значений в ячейках A1 и A2.

Создание формулы для одного рабочего листа

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

Все формулы должны быть созданы следующим образом:

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

  2. Чтобы запустить функцию, нажмите [=].

  3. Щелкните первую ячейку, которую необходимо включить.
    ИЛИ
    Введите ссылку на первую включаемую ячейку.
    ПРИМЕЧАНИЕ. Обязательно помните, нужно ли вам использовать относительную или абсолютную ссылку на ячейку.

  4. Введите первый оператор.
    ПРИМЕР: Нажмите [+].

  5. Щелкните следующую ячейку для включения.
    ИЛИ
    Введите ссылку на следующую ячейку, которую необходимо включить.

  6. При необходимости повторите шаги 4–5, чтобы завершить формулу.

  7. Когда закончите, нажмите [Enter].
    СОВЕТ: Обязательно нажмите [Enter], прежде чем щелкнуть мышью вне ячейки. Если вы этого не сделаете, каждая ячейка, которую вы нажмете, будет добавлена ​​в вашу формулу.

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

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

Использование специальных символов

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

Символ Имя Использование в Формуле
! Восклицательный знак Поместите между именем рабочего листа и ссылкой на ячейку.
Апостроф Поместите вокруг имени файла и имени листа или когда имя листа содержит пробел.
ПРИМЕР: 'Бюджет 2007'!C4:C8
[ ] Кронштейны Место вокруг имени файла.
: Колонка Обозначает диапазон (B3:C2 означает от B3 до C2).
+ Знак плюс Обозначает диапазон (B3+C2 означает B3 и C2).

ПРИМЕЧАНИЕ. В следующих примерах предполагается, что рабочие листы «Бюджет» и «Зарплата» расположены в рабочей книге 07Budget. xls.

Создание ссылки на один лист в той же книге

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

  1. Выберите ячейку, в которой вы хотите разместить формулу.

  2. Для создания формулы используйте следующий формат: = имя_функции (имя_листа! ссылка_ячейки)
    ПРИМЕРЫ:
    (без пробелов в имени листа) =СУММ(Бюджет!C4:C8)
    (с пробелом в имени листа) =СУММ('Бюджет 2007'!C4:C8)

  3. Чтобы принять формулу, нажмите [Enter].

Создание ссылки на несколько листов в одной книге

Excel позволяет создавать ссылки на несколько рабочих листов одновременно. Этот метод позволяет производить расчеты на основе выбранного диапазона рабочих листов.

  1. Выберите ячейку, в которой вы хотите разместить формулу.

  2. Чтобы создать формулу, используйте следующий формат: = имя_функции (диапазон рабочего листа! ссылка_ячейки)
    ПРИМЕРЫ:
    (без пробелов в имени листа) =СУММ(Бюджет:Зарплата!D2:D10)
    (с пробелом в имени листа) =СУММ('Июль 2006:Июнь 2007'!C4:C8)

    ПРИМЕЧАНИЕ. Не забудьте поставить знак плюс (+) или двоеточие (:) между диапазонами листа.

  3. Чтобы принять формулу, нажмите [Enter].

Написание формул для других файлов рабочей книги

Формулы также могут ссылаться на ячейки и рабочие листы из других рабочих книг.

  1. Выберите ячейку, в которой вы хотите разместить формулу.

  2. Чтобы создать формулу, используйте следующий формат: =имя_функции(‘[имя_рабочей_книги]имя_листа’!ссылка_ячейки)
    ПРИМЕР: =СУММ('[07Budget.xls]Зарплата'!D2:D10)
    ПРИМЕЧАНИЯ:
    В формуле нет разницы, если имя вашего листа содержит пробел.
    Если два файла, с которыми вы работаете, находятся в разных каталогах, вы должны указать путь к файлу в своей формуле. Используйте следующий формат: = имя_функции('[путь_к_файлу\имя_книги]имя_листа'!ссылка_ячейки)
    ПРИМЕР: =СУММ('[E:\Accounting\07Budget.xls]Зарплата'!D2:D10)

  3. Чтобы принять формулу, нажмите [Enter].

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

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

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

ПРИМЕР:

Ссылка на ячейку

Ссылка на имя диапазона

=С24*5000

=ВКЛ*5000

Расчет рабочей тетради

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

ПРЕДУПРЕЖДЕНИЕ. В Excel есть функция автоматического расчета перед сохранением работы, но не перед ее печатью. Если вы настроили вычисление вручную, вам нужно будет убедиться, что вы пересчитали его перед печатью и особенно перед тем, как другие увидят ваш документ.

Читайте также: