Xl как в нескольких выделенных ячейках добавить формулуу: Как вставить одинаковые данные (формулы) во все выделенные ячейки одновременно

Как вставить одинаковые данные (формулы) во все выделенные ячейки одновременно

В этой статье Вы узнаете 2 самых быстрых способа вставить в Excel одинаковую формулу или текст сразу в несколько ячеек. Это будет полезно в таких ситуациях, когда нужно вставить формулу во все ячейки столбца или заполнить все пустые ячейки одинаковым значением (например, “Н/Д”). Оба приёма работают в Microsoft Excel 2013, 2010, 2007 и более ранних версиях.

Знание этих простых приёмов сэкономит Вам уйму времени для более интересных занятий.

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

Вот самые быстрые способы выделить ячейки:

Выделяем целый столбец

  • Если данные в Excel оформлены как полноценная таблица, просто кликните по любой ячейке нужного столбца и нажмите Ctrl+Space.

Примечание: При выделении любой ячейки в полноценной таблице на Ленте меню появляется группа вкладок Работа с таблицами (Table Tools).

  • Если же это обычный диапазон, т.е. при выделении одной из ячеек этого диапазона группа вкладок Работа с таблицами (Table Tools) не появляется, выполните следующие действия:

Замечание: К сожалению, в случае с простым диапазоном нажатие Ctrl+Space выделит все ячейки столбца на листе, например, от C1 до C1048576, даже если данные содержатся только в ячейках C1:C100.

Выделите первую ячейку столбца (или вторую, если первая ячейка занята заголовком), затем нажмите Shift+Ctrl+End, чтобы выделить все ячейки таблицы вплоть до крайней правой. Далее, удерживая Shift, нажмите несколько раз клавишу со Стрелкой влево, пока выделенным не останется только нужный столбец.

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

Выделяем целую строку

  • Если данные в Excel оформлены как полноценная таблица, просто кликните по любой ячейке нужной строки и нажмите Shift+Space.
  • Если перед Вами обычный диапазон данных, кликните последнюю ячейку нужной строки и нажмите Shift+Home. Excel выделит диапазон, начиная от указанной Вами ячейки и до столбца А. Если нужные данные начинаются, например, со столбца B или C, зажмите Shift и понажимайте на клавишу со Стрелкой вправо, пока не добьётесь нужного результата.

Выделяем несколько ячеек

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

Выделяем таблицу целиком

Кликните по любой ячейке таблицы и нажмите Ctrl+A.

Выделяем все ячейки на листе

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

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

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

Нажмите F5 и в появившемся диалоговом окне Переход (Go to) нажмите кнопку Выделить (Special).

В диалоговом окне Выделить группу ячеек (Go To special) отметьте флажком вариант Пустые ячейки (Blanks) и нажмите ОК.

Вы вернётесь в режим редактирования листа Excel и увидите, что в выбранной области выделены только пустые ячейки. Три пустых ячейки гораздо проще выделить простым щелчком мыши – скажете Вы и будете правы. Но как быть, если пустых ячеек более 300 и они разбросаны случайным образом по диапазону из 10000 ячеек?

Самый быстрый способ вставить формулу во все ячейки столбца

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

  1. Преобразуйте диапазон в таблицу Excel. Для этого выделите любую ячейку в диапазоне данных и нажмите Ctrl+T, чтобы вызвать диалоговое окно Создание таблицы (Create Table). Если данные имеют заголовки столбцов, поставьте галочку для параметра Таблица с заголовками (My Table has headers). Обычно Excel распознаёт заголовки автоматически, если это не сработало – поставьте галочку вручную.
  2. Добавьте новый столбец к таблице. С таблицей эта операция осуществляется намного проще, чем с простым диапазоном данных. Кликните правой кнопкой мыши по любой ячейке в столбце, который следует после того места, куда нужно вставить новый столбец, и в контекстном меню выберите Вставить > Столбец слева (Insert > Table Column to the Left).
  3. Дайте название новому столбцу.
  4. Введите формулу в первую ячейку нового столбца. В своём примере я использую формулу для извлечения доменных имён:

    =MID(C2,FIND(":",C2,"4")+3,FIND("/",C2,9)-FIND(":",C2,"4")-3)
    =ПСТР(C2;НАЙТИ(":";C2;"4")+3;НАЙТИ("/";C2;9)-НАЙТИ(":";C2;"4")-3)

  5. Нажмите Enter. Вуаля! Excel автоматически заполнил все пустые ячейки нового столбца такой же формулой.

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

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

Вставляем одинаковые данные в несколько ячеек при помощи Ctrl+Enter

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

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

  1. Выделите все пустые ячейки в столбце.
  2. Нажмите F2, чтобы отредактировать активную ячейку, и введите в неё что-нибудь: это может быть текст, число или формула. В нашем случае, это текст “_unknown_”.
  3. Теперь вместо Enter нажмите Ctrl+Enter. Все выделенные ячейки будут заполнены введёнными данными.

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

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

Добавить символ, слово или текст во все ячейки столбца

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

Добавить значение в начало или конец ячеек формулой

Можно решить задачу формулой «СЦЕПИТЬ» или простой конкатенацией через амперсанд (символ «&»). Но на это нужно много времени, т.к. нужно

  • добавить дополнительный столбец рядом с исходным
  • ввести формулу с функцией «СЦЕПИТЬ» или формулу с амперсандом в первую ячейку — здесь кому что больше нравится. =СЦЕПИТЬ(A1;» слово») или A1&» слово».
  • протянуть формулу до конца нового столбца, убедившись, что она затронула весь диапазон
  • заменить в нем формулы на значения
  • скопировать данные из нового столбца в первый
  • удалить новый столбец

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

формулы добавления символа к значению ячейки

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

=""""&A1&"""" 

«заключит» значение ячейки A1 в кавычки.

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

Добавить символ в конце каждой ячейки

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

Вставляем вопросительный знак в конце каждой ячейки столбца

Как добавить символ в конце и начале ячеек столбца

Допустим, у нас есть названия ООО, но они без кавычек и без «ООО».
Ниже пример, как ускорить процесс.
1. Добавления кавычек с обеих сторон ячейки — чтобы не производить две операции.

Добавить символы в начале каждой ячейки

2. Теперь добавим символы «ООО » в начале каждой ячейки, чтобы завершить задачу.

Добавление символов в начале каждой ячейки выделенного столбца

Добавить слово в каждую ячейку столбца

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

Выделяем столбец, склоняем ФИО, и далее выбираем процедуру в меню и вводим слово в окошко, вместе с пробелом после него:

Вставляем слова в начале и конце каждой ячейки выбранных столбцов в Excel с помощью !SEMTools

Хотите так же быстро выполнять данные процедуры и сделать это прямо сейчас?

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

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

Перемещение и копирование формулы — Excel

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


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


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

Перемещение формулы

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

  2. В группе «Буфер обмена» на вкладке «Главная» нажмите кнопку «Вырезать».

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

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


    • Чтобы вировать формулу илюбое форматирование: в группе «Буфер обмена» на вкладке «Главная» нажмите кнопку «Вировать».


    • Чтобы вклеитьтолько формулу: в группе «Буфер обмена» на вкладке «Главная» нажмите кнопку «Вировать», выберите «Специальная вклейка» и нажмите кнопку «Формулы».

Копирование формулы

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

  2. В группе «Буфер обмена» на вкладке «Главная» нажмите кнопку «Копировать».

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

    • Чтобы вировать формулу и любое форматирование, на вкладке «Главная» в группе «Буфер обмена» нажмите кнопку «Вировать».

    • Чтобы вклеить только формулу, в группе «Буфер обмена» на вкладке «Главная» нажмите кнопку «Вировать», выберите «Специальная вшка» и нажмите кнопку «Формулы».


      Примечание: В этом окте можно вклеить только результаты формулы. В группе «Буфер обмена» на вкладке«Главная» нажмите кнопку «Вировать», выберите «Специальная ветвь» и нажмите кнопку «Значения».

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

    1. Выделите ячейку с формулой.

    2. В строке формул строка формул

      выделите ссылку, которую нужно изменить.

    3. Для переключения между сочетаниями нажмите F4.

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






Копируемая формула


Первоначальная ссылка


Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

$A$1

A$1 (относительный столбец и абсолютная строка)

C$1

$A1 (абсолютный столбец и относительная строка)

$A3

A1 (относительный столбец и относительная строка)

C3


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

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

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

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

  2. Нажмите кнопку «>вырезать» (или нажмите CTRL+X).

  3. Выберите ячейку, в которая должна вться формула, и нажмите кнопку «Вировать» (или нажмите CTRL+V).

  4. Убедитесь, что ссылки на ячейки остаются нужными.


    Совет: Вы также можете щелкнуть ячейки правой кнопкой мыши, чтобы вырезать и вжать формулу.

Как протянуть (распространить) формулу в Excel: несколько способов

Рассмотрим несколько способов протягивания (копирования) формул в строках и столбцах программы Excel.

Первый способ: протянуть ячейку в столбце или строке.

Чтобы протянуть (распространить) формулу из одной ячейки в несколько ячеек столбца или строки следует выполнить ряд действий:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Нажать и удерживать левую кнопку мыши.
4. Не отпуская кнопку мыши потянуть крестик в нужном направлении. В ту сторону, в которую следует распространить значение ячейки.

Второй способ: быстрое протягивание формулы в один клик мыши.

Для быстрого заполнения столбца формулой или значением из ячейки достаточно выполнить следующие действия:
1. Записать функцию (формулу) в ячейку и нажать «ENTER».
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Кликнуть двойным кликом по правому нижнему углу ячейки.

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

Третий способ: протянуть формулу без изменения (смещения) исходных ячеек.

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

Четвертый способ: протянуть формулу при помощи копирования.

Подходит для работы под фильтрами.
Выполняется следующим образом:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Копировать значение ячейки при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»c».
3. Выделить нужный диапазон.
Для быстрого выделения вниз достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка вниз
Для быстрого выделения в сторону достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка в сторону

  1. После того, как выделили диапазон, следует вставить скопированную формулу при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»v».

Советы по копированию и вставке

Копировать данные в Smartsheet или между Smartsheet и другими программами можно разными способами. Их обзор представлен в таблице ниже.

ПРИМЕЧАНИЕ. В настоящее время копирование и вставка столбцов не поддерживаются.

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

Используйте команды контекстного меню либо сочетания клавиш CTRL+C для копирования и CTRL+V для вставки данных (COMMAND+C и COMMAND+V на компьютере Mac).

Пользователям Mac следует использовать клавишу COMMAND вместо CTRL.

Копирование данных между Smartsheet и другими программами, например Microsoft Excel.Сочетания клавиш

Используйте сочетания клавиш CTRL+C для копирования и CTRL+V для вставки данных (COMMAND+C и COMMAND+V на компьютере Mac).

ПРИМЕЧАНИЯ:

  • Команды контекстного меню для копирования и вставки не всегда работают правильно при переходе из одного приложения в другое.
  • Из других приложений для работы с электронными таблицами, таких как Excel или Open Office, можно вставить одновременно не более 500 строк.

СОВЕТ. Если вы скопировали ячейку с несколькими строками в Excel или другой программе, перед вставкой дважды щёлкните ячейку в Smartsheet, чтобы строки не были вставлены в нижние ячейки.

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

Копирование и вставка ячеек, строк, формул, иерархии и гиперссылок

Копирование и вставка ячеек (внутри таблицы или между таблицами)

Чтобы скопировать ячейку, щёлкните её правой кнопкой мыши и выберите пункт Копировать.

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

Чтобы вставить данные в ячейку, щёлкните её и нажмите клавиши CTRL+V (или щёлкните её правой кнопкой мыши и выберите пункт Вставить).

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

Копирование строк в пределах таблицы

ПРИМЕЧАНИЕ. Если вам нужно скопировать строки между разными таблицами, см. статью Копирование строки в другую таблицу.

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

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

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

ПРИМЕЧАНИЕ. При копировании строки вложения и обсуждения не копируются.

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

ПРИМЕЧАНИЕ. Если при вставке строки щёлкнуть ячейку, а не номер строки, значения ячеек будут перезаписаны.

Копирование и вставка формул

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

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

  1. Дважды щёлкните ячейку с формулой, чтобы открыть формулу для редактирования.
  2. Выделите формулу и нажмите клавиши CTRL+C, чтобы скопировать её.
  3. Откройте конечную таблицу, щёлкните нужную ячейку и нажмите клавиши CTRL+V.

ПРИМЕЧАНИЕ. Вам может потребоваться изменить ссылки на ячейки в соответствии с конечной таблицей.

Копирование и вставка иерархии

Разверните иерархию, если она свёрнута, щёлкнув значок развёртывания в родительской строке.

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

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


Специальная вставка

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

Скопировав ячейку или строку, щёлкните правой кнопкой мыши конечную ячейку и выберите пункт Специальная вставка. Откроется окно Специальная вставка.

Чтобы решить, какой вариант выбрать, обратитесь к этой таблице:

ПараметрЖелаемый результат
ЗначенияВставка скопированных значений. Если в скопированной ячейке содержится формула, будет вставлен только результат её вычисления, но не сама формула.
ФорматыВставка форматирования скопированной ячейки, например цвета фона, цвета шрифта, стиля шрифта и т. д.
Ссылки на скопированные ячейки

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

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


Заполнение перетаскиванием

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

СОВЕТ. Формула автоматически добавляется в ячейку, если она находится непосредственно над, под или между ячейками с такой же формулой. Дополнительные сведения об этом см. в статье Автоматическое заполнение формул и форматирование.

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

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

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

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

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

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

Как создать формулу, Операторы и Функции в Excel

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

Формулы Эксель

Из чего состоят формулы Эксель:

  1. Знак равно «=»

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

  1. Операторы

Операторы в Excel бывают четырех видов: арифметические, операторы сравнение, операторы объединения текста, операторы ссылок на ячейки.

  1. Функции

Функция – это предопределенная формула, выполняющая определенный тип вычислений. Например, функция СУММ выполняет суммирование определенных ячеек. Благодаря функциям сокращается и упрощается формула в Excel.

Как ввести формулу в Excel

Основным элементом программы Excel являются формулы. Формулы Эксель позволяют получать мгновенный результат её вычислений. При этом формула сразу делает перерасчет при изменении исходных значений.

Рассмотрим следующий пример:

В ячейки A1 и B1 поместим любые числа, например 8 и 5 соответственно. А в ячейку C1 введем формулу:

=A1*B1

Чтобы ввести эту формулу в таблице Excel необходимо выполнить строгую последовательность действий:

  1. Кликните по ячейке С1;
  2. Введите следующую формулу: =A1*B1
  3. В завершении нажмите Enter.

Можно поступить и по-другому.

  1. Кликните по ячейке С1;
  2. С помощью клавиатуры введите знак равно «=»;
  3. Кликните по ячейке A1

При этом в ячейке C1 появится ссылка на ячейку A1

  1. На клавиатуре нажмите символ звездочки «*»;

В Excel в качестве оператора умножения используется символ звездочки «*».

  1. Далее кликните мышкой по ячейке B1;

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

  1. В завершении нажмите Enter.

В ячейке C1 отобразится результат умножения ячеек A1 и B1.

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

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

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

Изменение естественного порядка операций

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

Для примера возьмем следующую формулу:

=A1-B1/C1

Заполним ячейки следующими цифрами: в ячейку A1 поставим число 8, в ячейке B1 — 6, а в ячейке C1 — 2. Таким образом получим такую формулу:

=8-6/2

Используя математические приоритеты, программа Excel сначала разделит 6 на 2, а затем от 8 отнимет 3. В итоге получится число 5.

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

=(A1-B1)/C1

Таким образом, мы даем команду программе сначала выполнить операцию вычитания в скобках, а затем разделить полученный результат. Таким образом, программа отнимет от 8 цифру 6 и разделит его на 2. В итоге формула выдаст совсем иной результат: 1.

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

=(А3+(В3+С3))*D3

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

Если бы скобок не было, то программа, по правилам математики, сначала бы умножила ячейки D3 и C3, а потом прибавила к полученному результату значения в ячейках B3 и A3.

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

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

Операторы сравнения

Данные операторы сравнивают одно значение с другим. В результате оператор сравнения выдаёт ИСТИНУ, если сравнение подтверждается, или ЛОЖЬ, если сравнение не подтверждается.

ЗнакОператорПример
знак «равенства»==A1=B2
знак «больше»>=C3>B1
знак «меньше»<=B2<B1
знак «больше или равно»>==A3>=D2
знак «меньше или равно»<==B3<=D1
знак «не равно»<>=A1<>B1

Оператор объединения текста

Чтобы объединить содержимое двух ячеек в таблице Excel необходимо использовать символ «&» (амперсанд). Таким же свойством обладает функция «СЦЕПИТЬ». Давайте рассмотрим несколько примеров:

  1. Для объединения текста или иного содержимого из разных ячеек в единое целое необходимо применить следующую формулу:

=A1&C1&E1

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

=A1&» «&C1&»; «&E1

  1. Объединить можно не только ячейки, но и слова внутри одной ячейки.

=»Водо»&»пад»

Запомните, что кавычки можно использовать только такие, как на скриншоте.

Операторы ссылок на ячейки

  1. Чтобы создать ссылку на диапазон ячеек достаточно ввести первую и последнюю ссылку на ячейки и между ними поставить знак «:» (двоеточие).

=СУММ(A11:A13)

  1. Если требуется указать ссылки на отдельные ячейки, то для этого применяют символ «;» (точка с запятой).

=СУММ(A11;A12;A13)

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

=F12:G12 G11:G13

Использование ссылок

В программе Excel существуют несколько видов ссылок на ячейки. Однако, не все пользователи про них знают. Большинство пользователей использует самые простые из них.

Итак, ссылки бывают следующих видов: простые ссылки, ссылки на другой лист, абсолютные ссылки, относительные ссылки.

Простые ссылки

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

В таблице Excel общее количество столбцов равно 16384 (от A до XFD), а строк 1048576.

Для закрепления рассмотрим следующие примеры:

  • диапазон ячеек в столбце A начиная с 1 по 10 строку – «A1:A10»;
  • диапазон ячеек в строке 3 начиная со столбца C до E – «C3:E3»;
  • все ячейки в строке 5 – «5:5»;
  • все ячейки в строках с 3 по 28 – «3:28»;
  • все клетки в столбце C – «C:C»;
  • все клетки в столбцах с D по G – «D:G».

Ссылки на другой лист

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

=СУММ(Лист2!A3:C3)

На Листе 2 введены следующие значения.

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

=СУММ(‘Лист № 2’!A3:C3)

Абсолютные и относительные ссылки в формулах Эксель

Относительные ссылки

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

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

=СУММ(B3:B6)

Далее скопируем данную формулу в ячейку C7.

При копировании исходной формулы Эксель в ячейку С7 программа немного изменяет формулу, после чего она приобретает такой вид:

=СУММ(СЗ:С6)

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

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

=CУMM(B3:D3)

При копировании этой формулы Эксель в ячейку Е4 программа создает следующую формулу:

=СУММ(В4:D4)

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

Абсолютные ссылки

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

Одним из самых распространенных исключений является сравнение ячеек некоторого диапазона с одним значением. Например, вам может потребоваться указать в ячейках объем продаж каждого из подразделений относительно общего объема продаж компании в целом. На рабочем листе объемов продаж компании “Наш концерн” такая ситуация возникает при копировании формулы Эксель, вычисляющей, какой процент составляют ежемесячные объемы (ячейки B9:D9) в ежеквартальном объеме продаж (ячейка Е7).

Предположим, что мы начинаем ввод этих формул в строке 9 с ячейки В9. Формула в этой ячейке вычисляет процент продаж в январе (В7) относительно квартального (Е7) методом деления. Что может быть проще?

=В7/Е7

Эта формула делит итог январских продаж (в ячейке В7) на квартальный итог в ячейке Е7. А теперь посмотрите, что произойдет, если перетащить маркер заполнения на одну ячейку вправо, чтобы скопировать формулу в ячейку С9:

=C7/F7

Корректировка ячейки числителя с В7 на С7 — это как раз то, что доктор прописал. Тем не менее изменение второго указателя ячейки c E7 на F7 — это уже катастрофа. Вы не только не сможете вычислить процентное соотношение февральских продаж в ячейке С9 относительно итоговых продаж первого квартала в ячейке Е7, но и получите в итоге ужасную ошибку #ДЕЛ/0! (#DIV/0!) в ячейке С9.

Чтобы предотвратить изменение ссылки на ячейку во всех создаваемых копиях формулы Эксель, нужно преобразовать ссылку из относительной в абсолютную. Это выполняется с помощью клавиши <F4> после переключения Excel в режим редактирования (с помощью клавиши <F2>). В ответ на это программа помещает перед буквой столбца и номером строки в формуле знаки доллара. В качестве примера рассмотрим скриншот ниже. Ячейка В9 на этом рисунке содержит корректную формулу, которую уже можно копировать в диапазон ячеек C9:D9:

=B7/$E$7

Посмотрим теперь на эту формулу в ячейке С9 после копирования в диапазон C9:D9 методом перетаскивания. В строке формул отображается следующее:

=С7/$Е$7

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

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

  1. Дважды щелкните на ячейке с формулой или нажмите клавишу <F2>, чтобы приступить к редактированию.
  2. Переместите точку вставки к ссылке, которую хотите преобразовать в абсолютную.
  3. Нажмите клавишу <F4>.
  4. Когда закончите редактирование, щелкните на кнопке Ввод в строке формул, а затем скопируйте ее в диапазон ячеек путем перетаскивания маркера заполнения.

Нажимайте клавишу <F4> только тогда, когда необходимо преобразовать ссылку на ячейку в полностью абсолютную. Если нажмете клавишу <F4> второй раз, то получите так называемую смешанную ссылку, в которой строка абсолютна, а столбец относителен (например, Е$7). Если нажмете клавишу <F4> еще раз, то получите другой тип смешанной ссылки, в которой столбец абсолютен, а строка относительна (например, $Е7). Если же нажать клавишу <F4> еще раз, ссылка станет полностью относительной (например, Е12). Таким образом, вы вернетесь к тому, с чего начали. Последующие нажатия клавиши <F4> повторят вышеописанный цикл преобразований.

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

Формулы Эксель: Использование функций

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

Функцией называют предопределенную формулу, выполняющую определенный тип вычислений. Ей необходимо передать значения, используемые в операции (они называются аргументами). Как и в простых формулах, аргументами функций могут быть числа (например, 22 или -4,56), а также ссылки на ячейки (В10) или диапазоны ячеек (СЗ: РЗ).

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

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

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

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

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

Вставка функции в формулу с помощью мастера

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

Диалоговое окно мастера функций содержит текстовое поле Поиск функции (Search for a Function), а также списки Категория (Or Select a Category) и Выберите функцию (Select a Function). Когда открывается окно вставки функции, автоматически выбирается категория десяти недавно использованных функций.

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

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

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

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

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

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

Функция СУММ может суммировать до 255 аргументов. Совершенно очевидно, что все они находятся далеко не в одной ячейке. На практике вам придется чаще всего суммировать значения, содержащиеся в соседних ячейках.

Для того чтобы выбрать первый аргумент функции, щелкните на ячейке рабочего листа или перетащите указатель мыши по диапазону ячеек. В текстовом поле Число1 (Numberl) программа отобразит адрес ячейки (или диапазон адресов), а в нижней части окна, в поле Значение (Formula result), появится результат вычислений.

Имейте в виду, что во время выбора ячеек на рабочем листе диалоговое окно аргументов можно свернуть; при этом отображаться будет только поле Число! Чтобы свернуть окно аргументов, щелкните на кнопке, расположенной справа от поля Число1. После этого можно выделить диапазон ячеек и щелкнуть на кнопке восстановления окна (в свернутом окне эта кнопка будет единственной) или нажать клавишу <Esc>. Вместо свертывания можете переместить это окно в любое свободное место экрана.

Если на рабочем листе заполнено множество ячеек, щелкните на поле Число2 или нажмите клавишу <Tab>. (Excel отреагирует на это, открыв поле Число3.) В поле Число2 введите аналогичным образом второй диапазон ячеек, только на этот раз для сворачивания окна щелкайте на кнопке рядом с этим полем. В поле результата вычислений появится сумма уже двух диапазонов значений. При желании можете выделить несколько таких диапазонов (Число2, Число3, Число4 и т.д.).

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

Редактирование функций с помощью мастера

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

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

Учтите, что Excel автоматически добавляет для текущего аргумента ячейку (или диапазон), выделенную на рабочем листе. Если хотите заменить текущий аргумент, то выделите его и нажмите клавишу <Delete>, а затем выделите новый диапазон ячеек. (Не забывайте, что в любой момент можно свернуть это окно или переместить в другое место экрана, если оно перекрывает ячейки, которые нужно выделить.)

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

Формулы Эксель: Операции с формулами

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

Если вам нужно скопировать формулу из одной ячейки в другую достаточно воспользваться всем известной комбинацией клавиш <Ctrl+C> (копировать) и <Ctrl+V> (вставить). Для этого выделите нужную вам ячейку, кликнув по ней курсором мыши, нажмите комбинацию клавиш Ctrl+C, при этом контуры ячейки будут выделены пунктирной линией. Затем выделите ту ячейку, в которую нужно вставить значение из первой ячейки и нажмите комбинацию клавиш Ctrl+V. Всё содержимое из первой ячейки скопируется во вторую ячейку.

Отмена операций

Прежде чем начинать редактировать только что открытую рабочую книгу, следует узнать о функции отмены операций и о том, как она может спасти случайно удален­ные данные. Кнопка Отменить (Undo) панели быстрого доступа — настоящий “ха­мелеон”: она приспосабливается к выполненным вами действиям. Например, если вы случайно удалили содержимое группы ячеек, нажав клавишу <Delete>, то экранная подсказка этой кнопки будет гласить “Отменить очистку (Ctrl+Z)”. Если вы перета­щили диапазон ячеек в другую часть рабочего листа, подсказка изменится на “Отме­нить перетаскивание”.

Для использования этой команды можно не только щелкать на кнопке панели бы­строго доступа, но и нажимать комбинацию клавиш <Ctrl+Z>.

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

Повторение действий

После выполнения команды Отменить программа активизирует кнопку Вернуть (Redo), находящуюся непосредственно справа от нее. Если вы удалили содержимое ячейки с помощью клавиши <Delete>, а затем щелкнули на кнопке Отменить (или нажали комбинацию клавиш <Ctrl+Z), то экранная подсказка, отображаемая при по­мещении указателя мыши над кнопкой Вернуть, будет гласить: “Вернуть очистку (Ctrl+Y)”.

Если теперь щелкнуть на кнопке Вернуть или нажать комбинацию клавиш <Ctrl+Y>, то Excel повторит только что отмененную операцию. На самом деле все звучит намного сложнее, чем есть на самом деле.Закрыть) и в ответ на запрос указать, что изменения сохранять не следует.

Старое доброе перетаскивание

Первой методикой редактирования, которую следует освоить, является перета­скивание (drag-and-drop). Как следует из названия, эта методика предполагает ис­пользование указателя мыши, который переносит выделение ячеек и оставляет его в другом месте рабочего листа. Несмотря на то что перетаскивание в основном исполь­зуется для перемещения содержимого ячеек в пределах рабочего листа, его можно применять и для копирования данных.

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

  1. Выделите диапазон ячеек.
  2. Поместите указатель мыши (либо палец или стилус при работе с сенсор­ным экраном) над одной из границ выделенного диапазона.

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

Перетащите выделенный диапазон в требуемое место. Перетаскивание выпол­няется путем нажатия главной (обычно левой) кнопки мыши и ее удерживания во время перетаскивания.

Во время перетаскивания вы перемещаете только контур диапазона, a Excel в экранной подсказке информирует о том, какими будут адреса нового диапазо­на, если вы в данный момент отпустите кнопку мыши.

Перетаскивайте контур до тех пор, пока этот диапазон не совпадет с требуемым.

  1. Отпустите кнопку мыши (либо оторвите палец или стилус от сенсорного экрана).
  2. Как только отпустите кнопку мыши, содержимое ячеек выделенного диа­пазона отобразится в новом месте.

Копирование путем перетаскивания

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

  1. Выделите диапазон ячеек.

В данном примере этим диапазоном будет А1:Е2.

  1. Удерживая нажатой клавишу <Ctrl>, поместите указатель мыши на гра­ницу выделенного фрагмента.

Указатель мыши примет вид четырехнаправленной стрелки с расположенным справа знаком “плюс” (к тому же рядом вы увидите экранную подсказку). Знак “плюс” свидетельствует о том, что выполняться будет не перемещение, а копи­рование.

  1. Перетащите контур выделенного диапазона в нужное место и отпустите кнопку мыши.

Если при перетаскивании ячеек перемещаемый контур перекрывает уже заполненные ячейки, то Excel откроет окно предупреждения с вопросом о том, хотите ли вы заменить их содержимое. Чтобы избежать замены существующего содержимого и отменить операцию перетаскивания, в окне предупреждения щелкните на кнопке Отмена; чтобы продолжить операцию, щелкните на кнопке ОК или нажмите клавишу <Enter>.

Особенности вставки при перетаскивании

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

Чтобы вставить перетаскиваемый диапазон ячеек в уже заполненный без замеще­ния прежнего содержимого, во время перетаскивания удерживайте нажатой клавишу <Shift>. (При копировании придется проявить немалую ловкость, чтобы одновремен­но удерживать нажатыми клавиши <Shift> и <Ctrl>.)

Если во время перетаскивания удерживать нажатой клавишу <Shift>, то при пере­мещении отображается не контур области, а вертикальный отрезок, указывающий место потенциальной вставки, наряду с экранной подсказкой с текущими адреса­ми, куда в результате будет вставлено содержимое ячеек. Обратите внимание на то, что во время перемещения отрезок пытается прикрепиться к ближайшим границам столбцов и строк. Когда вы достигнете границы того диапазона, в который должно быть вставлено содержимое, отпустите кнопку мыши. Excel вставит диапазон ячеек, переместив ранее существовавшее содержимое в ближайшие свободные ячейки.

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

Но я ведь удерживал нажатой клавишу <Shift>, как вы и говорили…

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

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

Копирование методом перетаскивания (с удерживанием нажатой клавиши <Ctrl>) особенно полезно, когда нужно скопировать большой диапазон ячеек в другую часть рабочего листа. Однако зачастую нужно скопировать всего одну формулу в массу со­седних ячеек, чтобы в них выполнялся тот же тип вычислений (например, суммиро­вание значений в столбце). И хотя такой способ копирования формул является до­статочно распространенным, его невозможно выполнить методом перетаскивания. Вместо этого используется функция автозаполнения или последователь­ность команд Копировать и Вставить.

Не забывайте о параметре Итоги (Totals) панели инструментов быстрого анализа. С его помощью можно мгновенно создавать строку или столбец итогов, находящийся в нижней или в правой части таблицы данных соответственно. Просто выделите та­блицу как диапазон ячеек и щелкните на кнопке Быстрый анализ (Quick Analysis), а затем на панели инструментов быстрого анализа выберите параметр Итоги. Если щелкнуть на кнопке Сумма (Sum), находящейся в начале панели, то будет создана формула, которая подсчитывает сумму по столбцам и отображает ее в новой стро­ке (в нижней части таблицы). Если же щелкнуть на кнопке Сумма, находящейся в правом конце панели инструментов, то будут созданы формулы Эксель, подсчитывающие суммы по строкам и выводящие результат в новом столбце (в правом конце таблицы).

Формулы Эксель: Заключение

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

Continue Reading

Глава 5 – Табличный процессор Calc — Документация Краткое руководство по LibreOffice 4.3

Что такое Calc?

Calc – это компонент для работы с электронными таблицами из состава LibreOffice. В электронную таблицу можно вводить данные (обычно числа) и манипулировать этими данными для получения определённого результата.

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

Другие возможности, представленные в Calc, включают в себя:

  • Функции, которые могут быть использованы при создании формул, для выполнения сложных вычислений на основе данных.
  • Функции баз данных, чтобы организовывать, хранить и фильтровать данные.
  • Динамические диаграммы; широкий спектр 2D и 3D диаграмм.
  • Макросы для записи и исполнения повторяющихся задач. В поставку включена поддержка для языков программирования Basic, Python, BeanShell и JavaScript.
  • Возможность открывать, редактировать и сохранять файлы в формате Microsoft Excel.
  • Импорт и экспорт электронных таблиц во множество форматов, включая HTML, CSV, PDF и PostScript.

Примечание

При необходимости использовать макросы в LibreOffice, написанные в Microsoft Excel с помощью VBA, сначала нужно изменить код макроса в редакторе LibreOffice Basic IDE. Для получения более подробной информации смотрите Главу 13 данного руководства, Приступая к работе с макросами, и Главу 12 Руководства по Calc, Макросы Calc.

Таблицы, листы и ячейки

Calc работает с элементами, называемыми таблицами. Таблицы состоят из ряда отдельных листов, каждый лист содержит ячейки, расположенные в строках и столбцах. Положение (адрес) каждой конкретной ячейки определяется буквой столбца и номером её строки.

Ячейки содержат отдельные элементы – текст, числа, формулы и так далее, которые являются данными для отображения и манипулирования.

В каждой таблице может быть несколько листов, и каждый лист имеет максимум 1048576 строк и максимум 1024 столбца.

Главное окно Calc

При запуске Calc становится доступным главное окно программы (рисунок 1). Различные части этого окна будут описаны ниже.

Заголовок окна

Заголовок окна расположен в верхней части окна и показывает название текущей таблицы (документа). Если открыта новая таблица, то название её будет Без имени Х, где Х — это номер по порядку. Если таблица сохраняется в первый раз, то необходимо будет задать ей имя.

Панель меню

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

Главное окно Calc

  • Файл – содержит команды, применяемые ко всему документу. Например, Открыть, Сохранить, Мастер, Экспорт в PDF, Печать, Цифровая подпись и другие.
  • Правка – содержит команды для редактирования документа. Например, Отменить, Копировать, Изменения, Подключаемый модуль и другие.
  • Вид – содержит команды для изменения интерфейса Calc. Например, Панели инструментов, Заголовки столбцов и строк, Во весь экран, Масштаб и другие.
  • Вставка – содержит команды для вставки элементов в таблицу. Например, Ячейки, Строки, Столбцы, Лист, Изображение и другие.
  • Формат – содержит команды для изменения разметки таблицы. Например, Ячейка, Страница, Стили и форматирование, Выравнивание и другие.
  • Сервис – содержит различные функции для проверки и настройки таблицы. Например, Параметры, Проверка орфографии, Совместно использовать документ, Галерея, Макросы и другие.
  • Данные – содержит команды для манипулирования данными в вашей таблице. Например, Задать диапазон, Сортировка, Объединить и другие.
  • Окно – содержит команды для отображения окон. Например, Новое окно, Разбить и другие.
  • Справка – содержит ссылки на справочную систему встроенную в программу и другие разнообразные функции. Например, Справка, Лицензия, Проверка обновлений и другие.

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

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

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

Стандартный набор значков на панели инструментов предоставляет широкий спектр общих команд и функций. Значки на панелях служат для быстрого доступа к командам, которые также доступны через меню программы. Можно удалять или добавлять значки на панели инструментов. Для получения дополнительных сведений смотрите Главу 14 – Настройка LibreOffice данного руководства.

Панель формул

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

Панель формул

Панель формул состоит из следующих элементов (слева направо на рисунке Панель формул):

  • Поле Имя – указывает ссылку на ячейку, используя комбинацию букв и цифр, например А1. Буква указывает столбец, а цифра – номер строки выбранной ячейки. Также можно задать собственное название ячейки.
  • Мастер функций – открывает диалог, в котором можно просмотреть список всех доступных функций. Каждая функция содержит подробное описание с указанием всех возможных параметров.
  • Сумма – нажмите на значок Сумма, чтобы посчитать сумму в заранее выделенных ячейках. Сумма будет вставлена в ячейку ниже выделенного диапазона.
  • Функция – нажатие на значок Функция вставит знак равенства (=) в выбранную ячейку и в Строку ввода, что позволит начать ввод формулы.
  • Строка ввода – отображает и позволяет редактировать содержимое выбранной ячейки (данные, формулы или функции).

Также можно изменять содержимое ячейки прямо в ячейке, дважды нажав на неё левой кнопкой мыши. При вводе новых данных в ячейку, значки Сумма и Функция в строке ввода изменятся на значки Отменить и Принять .

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

Совет

Ввод формул вручную всегда начинается с ввода знака равно =.

Примечание

В электронных таблицах Calc термин «функция» охватывает гораздо больше, чем просто математические функции. Смотрите Руководство по Calc, Глава 7, Использование формул и функций для получения дополнительных сведений.

Разметка таблицы

Ячейки

Основная часть рабочей области в Calc отображается ячейками в виде сетки. Каждая ячейка образована пересечением столбцов и строк в электронной таблице.

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

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

Ярлыки листов

В одном файле Calc может содержаться более, чем один Лист. В нижней части рабочей области в электронной таблице находятся ярлыки листов с указанием номера и названия каждого листа в таблице. Нажатие левой кнопкой мыши на ярлык позволяет получить доступ к каждому отдельному листу и отображает этот лист. Активный лист обозначается белым цветом (согласно настройкам по умолчанию для Calc). Также можно выбрать несколько листов, удерживая нажатой клавишу Ctrl, и нажимая на ярлыки листов.

Ярлыки листов

Чтобы изменить имя листа (по умолчанию им присваиваются имена Лист1, Лист2 и так далее), нажмите правой кнопкой мыши на ярлыке листа и выберите пункт Переименовать лист из контекстного меню. Откроется диалоговое окно, в котором можно ввести новое название для листа. Нажмите OK, чтобы закрыть диалоговое окно.

Чтобы изменить цвет ярлыка листа, также нажмите на него правой кнопкой мыши и выберите пункт Цвет ярлыка из контекстного меню, чтобы открыть диалог выбора цвета (рисунок Диалог выбора цвета ярлыка листа). Выберите цвет и нажмите кнопку ОК, чтобы закрыть диалоговое окно. Для получения информации о добавлении новых цветов к цветовой палитре смотрите Главу 14, Настройка LibreOffice данного руководства.

Диалог выбора цвета ярлыка листа

Строка состояния

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

Строка состояния

Боковая панель

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

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

Боковая панель доступна во всех компонентах LibreOffice. В некоторых компонентах она имеет дополнительные вкладки. В Calc она содержит следующий набор вкладок:

  • Меню конфигурации боковой панели;
  • Свойства;
  • Стили и форматирование;
  • Галерея;
  • Навигатор;
  • Функции (только в Calc).

Боковая панель

  • Меню конфигурации боковой панели – содержит настройки самой боковой панели. При необходимости позволяет включать/выключать отображение тех или иных вкладок.

  • Вкладка Свойства содержит четыре области:

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

  • Вкладка Галерея – аналогична диалогу Галерея ( )

  • Вкладка Навигатор – аналогична диалогу Навигатор ( или F5)

  • Вкладка Функции – содержит набор функций, доступных также в диалоге .

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


Открытие файлов в формате CSV

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

Чтобы открыть файл CSV в Calc:

  1. Выберите пункт меню и найдите файл в формате CSV, который нужно открыть.
  2. Выберите файл и нажмите кнопку Открыть. По умолчанию файл CSV имеет расширение .csv. Также файл CSV может быть с расширением .txt или не иметь его вообще.
  3. Откроется диалог Импорт текста (рисунок ниже), в котором можно выбрать несколько настроек, доступных при импорте файлов CSV в таблицы Calc.
  4. Нажмите кнопку OK, чтобы открыть и импортировать файл.

Диалог Импорт текста

Различные опции для импорта файлов CSV в электронную таблицу Calc описаны ниже:

Импорт

  • Кодировка – определяет набор символов, который будет использоваться в импортируемом файле.
  • Язык – определяет, как импортируются цифровые строки. Если язык для импорта CSV установлен в значение По умолчанию, Calc будет использовать язык, установленный в общих настройках. Если язык установлен конкретно (например «английский», при значении по умолчанию «русский»), этот язык будет использоваться при импорте чисел.
  • Со строки – определяет строку, с которой начнётся импорт. Строки видны в окне предварительного просмотра в нижней части диалогового окна.

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

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

Примечание

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

  • Объединять разделители – сочетает в себе последовательные разделители и удаляет пустые поля данных.
  • Разделитель текста – задаёт символ для разграничения текстовых данных.

Другие параметры

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

  • Распознавать особые числа – если эта опция активна, Calc автоматически обнаружит все числовые форматы, в том числе специальные числовые форматы такие, как дата, время и экспоненциальное представление.

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

    Если эта опция отключена, Calc будет обнаруживать и конвертировать только десятичные числа. Остальные, в том числе числа, представленные в экспоненциальном представлении, будут импортированы в виде текста. Десятичное число может содержать цифры от 0 до 9, разделители тысяч и десятичные разделители. Разделители тысяч и десятичные разделители могут изменяться в зависимости от выбранного языка и региона.

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

  • Тип столбца – выберите столбец в окне предварительного просмотра и выберите тип данных, который будет применяться к импортируемым данным.
  • Стандарт – Calc определяет тип данных.
  • Текст – импортирует данные, как текст.
  • Английский США – числа, отформатированные на языке Английский США ищутся и включаются независимо от языка системы. Формат числа не применяется. Если нет записей c настройкой Английский США, то применяется стандартный формат.
  • Скрыть – данные в указанных столбцах не будут импортированы.
  • Дата (ДМГ)/(МДГ)/(ГМД) – определяет формат вывода дат: Д – День; М – Месяц; Г – Год.

Сохранение электронных таблиц

Смотрите раздел Сохранение документов для ознакомления с основами сохранения документов в LibreOffice. Также Calc может сохранять таблицы в различных форматах и экспортировать таблицы в форматы PDF, HTML и XHTML. Для получения подробной информации смотрите Главу 6 – Печать, Экспорт и Рассылка электронной почтой руководства по Calc.

Сохранение электронных таблиц в других форматах

По умолчанию LibreOffice сохраняет электронные таблицы в формате *.ods (входит в состав формата ODF). Для сохранения электронных таблиц в других форматах необходимо:

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

Выбор формата сохранения

При сохранении в форматах отличных от *.ods будет выведен диалог подтверждения формата сохранения. Чтобы этот диалог больше не появлялся, необходимо снять галочку напротив Спрашивать при сохранении не в ODF формат.

Подтверждение сохранения в формате, отличном от ODF

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

Экспорт в текстовый файл

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

Изменение формата сохранения по умолчанию

Навигация в электронных таблицах

Calc предоставляет множество способов навигации по электронной таблице от ячейке к ячейке и с одного листа на другой лист. Можно использовать любой метод.

Навигация по ячейкам

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

  • Использование мыши – поместите курсор мыши на ячейку и нажмите левой кнопкой мыши. Для перемещения выделения в другую ячейку с помощью мыши, просто переместите указатель мыши к нужной ячейке и нажмите левую кнопку мыши.
  • Использование ссылок на ячейки – выделение или удаление существующей ссылки на ячейку в поле Имя (смотрите рисунок Панель формул) на панели формул. Введите новую ссылку на нужную ячейку и нажмите клавишу Enter на клавиатуре. Ссылки на ячейки не зависят от регистра, например, при наборе не будет разницы между a3 или A3, выделена будет ячейка A3. Не забывайте, что в координатах ячеек используются только латинские буквы.
  • Использование Навигатора – нажмите на значок Навигатор на стандартной панели или нажмите клавишу F5 (), чтобы открыть Навигатор. Введите ссылку на ячейку в полях Столбец и Строка и нажмите клавишу Enter.

Изменение формата сохранения по умолчанию (ТЫ НИЧЕГО НЕ НАПУТАЛ С НАЗВАНИЕМ РИСУНКА?!) 18 же с таким идет!

  • Использование клавиши Enter – нажимайте клавишу Enter, чтобы перемещать выделение ячейки вниз по столбцу на следующую строку. Нажимайте сочетание клавиш Shift+Enter, чтобы перемещать выделение ячейки вверх по столбцу на предыдущую строку.

  • Использование клавиши Tab – нажимайте клавишу Tab, чтобы перемещать выделение ячейки вправо по строке на следующий столбец. Нажимайте сочетание клавиш Shift+Tab, чтобы перемещать выделение ячейки влево по строке на предыдущий столбец.

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

  • Использование клавиш Home, End, Page Up и Page Down

    • Home перемещает выделение в начало строки (крайняя левая ячейка строки).
    • End перемещает выделение вправо по текущей строке, в ячейку на пересечении с крайним правым столбцом, содержащим данные.
    • Page Down перемещает выделение вниз на высоту экран.
    • Page Up перемещает выделение вверх на высоту экран.

Навигация по листам

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

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

Навигация по листам таблицы

  • Использование клавиатуры – используйте сочетания клавиш Ctrl+Page Down, чтобы перейти к листу, расположенному справа от текущего и Ctrl+Page Up – к листу слева от текущего.
  • Использование мыши – нажмите на один из ярлыков листа, расположенным внизу таблицы, чтобы перейти к нему, либо нажмите правой кнопкой мыши по стрелкам слева от ярлыков листов и из контекстного меню выберите нужный лист.

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

Навигация при помощи клавиатуры

Нажатие некоторых клавиш или сочетаний клавиш позволяет перемещаться по таблице с помощью клавиатуры. Сочетания клавиш – это нажатие одновременно более одной клавиши, например используйте комбинацию клавиш Ctrl + Home, чтобы перейти к ячейке A1. В таблице ниже представлены клавиши и сочетания клавиш, которые используются для навигации в таблицах Calc. Подробнее об общих сочетаниях клавиш смотрите _KeyboardShortcuts.

Клавиши и сочетания клавишРезультат
Стрелка вправо Перемещает выделение на ячейку вправо
Стрелка влево Перемещает выделение на ячейку влево
Стрелка вверх Перемещает выделение на ячейку вверх
Стрелка вниз Перемещает выделение на ячейку вниз
Ctrl+→

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

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

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

Ctrl+←

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

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

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

Ctrl+↑

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

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

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

Ctrl+↓

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

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

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

Ctrl+HomeПеремещение выделения на ячейку А1 текущего листа
Ctrl+EndПеремещает выделение из любой ячейки листа на крайнюю нижнюю правую ячейку листа с данными.
Alt+Page DownПеремещает выделение на один экран вправо (если возможно).
Alt+Page UpПеремещает выделение на один экран влево (если возможно).
Ctrl+Page DownПеремещает выделение на следующий лист справа от текущего, если таблица имеет более одного листа.
Ctrl+Page UpПеремещает выделение на следующий лист слева от текущего, если таблица имеет более одного листа.
TabПеремещает выделение на следующую ячейку справа от текущей
Shift+TabПеремещает выделение на следующую ячейку слева от текущей
EnterВниз на одну ячейку (если не изменено в настройках пользователем)
Shift+EnterВверх на одну ячейку (если не изменено в настройках пользователем)

Настройка действия при нажатии клавиши Enter

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

Настройка действия при нажатии клавиши Enter


Выбор элементов в таблице

Выбор ячеек

Одна ячейка

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

Диапазон смежных ячеек

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

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

  1. Нажмите на ячейке.
  2. Нажмите и удерживайте нажатой левую клавишу мыши.
  3. Перемещайте мышь.
  4. Как только желаемый диапазон ячеек будет выделен, отпустите левую кнопку мыши.

Выбор диапазона ячеек без перетаскивания мышью:

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

Выбор диапазона ячеек

Совет

Также можно выбрать диапазон ячеек, нажав сначала на первую ячейку диапазона, и, выбрав в области Режим выбора в строке состояния (рисунок ch5-lo-screen-012) режим Расширяемое выделение. Затем достаточно просто нажать на последнюю ячейку диапазона. Чтобы вернуться к обычному режиму выделения, снова выберите в строке состояния режим Обычное выделение.

Чтобы выбрать диапазон ячеек без помощи мыши:

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

Совет

Также можно выделить диапазон ячеек, используя поле Имя на панели формул (рисунок Панель формул). Чтобы выбрать диапазон ячеек, введите ссылку на верхнюю левую ячейку диапазона, вставьте двоеточие (:) и введите ссылку на нижнюю правую ячейку диапазона. Например, чтобы выбрать диапазон ячеек от A3 до C6, нужно ввести A3:C6.

Диапазон не смежных ячеек

Чтобы выделить несколько диапазонов, выполните следующие шаги:

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

Выбор нескольких диапазонов

Выделение столбцов и строк

Один столбец или одна строка
  • Чтобы выделить один столбец, нажмите на его заголовок.
  • Чтобы выделить одну строку, нажмите на её заголовок.
Несколько столбцов или строк

Чтобы выделить несколько столбцов или строк, которые являются смежными:

  1. Нажмите на первый столбец или строку из группы.
  2. Нажмите и удерживайте клавишу Shift.
  3. Нажмите на последний столбец или строку из группы.

Выделение нескольких столбцов

Чтобы выделить несколько столбцов или строк, которые не являются смежными:

  1. Нажмите на первый столбец или строку из группы.
  2. Нажмите и удерживайте клавишу Ctrl.
  3. Нажмите на все необходимые столбцы и строки по очереди.
Весь лист

Чтобы выделить весь лист, нажмите на поле между заголовками столбцов и строк или используйте комбинацию клавиш Ctrl+A, также можно воспользоваться пунктом меню .

Поле Выделить всё

Выбор листов

Можно выбрать один или несколько листов в Calc. Это может быть удобно, если требуется внести изменения в несколько листов сразу.

Один лист

Нажмите на ярлык листа для его выбора. Ярлык выбранного листа окрашен в белый цвет (согласно настроек Calc по умолчанию).

Несколько смежных листов

Чтобы выбрать несколько смежных листов:

  1. Нажмите на ярлык первого листа из желаемых.
  2. Переместите курсор мыши на ярлык последнего из желаемых листов.
  3. Нажмите и удерживайте клавишу Shift и нажмите на ярлык последнего листа.
  4. Все ярлыки между этими двумя ярлыками выделятся и станут белого цвета (согласно настроек Calc по умолчанию). Любые действия, которые вы будете выполнять, повлияют на все выделенные листы.
Несколько не смежных листов

Чтобы выделить несколько не смежных листов:

  1. Нажмите на ярлык первого листа из желаемых.
  2. Переместите курсор мыши на ярлык следующего из желаемых листов.
  3. Нажмите и удерживайте клавишу Ctrl и нажмите на ярлык листа.
  4. Повторите пункты 2 и 3 для каждого требуемого листа.
  5. Выделенные ярлыки листов будут белого цвета (согласно настроек Calc по умолчанию). Любые действия, которые будут выполняться в таблице, повлияют на все выделенные листы.
Все листы

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


Работа со столбцами и строками

Вставка столбцов и строк

Примечание

При вставке столбца, он вставляется слева от текущего столбца. При вставке строки, она вставляется выше текущей строки.

Ячейки вставляемых столбца или строки будут отформатированы так, как соответствующие ячейки из столбца слева и из строки выше вставленных.

Один столбец или строка

С использованием меню Вставка:

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

С использованием мыши:

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

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

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

Удаление столбцов и строк

Один столбец или строка

Чтобы удалить один столбец или строку:

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

Диалог Удалить ячейки

Можно сделать иначе:

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

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

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

Работа с листами

Вставка новых листов

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

Диалог Вставить лист

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

Перемещение и копирование листов

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

Перетаскивание

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

Чтобы скопировать лист в пределах одного файла, удерживая клавишу Ctrl (клавиша Option в MacOS X), нажмите на ярлык листа, перетащите лист на новое место и отпустите клавишу мыши. Курсор мыши может измениться на знак плюс в зависимости от настроек вашей операционной системы.

Использование диалога

Диалог Переместить/скопировать лист (на рисунке ниже) позволяет точно указать, куда поместить лист: в этот же или в другой документ; его позицию в документе; имя листа при перемещении или копировании листа:

  1. В текущем документе нажмите правой кнопкой мыши по ярлыку листа, который требуется переместить или скопировать и выберите из контекстного меню пункт Переместить/копировать или откройте пункт меню .
  2. Выберите Переместить или Копировать для совершения соответствующих действий.
  3. Выберите документ, в который нужно переместить или скопировать лист, из выпадающего списка в разделе В документ. Там можно выбрать текущий документ, любой открытый в настоящий момент документ или создать новый документ.
  4. Выберите позицию для перемещаемого или копируемого листа из списка Вставить перед.
  5. Введите имя для перемещаемого или копируемого листа в поле Новое имя, если его нужно изменить. Если имя не ввести, то Calc создаст имя по умолчанию (Лист 1, Лист 2 и так далее).
  6. Нажмите кнопку OK, чтобы подтвердить перемещение или копирование листа и закрыть диалог.

Диалог Переместить/скопировать лист

Предупреждение

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

Удаление листов

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

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

Переименование листов

По умолчанию листам присваиваются имена вида ЛистХ, где Х — это номер по порядку. Можно переименовать лист, используя один из следующих методов:

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

Примечание

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


Внешний вид Calc

Настройка вида документа

Используйте функцию масштабирования, чтобы показать больше или меньше ячеек в окне при работе с таблицей. Более подробную информацию о масштабировании смотрите в главе Chapter-1-Introducing-LibreOffice данного руководства.

Фиксирование строк и столбцов

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

На рисунке Фиксированные строки и столбцы показаны несколько фиксированных строк и столбцов. Более жирная горизонтальная линия между строками 3 и 23 и более жирная вертикальная линия между столбцами F и Q указывают, что строки с 1 по 3 и столбцы от A до F зафиксированы. Строки с 3 до 23 и столбцы между F и Q были прокручены.

Фиксированные строки и столбцы

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

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

Разделение экрана

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

Разделение окна документа на 4 области

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

Горизонтальное или вертикальное разделение
  1. нажмите на заголовке строки, которая ниже строк в месте разделения экрана по горизонтали или нажмите на заголовке столбца справа от столбцов в месте разделения экрана по вертикали.
  2. Откройте меню Окно и выберите пункт Разбить. Жирная линия появится между строками или столбцами, указывающая, в каком месте было создано разделение.

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

  1. Наведите курсор мыши на разделительную линию и зажмите левую кнопку мыши.
  2. Потяните разделительную линию на нужную позицию, не отпуская левой кнопки мыши.
  3. Отпустите левую кнопку мыши, чтобы зафиксировать разделительную линию на новой позиции.
Горизонтальное и вертикальное разделение
  1. Нажмите на ячейку, которая находится непосредственно под строками и сразу справа от столбцов, в месте где вы хотите разделить таблицу.
  2. Откройте меню Окно и выберите пункт Разбить. Между строками и столбцами появятся две перпендикулярные жирные линии, указывающие на место создания разделения.
Отмена разделения экрана

Чтобы убрать разделение таблицы, выполните одно из следующих действий:

  • Дважды нажмите левой кнопкой мыши на каждой линии разделения.
  • Нажмите и перетащите линии разделения к верхнему и правому краю таблицы.
  • В меню Окно снимите флажок с пункта Разбить.

Ввод данных

Большинство данных вводятся в таблицу Calc с помощью клавиатуры.

Числа

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

Отрицательные числа

Чтобы ввести отрицательное число, либо введите символ знака минуса (-) перед числом, либо заключите число в скобки (), например, (1234). Результат для обоих способов записи будет одинаков, например, -1234.

Ведущие нули

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

  1. Если ячейка выбрана, нажмите правой кнопкой мыши на ней, выберите пункт Формат ячеек из контекстного меню или выберите пункт меню , или воспользуйтесь сочетанием клавиш Ctrl+1 (цифра), чтобы открыть диалог Формат ячеек (рисунок Диалог Формат ячейки – вкладка Числа).
  2. Откройте вкладку Числа и выберите формат Числовой в списке Категория.
  3. Ниже, в разделе введите минимальное число символов в числе. Например, для четырёх символов введите 4. Теперь любое число длиной менее четырех символов будет иметь добавочные нули в начале, например, число 12 станет 0012.
  4. Нажмите кнопку OK. Введенное число сохраняет свой числовой формат и любая формула, используемая в электронной таблице, будет относиться к значению из такой ячейки, как к числу, и выполнять все возможные для числа действия.

Диалог Формат ячейки – вкладка Числа

Если число введено с ведущими нулями, например 01481, то по умолчанию Calc автоматически отбрасывает ведущий 0. Чтобы сохранить ведущие нули в числе:

  1. Введите апостроф (') перед числом, например '01481.
  2. Переместите выделение на другую ячейку. Апостроф автоматически удаляется, ведущие нули сохраняются, а число преобразуется в текст, выровненный по левому краю.

Примечание

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

Число, как текст

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

  1. Если ячейка выбрана, нажмите правой кнопкой мыши на неё, выберите Формат ячеек из контекстного меню или выберите пункт меню , или воспользуйтесь сочетанием клавиш Ctrl+1, чтобы открыть диалог Формат ячеек (рисунок Диалог Формат ячейки – вкладка Числа).
  2. Откройте вкладку Числа и выберите формат Текст в списке Категория.
  3. Нажмите кнопку OK и число преобразуется в текст, и, по умолчанию, будет выровнено по левому краю.

Примечание

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

Текст

Нажмите левой кнопкой мыши на ячейке и введите текст. По умолчанию текст выравнивается по левому краю ячейки.

Дата и время

Выделите ячейку и введите дату или время.

Вы можете разделить элементы даты косой чертой / (слэшем) или дефисом (), или использовать текст, например 10 Октября 2012. Формат даты автоматически переключится на нужный формат, используемый Calc.

Примечание

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

При вводе времени отдельные элементы времени разделяют двоеточиями, например 10:43:45. Формат времени автоматически переключится на нужный формат, используемый Calc.

Чтобы изменить формат времени или даты, используемый Calc:

  1. Если ячейка выбрана, нажмите правой кнопкой мыши на неё, выберите Формат ячеек из контекстного меню или выберите пункт меню , или воспользуйтесь сочетанием клавиш Ctrl+1, чтобы открыть диалог Формат ячеек (рисунок Диалог Формат ячейки – вкладка Числа).
  2. Откройте вкладку Числа и выберите формат Дата или Время в списке Категория.
  3. Выделите формат даты или времени, который вы хотите использовать, в списке Формат.
  4. Нажмите кнопку OK.

Параметры автозамены

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

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

Диалог Автозамена

Замена

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

Исключения

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

Параметры

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

Национальные параметры

Укажите параметры автозамены кавычек и параметры, которые являются специфическими для языка текста.

Восстановить

Сброс измененных значений к значениям LibreOffice по умолчанию.

Отключение автоматических изменений

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


Ускорение ввода данных

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

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

Использование инструмента Заполнить

Инструмент Calc Заполнить используется для дублирования существующего контента или создания серии данных в диапазоне ячеек в электронной таблице:

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

Использование инструмента Заполнить

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

  1. Выделите ячейку, содержащую данные для копирования, или начальную ячейку для рядов.
  2. Поместите курсор на маленький квадрат в правом нижнем углу выделенной ячейки. Курсор изменит форму на крестик.
  3. Нажмите и перетащите его в нужном вам направлении заполнения. Если исходная ячейка содержит текст, то текст будет автоматически скопирован. Если исходная ячейка содержала ряд, то будет создан ряд.
Использование заполнения рядов

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

Диалог Заполнить ряды

  • Направление – определяет направление создания рядов.

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

    • Линейный – создает линейный числовой ряд с использованием определенного приращения и конечного значения и указания единиц измерения.
    • Геометрический – создает геометрический ряд с использованием определенного приращения (множителя) и конечного значения.
    • Дата – создает ряд дат с использованием определенного приращения, даты окончания и указания единиц измерения (день, месяц, год) .
    • Автозаполнение – образует ряд непосредственно на листе. Функция автозаполнения использует настроенные списки. Например, при вводе в первой ячейке слова Январь, серия завершится, используя список, определенный в настройках LibreOffice в меню . Автозаполнение пытается завершить ряд значениями с помощью заданного шаблона. Например, числовой ряд 1,3,5 автоматически будет продолжен числами 7,9,11,13.
  • Единица времени – в этой области можно указать нужную единицу измерения времени. Эта область активна только при выборе типа ряда Дата.

    • День – используйте тип рядов Дата и этот параметр для создания ряда с приращением по дням (без ограничений).
    • День недели – используйте тип рядов Дата и этот параметр для создания ряда из с приращением по дням (по пятидневкам).
    • Месяц – используйте тип рядов Дата и этот параметр, чтобы сформировать ряды с приращением по месяцам.
    • Год – используйте тип рядов Дата и этот параметр, чтобы создать ряды с приращением по годам.
  • Начальное значение – определяет начальное значение ряда. Используется число, дата или время.

  • Конечно значение – определяет конечное значение ряда. Используется число, дата или время.

  • Приращение – определяет значение, на которое ряд выбранного типа увеличивается с каждым шагом (для типа Геометрический — это значение будет множителем). Записи могут быть сделаны только тогда, когда выбран тип ряда линейный, геометрический или дата.

Определение заполнения ряда

Чтобы определить свой собственный ряд заполнения:

  1. Выберите пункт меню , чтобы открыть одноименный диалог. Этот диалог показывает ранее определенные ряды в поле Списки и содержание выделенного списка в поле Элементы.
  2. Нажмите кнопку Создать. Поле Элементы очистится.
  3. Введите ряд для нового списка в поле Элементы (одно значение на строку).
  4. Нажмите кнопку Добавить и новый список появится в поле Списки.
  5. Нажмите OK, чтобы сохранить новый список.

Диалог Списки сортировки

Использование списков выбора

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

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

Обмен содержимым между листами

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

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

Диалог Выбрать листы

Предупреждение

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

Проверка содержимого ячеек

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

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

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

Редактирование данных

Удаление данных

Удаление только данных

Данные могут быть удалены из ячеек без удаления форматирования ячеек. Выделите любым методом нужные ячейки и нажмите клавишу Delete на клавиатуре.

Удаление данных и форматирования

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

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

Диалог Удалить содержимое

Замена данных

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

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

Редактирование данных

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

Используя клавиатуру
  1. Нажмите на ячейку, чтобы выбрать её.
  2. Нажмите клавишу F2 и в конце введенного текста в ячейке появится курсор.
  3. Используя клавиши курсора на клавиатуре подведите курсор к тому месту, где необходимо ввести новые данные.
  4. Когда закончите ввод новых данных, нажмите клавишу Enter и изменения будут сохранены.
Используя мышь
  1. Дважды нажмите на ячейку, чтобы выбрать её и поместить курсор в ячейку для редактирования.
  2. Переместите курсор в место начала ввода новых данных в ячейку.

Другой вариант:

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

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


Форматирование данных

Примечание

Все обсуждаемые в этом разделе настройки могут также быть установлены, как часть стиля ячейки. Смотрите Руководство по Calc, Глава 4, Использование Стилей и шаблонов в Calc, для получения дополнительных сведений.

Несколько строк текста

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

Автоматическое размещение

Чтобы автоматически разместить несколько строк текста в ячейке:

  1. Нажмите правой кнопкой мыши на ячейку и выберите пункт Формат ячейки из контекстного меню или выберите пункт меню или нажмите сочетание клавиш Ctrl+1, чтобы открыть диалог Формат ячейки.
  2. Нажмите на вкладку Выравнивание.
  3. В разделе Свойства установите флажок у пункта Переносить по словам и нажмите кнопку OK.

Диалог Формат ячейки — вкладка Выравнивание

Разрывы строк вручную

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

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

Уменьшение текста для полного размещения его в ячейке

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

Форматирование чисел

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

Панель форматирования. Значки форматирования чисел

Для большего контроля или для выбора других числовых форматов, используйте вкладку Числа диалога Формат ячеек (рисунок Использование инструмента Заполнить):

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

Форматирование шрифта

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

  1. Выделите ячейку.
  2. Нажмите на треугольник справа от поля Имя шрифта на панели Форматирование (выделено на рисунке ниже) и выберите шрифт из выпадающего списка.

Имя шрифта и размер шрифта на панели форматирования

  1. Нажмите на треугольник справа от поля Размер шрифта на панели Форматирование и выберите размер шрифта из выпадающего списка.
  2. Чтобы изменить формат символов, нажмите на значки Жирный, Курсив или Подчёркивание .
  1. Чтобы изменить выравнивание шрифта по ширине, нажмите на один из четырёх значков выравнивания (По левой стороне, По центру, По правой стороне, По ширине )
  1. Для изменения цвета шрифта, нажмите на треугольник справа от значка Цвет шрифта, чтобы отобразить цветовую палитру, где можно выбрать желаемый цвет.

Чтобы указать язык, используемый в ячейке, откройте вкладку Шрифт в диалоговом окне Формат ячеек. Изменение языка в ячейке позволяет получить разные языки в одном документе. Используйте вкладку Эффекты шрифта в диалоге Формат ячеек для установки других характеристик шрифта. Смотрите Руководство по Calc, Глава 4, Использование Стилей и шаблонов в Calc, для получения дополнительных сведений.

Форматирование границ ячеек

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

Выбор границ ячеек

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

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

Обрамление ячеек

Примечание

Свойства границ ячеек применяются только к выбранным ячейкам и могут быть изменены только при редактировании этих ячеек. Например, если ячейка С3 имеет верхнюю границу, то она может быть удалена только при выборе ячейки C3. Такая граница не может быть удалена из ячейки С2, хотя она и кажется нижней границей для ячейки C2.

Форматирование фона ячейки

Для форматирования цвета фона ячейки или группы выделенных ячеек нажмите на значок Цвет фона на панели форматирования. Будет показана цветовая палитра. Также можно использовать вкладку Фон в диалоге Формат ячейки. Смотрите Руководство по Calc, Глава 4, Использование Стилей и шаблонов в Calc, для получения дополнительных сведений.


Автоформат ячейки

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

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

  1. Выделите ячейки как минимум в трёх столбцах и трёх строках, включая заголовки столбцов и строк, которые хотите отформатировать.
  2. Откройте диалог Автоформат из меню .
  3. Выберите тип формата из списка.
  4. При необходимости, нажмите кнопку Ещё, чтобы открыть раздел Форматирование, если его не видно.
  5. Выберите настройки форматирования, которые включены в функцию Автоформат.
  6. Нажмите кнопку OK

Диалог Аавтоформат

Определение нового Автоформата

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

  1. Отформатируйте тип данных, шрифт, размер шрифта, границы ячеек, фон ячейки и так далее для группы ячеек.
  2. Выберите всю таблицу в меню Правка > Выделить всё.
  3. Откройте диалог Автоформат из меню . Здесь стала активна кнопка Добавить.
  4. Нажмите кнопку Добавить.
  5. В поле Имя диалога Добавить автоформат введите имя нового формата.
  6. Нажмите OK для сохранения. Новый автоформат теперь доступен в списке Формат диалога Автоформат.

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

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

Чтобы применить тему к таблице:

  1. Нажмите на значок Выбор тем на панели инструментов Сервис (если эта панель не видна, то выберите в меню и выберите Сервис). Откроется диалог Выбор тем (рисунок Диалог Выбор тем). Этот диалог содержит список всех доступных тем для таблиц.
  2. Выберите тему, которую хотите применить. При выборе темы тематические стили сразу применяются к таблице и становятся видны.
  3. Нажмите OK.
  4. Если хотите, то можете открыть диалог Стили и форматирование для изменения конкретных стилей. Эти изменения не поменяют тему, они изменят только внешний вид стиля в той электронной таблице, которая была создана.

Диалог Выбор тем

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

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

Условное форматирование зависит от использования стилей, также должна быть включена функция Вычислять автоматически. Проверьте это в меню . Смотрите Руководство по Calc, Глава 2, Ввод, редактирование и форматирование данных для получения дополнительной информации.

Скрытие и отображение данных

В Calc можно скрывать элементы так, чтобы они не были видны на экране, и не печатались на принтере при отправке таблицы на печать. Однако скрытые элементы все еще могут быть выбраны для копирования, если будут выбраны элементы рядом с ними. Например, если столбец B скрыт, то он будет копироваться при выборе столбцов А и С.

Для получения дополнительной информации о том, как скрыть и показать данные, в том числе, как использовать фильтрацию, смотрите Руководство по Calc, Глава 2, Ввод, редактирование и форматирование данных.

Скрытие данных

Чтобы скрыть листы, строки и столбцы:

  1. Выделите нужные листы, строки или столбцы.
  2. В меню Формат выберите Лист, Строка или Столбец.
  3. Выберите пункт Скрыть и лист, строка или столбец в дальнейшем будут не видны и не будут напечатаны.
  4. Также можно нажать правой кнопкой мыши на ярлыке листа, заголовке строки или столбца и выбрать пункт Скрыть из контекстного меню.

Чтобы скрыть и защитить данные в выделенных ячейках:

  1. В меню выберите пункт Лист. Откроется диалог Защитить лист.

Диалог Защитить лист

  1. Поставьте флажок у пункта Защитить лист и содержимое защищённых ячеек.
  2. Создайте и подтвердите пароль.
  3. Установите или снимите флажок параметров выбора пользователя для ячеек.
  4. Нажмите OK.
  5. Выделите ячейки, которые вы хотите скрыть.
  6. Выберите пункт меню или нажмите правой кнопкой мыши и выберите пункт Формат ячеек из контекстного меню, или нажмите Ctrl+1, чтобы открыть диалог Формат ячеек.
  7. Щелкните на вкладке Защита ячейки и выберите параметры скрытия ячейки.
  8. Нажмите OK.

Вкладка Защита ячейки в диалоге Формат Ячеек

Примечание

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

Отображение данных

Чтобы отобразить скрытые листы, строки или столбцы:

  1. Выделите листы, строки или столбцы, расположенные с обеих сторон от скрытых листов, строк или столбцов.
  2. В меню Формат выберите пункт Лист, Строка или Столбец.
  3. Выберите пункт Показать и лист, строка или столбец будут отображаться и печататься.
  4. Также можно щелкнуть правой кнопкой мыши на ярлыках листов, заголовке строк или столбцов и выбрать из контекстного меню пункт Показать.

Чтобы показать скрытые данные в ячейках:

  1. В меню выберите пункт Лист.
  2. Введите пароль, чтобы снять защиту с листа и нажмите OK.
  3. Выберите пункт меню или нажмите правой кнопкой мыши и выберите пункт Формат ячеек из контекстного меню, или нажмите Ctrl+1, чтобы открыть диалог Формат ячеек
  4. нажмите вкладку Защита ячейки (рисунок Вкладка Защита ячейки в диалоге Формат Ячеек) и снимите флажки с настроек скрытия ячеек.
  5. Нажмите OK.

Сортировка записей

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

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

Для получения более подробной информации о том, как сортировать записи и доступных настройках сортировки, смотрите Руководство по Calc, Главу 2, Ввод, редактирование и форматирование данных.

Вкладка Защита ячейки в диалоге Формат Ячеек

Для сортировки ячеек в таблице:

  1. Выделите ячейки для сортировки.
  2. Выберите меню , чтобы открыть одноимённый диалог.
  3. Выберите критерии сортировки из выпадающего списка.
  4. Выберите порядок сортировки: нисходящий (А-Я, 1-9) или восходящий (Я-А, 9-1).
  5. Нажмите OK и данные будут отсортированы.

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

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

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

Смотрите Руководство Calc, Глава 7, Использование формул и функций, для получения более подробной информации.

Анализ данных

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

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

Смотрите Руководство Calc, Главу 8, Использование сводных таблиц, и Главу 9, Анализ данных для получения подробной информации по сводным таблицам и другим инструментам доступным для анализа данных.

Печать

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

Диапазоны печати

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

Определение диапазонов печати

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

  1. Выберите диапазон ячеек, которые будут включены в диапазон печати.
  2. Выберите пункт меню . На экране будут показаны линии разрыва страниц.
  3. Чтобы проверить диапазон печати, перейдите в меню или нажмите на значок предварительного просмотра страницы. LibreOffice отобразит ячейки в диапазоне печати.
Добавление ячеек к диапазону печати

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

  1. После определения диапазона печати выберите дополнительный диапазон ячеек для добавления к нему.
  2. Выберите пункт меню , чтобы добавить дополнительные ячейки к диапазону печати. Линии разрыва страницы перестанут отображаться на экране.
  3. Для проверки диапазонов печати откройте пункт меню или нажмите на значок предварительного просмотра страницы. LibreOffice будет отображать диапазоны печати как отдельные страницы.

Примечание

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

Удаление диапазона печати

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

Редактирование диапазона печати

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

Параметры печати

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

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

3. Нажмите OK.
Для получения более подробной информации о настройках печати, смотрите Руководство по Calc, Глава 6, Печать, экспорт и рассылка электронной почтой.

Повторение печати строк и столбцов

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

  1. Выберите пункт меню , чтобы открыть диалог Изменение диапазонов печати.
  2. Введите идентификаторы строк в поле ввода, чтобы повторить их. Например, чтобы повторить строки 1 и 2, введите $1:$2. Это автоматически изменит значение поля Повторять строки с «- нет -» на «- определенный пользователем -».
  3. Введите идентификаторы столбцов в поле ввода, чтобы повторить их. Например, чтобы повторить столбец А, введите $А. Это автоматически изменит значение поля Повторять столбцы с «- нет -» на «- определенный пользователем -».
  4. Нажмите кнопку OK.

Диалог Изменение диапазона печати

Для получения более подробной информации об изменении диапазона печати, смотрите Руководство по Calc, Глава 6, Печать, экспорт и рассылка электронной почтой.

Разрывы страниц

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

Для получения более подробной информации об разрывах страниц, смотрите Руководство по Calc, Глава 6, Печать, экспорт и рассылка электронной почтой.

Вставка разрыва

Чтобы вставить разрыв страницы:

  1. Перейдите к ячейке, где начнётся разрыв страницы.
  2. Выберите пункт меню .
  3. Выберите Разрыв по строке, чтобы создать разрыв страницы выше выделенной ячейки.
  4. Выберите Разрыв по столбцу, чтобы создать разрыв страницы слева от выделенной ячейки.
Удаление разрыва страницы

Чтобы удалить разрыв страницы:

  1. Перейдите к ячейке, которая находится рядом с разрывом который вы хотите удалить.
  2. Выберите пункт меню .
  3. Выберите Разрыв по строке или Разрыв по столбцу и разрыв будет удалён.

Примечание

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

Верхние и нижние колонтитулы

Верхние и нижние колонтитулы – предопределенные части текста, которые печатаются в верхней или нижней части каждой страницы, при печати электронной таблицы. Для получения дополнительной информации о настройке и определении верхних и нижних колонтитулов, смотрите Руководство по Calc, Глава 6, Печать, экспорт и рассылка электронной почтой.

Верхние и нижние колонтитулы также относятся к стилю страницы. Можно определить более одного стиля страницы для электронной таблицы и назначать различные стили страниц для разных листов в электронной таблице. Для получения дополнительной информации о стилях страниц смотрите Руководство по Calc, Глава 4, Использование Стилей и шаблонов.

Вкладка Верхний колонтитул

Установка верхних и нижних колонтитулов

Чтобы установить верхние или нижние колонтитулы:

  1. Выделите лист, для которого необходимо установить верхние или нижние колонтитулы.
  2. Выберите пункт меню , чтобы открыть диалог Стиль страницы.
  3. В диалоге Стиль страницы выберите вкладку Верхний колонтитул или Нижний колонтитул.
  4. Установите флажок для пункта Вкл.верхний колонтитул или Вкл.нижний колонтитул.
  5. Установите флажок для пункта Одинаковое содержимое слева/справа если вы хотите, чтобы одинаковые колонтитулы появились на всех печатных страницах.
  6. Установите параметры полей, интервалы и высоту для верхнего или нижнего колонтитула. Также можно выбрать опцию Автоподбор высоты для автоматического регулирования высоты верхнего или нижнего колонтитула.
  7. Чтобы изменить внешний вид верхнего или нижнего колонтитула, нажмите кнопку Ещё, чтобы открыть диалог границ и фона.
  8. Чтобы установить содержимое, например, номер страницы, дату и так далее, которое появляется в верхнем или нижнем колонтитуле, нажмите на кнопку Правка, чтобы открыть диалог стиль.

Как вычислить несколько ячеек в Excel | Малый бизнес

К.А. Фрэнсис Обновлено 27 августа 2018 г.

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

Разбивка формул Excel

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

Ссылки на ячейки — это ячейки, содержащие значения, которые используются для выполнения функции. Пример A2, D5, F8 и т. Д.

Арифметический оператор — это оператор, используемый для вычисления функции. Символы плюса (+), минуса (-), умножения (*) и деления (/) являются арифметическими операторами.

Константа — значение, к которому применяется арифметический оператор. Если вы рассчитываете заработную плату брутто, почасовая ставка является постоянной.

Добавление значений в Excel

Одним из наиболее распространенных способов использования Excel является вычисление значений. Например, если вы отслеживаете инвентарь своих канцелярских принадлежностей, вы складываете общую сумму каждого предмета, складывая ячейки вместе. Если Computer Paper занимает ячейки C4, C5 и C6, а общая ячейка — C10, вы можете сложить значения в этих ячейках. Используйте формулу «= +» в ячейке C10. Формула в ячейке C10 будет выглядеть следующим образом:

В качестве альтернативы, поскольку ячейки являются последовательными, вы также можете использовать функцию СУММ для суммирования нескольких столбцов в Excel на основе критериев.В этом случае вы должны поместить курсор в ячейку C10, а затем нажать клавишу SUM на панели инструментов Excel на вкладке формулы. Затем вы выделяете и перетаскиваете клавиши с C4 по C6 и нажимаете Enter. Формула в C10 выглядит так:

Расчет несмежных значений в Excel

Даже если у вас есть значения, которых нет в соседней ячейке, вы все равно можете их вычислить. Во многих случаях в разных частях листа будут значения, которые необходимо вычислить вместе, чтобы получить желаемые результаты.Например, если вам нужно использовать общее количество часов, проработанных сотрудником, и умножить его на почасовую заработную плату, чтобы определить валовую заработную плату, это можно сделать, даже если значения не совпадают. Итак, если значение отработанного времени находится в D10, а почасовая ставка находится в B2, и вы хотите рассчитать валовую заработную плату в F7, формула, которую вы должны написать в F7, будет = D10 * B2.

Примеры других вычисляемых значений

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

Средние

— Если вы хотите узнать среднее значение списка чисел, формула будет = СРЕДНЕЕ (C4: C6) (с использованием ячеек из предыдущего примера. Эта формула складывает значения в C4, C5 и C6 и делит эта сумма умножается на 3. Если бы в выбранной области было 15 чисел (скажем, от C1 до C15), формула изменилась бы на СРЕДНЕЕ (C1: C15), а делитель был бы равен 15.

Способы добавления значений в электронную таблицу

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

Автосумма создает формулу за вас, так что вам не нужно вводить текст.Однако, если вы предпочитаете вводить формулу самостоятельно, см. Функцию СУММ.

Добавить на основе условий

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

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

Сложить или вычесть даты

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

Сложить или вычесть время

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

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в сообществе специалистов по Excel или получить поддержку в сообществе ответов.

Как применить одну и ту же формулу к нескольким ячейкам в Excel (8 простых способов)

В этом уроке я покажу вам, как применить одну и ту же формулу к нескольким ячейкам в Excel.

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

Давайте изучим методы.

Загрузить рабочий файл

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

Применение одной и той же формулы Excel к нескольким ячейкам (ссылки на ячейки изменятся)

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

1) Использование сочетания клавиш CTRL + Enter

На рисунке ниже вы видите образец данных. Цены на некоторые товары (в евро) указаны в столбце Цена (евро). Кроме того, обменные курсы отображаются в диапазоне ячеек C2: E2.Я хочу показать цены на продукты в других валютах, таких как доллары США, фунты стерлингов и японские йены.

Я напишу только одну формулу в ячейку C6 и буду использовать ее в диапазоне ячеек C6: E10.

Позвольте мне показать вам, как это сделать.

Шаг 1

Выберите диапазон ячеек C6: E10.

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

Шаг 2

Теперь введите знак равенства и напишите следующую формулу: = $ B6 * C $ 2. Эта формула будет автоматически введена в ячейку C6, поскольку C6 является активной ячейкой.

Шаг 3

Нажмите одновременно CTRL + Enter на клавиатуре. Это то, что вы получите.

Как работает эта формула (= $ B6 * C $ 2)?

  • Обратите внимание, что в формуле есть смешанные ссылки на ячейки. Ссылка на $ B6 означает, что при копировании формулы вправо столбец B не изменится.Итак, это будет похоже на B6, B6 и B6. Все — B6, так как при копировании формулы вправо строка не меняется. Когда формула будет скопирована, ссылка на ячейку будет B6, B7, B8, B9, B10. Это связано с тем, что строки меняются при копировании формулы.
  • Ссылка C $ 2 означает, что при копировании формулы вправо ссылками будут C2, D2 и E2. Поскольку ссылка на столбец C является относительной. И когда мы скопируем эту формулу, ссылки будут C2, C2, C2, C2 и C2.Это потому, что строка 2 является абсолютной.
  • Давайте проверим ячейку в диапазоне. Я выбираю ссылку на ячейку D8 и нажимаю клавишу F2 (это переводит ячейку в режим редактирования) на клавиатуре. Формула работает отлично.

2) Использование инструмента Excel Fill Handle Tool (перетаскивание)

Я буду использовать тот же пример, чтобы объяснить этот метод. На этот раз я выберу только ячейку C6 и введу эту формулу: = $ B6 * C $ 2. Затем нажмите клавишу Enter. В ячейке C6 получим значение 7.10. Снова выберите ячейку C6.

Обратите внимание на изображение выше. Вы увидите сплошной квадрат зеленого цвета в правом нижнем углу ячейки. Это инструмент Fill Handle в Excel.

  • Наведите указатель мыши на твердое тело «Ручка заливки». Большой белый знак «плюс» превратится в маленький черный знак.
  • Щелкните и удерживайте указатель мыши и потяните вниз. Затем, используя тот же метод, перетащите вправо.
  • Это полный метод, показанный на изображении в виде гифки.

Я не буду объяснять, как работает формула, потому что это уже сделано в вышеупомянутом методе.

3) Использование инструмента Excel Fill Handle (Двойной щелчок)

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

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

Что делать, если инструмент Fill Handle не отображается?

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

  • вкладка Файл> Параметры
  • Появится диалоговое окно Параметры Excel> щелкните Расширенный
  • В разделе Параметры редактирования> установите флажок Включить маркер заполнения и поле перетаскивания ячейки
  • Наконец, нажмите на ОК Все готово.

4) Использование команды копирования и вставки (CTRL + C)

  • Выберите ячейку C6 (= $ B6 * C $ 2) и скопируйте ее с помощью сочетания клавиш CTRL + C или с помощью командной кнопки на главной странице> Группа команд в буфере обмена.
  • Теперь выберите диапазон ячеек C6: E10
  • Затем используйте команду Главная> Буфер обмена> Вставить или нажмите сочетание клавиш CTRL + V.

5) Использование сочетания клавиш CTRL + D

  • Поместите эту формулу Excel в ячейку C6: = $ B6 * C $ 2 и нажмите Enter
  • Теперь выберите ячейку C6 и другие ячейки в столбце, к которому вы хотите применить формулу.
  • Теперь нажмите CTRL + D одновременно на клавиатуре. Весь столбец заполняется формулой в ячейке C6

Примечание. Используя сочетание клавиш CTRL + D, вы не можете применить формулу к диапазону. Работает только для колонки.

6) Сочетание клавиш CTRL + R для применения формулы к столбцу справа

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

Теперь вы хотите применить этот набор формул к правому столбцу.Для этого вы можете использовать сочетание клавиш CTRL + R.

  • Теперь нажмите клавиши CTRL + R на клавиатуре. Вот результат.

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

7) Копирование формулы в несколько ячеек, но ячейки не являются смежными

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

  • С помощью сочетания клавиш CTRL + C
  • Или с помощью Сочетание клавиш CTRL + D

# Использование сочетания клавиш CTRL + C

  • Скопируйте формулу в ячейку C6
  • Нажмите и удерживайте клавишу CTRL на клавиатуре, а затем выберите все ячейки, в которых вы хотите применить формулу
  • Теперь нажмите CTRL + V на клавиатуре.Готово.

# Использование сочетания клавиш CTRL + D

  • Выберите ячейку (C6), в которой находится формула, и все другие ячейки, к которым вы хотите применить формулу
  • Теперь нажмите CTRL + D на вашем клавиатура. Готово.

8) Использование таблицы Excel

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

Диапазон легко преобразовать в таблицу. Просто выберите ячейку в диапазоне и нажмите CTRL + T или используйте команду «Вставить»> «Таблицы»> «Таблица»

. Давайте рассчитаем НДС для товаров для этих продуктов.

  • Выберите ссылку на ячейку F3> введите знак равенства> выберите первую ячейку столбца цены за единицу> символ умножения (*)> выберите первую ячейку столбца количества> символ умножения (*)> наконец выберите первую ячейку столбец% НДС
  • Нажмите клавишу Enter> НДС будет рассчитан> появится раскрывающийся список> Щелкните раскрывающийся список и выберите вариант Заменить все ячейки в этом столбце с помощью этой формулы

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

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

Изучите некоторые важные сочетания клавиш

1) CTRL + SHIFT + END

Чтобы выбрать весь диапазон после вставки формулы в ячейку C6, нажмите клавиши CTRL + SHIFT + END на клавиатуре.

2) CTRL + SHIFT + СТРЕЛКА ВНИЗ для выбора данных столбца из ячейки, в которой вы находитесь.

Это сочетание клавиш CTRL + SHIFT + СТРЕЛКА ВНИЗ выберет столбец до ячейки, в которой доступны данные.Дальнейшее нажатие на этот ярлык выберет все из вашего местоположения.

3) CTRL + SPACE выберет весь столбец

Связанные материалы

Заключение

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

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

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

Как суммировать в Excel Примеры и видео

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

Обзор

: 7 способов подвести итоги
в Excel

Чтобы увидеть краткий обзор 7 способов суммирования в Excel, вы можете посмотреть это
короткое слайд-шоу или посмотрите пошаговые инструкции в видео.

Загрузите 7 способов
Обобщите образец книги, чтобы вы могли следить за видео. На молнии
Файл имеет формат xlsx и не содержит макросов.

Суммирование диапазона ячеек — функция СУММ

Самый быстрый способ суммировать диапазон ячеек — использовать кнопку Автосумма.Он автоматически вводит функцию СУММ в выбранную ячейку.

Функция СУММ суммирует одно или несколько чисел в диапазоне ячеек.

  1. Выберите пустую ячейку в строке под ячейками, которые вы хотите
    сумма, ячейка A5 в этом примере.

  2. Щелкните команду AutoSum на вкладке «Главная» ленты,
    или используйте сочетание клавиш: Alt + =

  3. В активной ячейке появится формула СУММ со ссылкой на
    ячейки выше.На снимке экрана ниже представлена ​​формула СУММ в
    ячейка A5: = СУММ (A1: A4)
    ПРИМЕЧАНИЕ. Если все ячейки не включены автоматически, вы можете расширить
    рамка, чтобы выбрать их.

  4. Нажмите клавишу Enter, чтобы завершить ввод.

Настройка функции СУММ

Вместо использования команды Автосумма для вставки функции СУММ вы можете
введите функцию вручную.

Настройка функции СУММ (синтаксис): СУММ (число1 , [число2] ,…).

  • Обязательный аргумент: число1
  • У него также есть необязательные аргументы (заключены в квадрат
    скобки): [число2], ..

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

В приведенном выше примере ( = СУММ (A1: A4) ) есть один аргумент —
ссылка на ячейки A1: A4.

Исправьте числа, которые не суммируются

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

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

Всего диапазон ячеек

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

Краткая общая сумма для диапазона ячеек
  1. Выберите диапазон ячеек и пустую строку под диапазоном, и
    пустые ячейки в столбце справа (ячейки A1: D5 в примере
    ниже)
  2. Нажмите кнопку «Автосумма» на вкладке «Главная» ленты. Формула СУММ будет
    автоматически вводиться для каждой суммы.

Итого

Чтобы увидеть промежуточную сумму в каждой строке списка Excel, вы можете использовать
СУММ, при этом начальная строка заблокирована как абсолютная ссылка.Ниже приведены несколько другие шаги,

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

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

Промежуточный итог — Рабочий лист

Для списка рабочего листа (не именованной таблицы Excel) выполните следующие действия, чтобы создать промежуточную сумму.На приведенном ниже снимке экрана суммы вводятся в столбце C, а текущая
Итого рассчитывается в столбце D.

  • Введите эту формулу в ячейку D2.
  • Скопируйте формулу в ячейку D6
Как работает формула

В формуле в качестве отправной точки используется абсолютная ссылка на строку 2.
— 2 C $ — и относительная ссылка на конечную точку — C2

Это гарантирует, что начальная точка не изменится при копировании
формула до строк ниже.Вот формула в ячейке D6 —
начальная точка осталась прежней, а конечная точка находится в текущем
ряд — C6

Промежуточная сумма в таблице Excel

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

Проблема с промежуточным итогом

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

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

Перед добавлением строки в ячейке D6 была следующая формула:

Как только в строке 7 была начата следующая запись, формула в D6 автоматически изменилась. Теперь у него неправильная конечная ссылка на C7 вместо C6:

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

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

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

На приведенном ниже снимке экрана суммы вводятся в столбце C, а текущая
Итого рассчитывается в столбце D.

  • Введите эту формулу в ячейку D2.
  • Формула автоматически заполняется до ячейки D6
  • Во всех ячейках столбца D отображается одна и та же формула.
Как работает формула

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

канадских долларов

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

Для конечной точки есть ссылка на структурированную таблицу — [@Amt]

  • Это ссылка на ячейку Amt в текущей строке
  • С этой ссылкой на ячейку таблицы нет проблем при добавлении новых строк в таблицу Excel

На этом снимке экрана показано, что при запуске новой строки в ячейке C6 по-прежнему отображается исходная формула, а промежуточные итоговые суммы верны в каждой строке

Сумма диапазона ячеек — СМЕЩЕНИЕ

Если вы вставите строку непосредственно над функцией СУММ в предыдущем примере,
новую строку нельзя включать в СУММ.Он может продолжать суммировать ячейки
A1: A4 и игнорируйте A5. Чтобы новые строки были включены в итоговую сумму,
вы можете использовать функцию СМЕЩЕНИЕ с функцией СУММ.

  1. Выберите ячейку A5.
  2. Введите следующую формулу:
    = СУММ (A1: СМЕЩЕНИЕ (A5, -1,0))
  3. Нажмите клавишу Enter, чтобы завершить ввод.
  4. Вставить строку над строкой 5
  5. Введите число в ячейку A5, и оно будет включено в итог в
    ячейка A6

Сумма ячеек, соответствующих критериям — СУММЕСЛИ

Вот 3 способа суммирования ячеек, соответствующих критерию:

— Точное соответствие критерию
— Критерий соответствия в
строка
— критерий соответствия с использованием
оператор

Точное соответствие критерию

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

  1. Выберите ячейку, в которой вы хотите увидеть итог
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СУММЕСЛИ (
  4. Выберите ячейки, содержащие значения для проверки критерия.
    В этом примере будут проверяться ячейки A2: A10
  5. Введите запятую для разделения аргументов
  6. Введите критерий.В этом примере вы проверяете текст, поэтому
    введите слово в двойных кавычках: «Pen»
    Примечание: прописные и строчные буквы обрабатываются одинаково
  7. Введите запятую для разделения аргументов
  8. Выберите ячейки, содержащие значения для суммирования. В этом примере
    ячейки B2: B10 будут суммированы
  9. Заполненная формула:
    = СУММЕСЛИ (A2: A10, «Pen», B2: B10)
  10. Нажмите клавишу Enter, чтобы завершить ввод

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете ссылаться на
в камеру.Например, формула на шаге 9 выше может быть изменена на:
= СУММЕСЛИ (A2: A10, B12, B2: B10)
, если в ячейке B12 содержится текст — pen .

Критерий совпадения в строке

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

  1. Выберите ячейку, в которой вы хотите увидеть итог (ячейка A12 в этом
    пример)
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СУММЕСЛИ (
  4. Выберите ячейки, содержащие значения для проверки критерия.В этом примере будут проверяться ячейки A2: A10
  5. Введите запятую для разделения аргументов
  6. Введите критерий. В этом примере вы проверяете текст, поэтому
    введите слово в двойные кавычки с одной или несколькими подстановочными знаками звездочки (*)
    символы: «* Pen *»
    Примечание: прописные и строчные буквы обрабатываются одинаково
  7. Введите запятую для разделения аргументов
  8. Выберите ячейки, содержащие значения для суммирования.В этом примере
    ячейки B2: B10 будут суммированы
  9. Введите закрывающую скобку. Заполненная формула: = СУММЕСЛИ (A2: A10, «* Pen *», B2: B10)
  10. Нажмите клавишу Enter, чтобы завершить ввод
  11. Результатом будет 53, всего строк, содержащих строку,
    «Ручка»

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете ссылаться на
в камеру.Например, формулу на шаге 9 выше можно изменить на:
= СУММЕСЛИ (A2: A10, «*» & B12 & «*», B2: B10)
, если в ячейке B12 содержится текст — pen .

Критерий и оператор

Вы можете использовать оператор с критерием. В приведенных ниже примерах см.
как объединить их в формуле СУММЕСЛИ.

Пример 1

Пример 2

Пример 1 — Сумма строк больше установленной суммы

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

  1. Выберите ячейку, в которой вы хотите увидеть итог
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СУММЕСЛИ (
  4. Выберите ячейки, содержащие значения для проверки критерия.
    В этом примере будут проверяться ячейки B2: B10
  5. Введите запятую для разделения аргументов
  6. Введите критерий. В этом примере вы проверяете строки, в которых
    количество посещений больше или равно 10.Оператор> =
    используется перед числом, а весь критерий заключен в двойные
    цитаты.
  7. Введите запятую для разделения аргументов
  8. Выберите ячейки, содержащие значения для суммирования
  9. Введите закрывающую скобку. Заполненная формула:
    = СУММЕСЛИ (B2: B10, «> = 10», C2: C10)
  10. Нажмите клавишу Enter, чтобы завершить ввод
  11. Результатом будет 183 строк с десятью или более торговыми представителями.

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете ссылаться на
в камеру. Например, формула на шаге 9 выше может быть изменена на:
= СУММЕСЛИ (B2: B10, «> =» & B12, C2: C10)
, если ячейка B12 содержит число — 10 .

Пример 2 — Прокатный итог за 12 месяцев

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

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

  1. Выберите первую ячейку, в которой вы хотите увидеть скользящую сумму —
    ячейка C2 в этом примере
  2. Введите следующую формулу и нажмите Enter:
    = СУММЕСЛИ (A $ 2: A2, «> =» & ДАТА (ГОД (A2), МЕСЯЦ (A2) -11, ДЕНЬ (A2)), B $ 2: B2).
  3. Скопируйте формулу до последней строки с данными.
  4. В каждой строке отображается скользящая сумма за последние 12 месяцев (если имеется)

Как это работает

Формула проверяет даты в столбце A, начиная со строки 2 (2 австралийских доллара), и
до текущей строки (A2)

= СУММЕСЛИ ( A $ 2: A2

Функция ДАТА вычисляет дату, которая на 11 месяцев раньше даты.
в текущей строке

ДАТА (ГОД (A2), МЕСЯЦ (A2) -11, ДЕНЬ (A2))

Оператор> = проверяет даты, которые больше или равны
та дата,

«> =» & ДАТА (ГОД (A2), МЕСЯЦ (A2) -11, ДЕНЬ (A2))

Для строк, соответствующих критерию, формула суммирует суммы в столбце
B, начиная со строки 2 (B $ 2) до текущей строки (B2)

B $ 2: B2

Суммарные ячейки, соответствующие нескольким критериям — СУММЕСЛИМН

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

Соответствие нескольким критериям

В этом примере
только строки со статусом «Отправлено» и количество
единицы больше или равны десяти, будут включены в общую сумму.

  1. Выберите ячейку, в которой вы хотите увидеть итог
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СУММЕСЛИМН (
  4. Выберите ячейки, содержащие значения для суммирования.В этом примере
    Ячейки D3: D10 будут суммированы
  5. Введите запятую, затем выберите ячейки, содержащие значения для проверки.
    по первому критерию. В этом примере будут проверяться ячейки B3: B10
  6. Введите запятую, а затем введите первый критерий «Отправлено».
  7. Введите запятую, затем выберите ячейки, содержащие значения для проверки.
    по второму критерию. В этом примере будут проверяться ячейки B2: B6
  8. Введите запятую, а затем введите второй критерий: «> =» & 10
  9. Отделка закрывающей скобкой 🙂
  10. Заполненная формула:
    = СУММЕСЛИМН (D3: D10, B3: B10, «Отправлено», C3: C10, «> =» & 10)
  11. Нажмите клавишу Enter, чтобы завершить ввод

Формулы со ссылками на таблицы

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

Проблемы со ссылками на таблицы

В этой сводке продаж в ячейке C5 есть формула СУММЕСЛИМН, которая показывает правильную сумму продаж слитков в Восточном регионе.

= СУММЕСЛИМН (Sales_Data [количество], Sales_Data [регион], $ B5, Sales_Data [категория], C $ 4)

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

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

= СУММЕСЛИМН (Sales_Data [TotalCost], Sales_Data [Category], $ B5, Sales_Data [Product], D $ 4)

  • Вместо количества формула суммирует столбец общих затрат
  • Вместо того, чтобы искать Восток в столбце «Регион», он ищет в категории
  • .

  • Вместо того, чтобы искать файлы cookie в столбце Категория, он ищет продукт

Ни один из этих критериев не найден, поэтому результат равен нулю.

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

Вместо этого используйте один из следующих методов:

Заполнение справа
  • Выберите ячейку с формулой и ячейки справа, куда вы хотите скопировать формулу
  • Нажмите Ctrl + R, чтобы заполнить формулу справа
Копирование и вставка
  • Выделите ячейку с формулой и нажмите Ctrl + C, чтобы скопировать ее
  • Выберите все ячейки, в которые вы хотите скопировать формулу
  • Нажмите Ctrl + V, чтобы вставить формулу

СУММЕСЛИМН с несколькими критериями И / ИЛИ

В приведенном ниже списке вы можете использовать формулу СУММЕСЛИМН для суммирования строк, в которых
город — Нью-Йорк, И категория — Бары.Формула в ячейке G9
будет:

= СУММЕСЛИМН (D4: D15, B4: B15, G4, C4: C15, G6)

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

  • город Нью-Йорк И категория Cookies
  • город Нью-Йорк И категория Бары

Две категории вводятся в ячейки G6: G7 на листе.

Формула с вводом массива

Чтобы вычислить сумму, мы заключим формулу СУММЕСЛИМН с функцией СУММ,
и введите формулу в виде массива.

  1. Выберите ячейку, в которой вы хотите увидеть итог — G9 в этом примере
  2. Чтобы запустить формулу, введите: = СУММ (СУММЕСЛИМН (
  3. Выберите ячейки, содержащие значения для суммирования. В этом примере
    Ячейки D4: D15 будут суммированы
  4. Введите запятую , затем выберите ячейки, содержащие значения
    проверить по первому критерию.В этом примере ячейки B4: B15
    будут проверены — они содержат названия городов
  5. Введите запятую , а затем щелкните ячейку с первым критерием
    G4
  6. Введите запятую , затем выберите ячейки, содержащие значения
    проверить по второму критерию. В этом примере ячейки C4: C15
    будут проверены — у них категория
  7. Введите запятую , а затем выберите ячейки со списком для
    второй критерий — G6: G7
  8. Отделка с 2 закрывающими скобами: ))
  9. Заполненная формула в ячейке G9:
    = СУММ (СУММЕСЛИМН (D4: D15, B4: B15, G4, C4: C15, G6: G7))
  10. Нажмите Ctrl + Shift + Enter для перехода к массиву — введите
    формула — результат будет неверным, если вы просто нажмете клавишу Enter
    ключ.
  11. Фигурные скобки будут автоматически добавлены в начало и конец
    формулы, чтобы показать, что она введена в массив. Не вводите эти скобки
    себя.

Сумма ячеек, соответствующих нескольким критериям —
СУММПРОИЗВ

Соответствие нескольким критериям

В Excel 2003 и более ранних версиях вы можете использовать функцию СУММПРОИЗВ для вычисления
итог для строк, соответствующих двум или более критериям.Если вы используете Excel
2007 или более поздней версии следует использовать функцию СУММЕСЛИМН, как описано в
предыдущий раздел.

В этом примере только строки со статусом «Активен»
и количество посещений больше или равно десяти будут включены
в общем.

  1. Выберите ячейку, в которой вы хотите увидеть итог
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: SUMPRODUCT (- (
  4. Выберите ячейки, содержащие значения для проверки первого критерия.В этом примере будут проверяться ячейки A2: A6
  5. Введите первый критерий: = «Активно»
  6. Тип), — (
  7. Выберите ячейки, содержащие значения для проверки второго критерия.
    В этом примере будут проверяться ячейки B2: B6
  8. Введите второй критерий:> = 10
  9. Тип), — (
  10. Выберите ячейки, содержащие значения для суммирования. В этом примере
    Ячейки C2: C6 будут суммированы
  11. Отделка с закрывающими скобками :))
  12. Заполненная формула:
    = СУММПРОИЗВ (- (A2: A6 = «Активный»), — (B2: B6> = 10), — (C2: C6))
  13. Нажмите клавишу Enter, чтобы завершить ввод

Суммируйте 5 первых чисел в списке

Используйте функции СУММ и НАИБОЛЬШИЙ вместе, чтобы сложить наибольший
числа в списке.

Версия 1 — Несколько главных номеров

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

= СУММ (НАИБОЛЬШИЙ (A1: A7, {1,2,3}))

Результат: 70 + 60 + 50 = 180

Примечание : Вторые 50 не включаются в результат, хотя
он разделен на 3-е место.

Версия 2 — Многие первые числа

Если нужно суммировать много старших чисел, вы можете включить функцию ДВССЫЛ.
в формуле с функцией СУММ.В функции ДВССЫЛ используйте строку
числа, которые представляют числа, которые вы хотите включить. В этом примере
строки 1:10 используются, поэтому первые 10 чисел в указанном диапазоне будут
суммировать.

  1. Введите формулу:
    = СУММ (НАИБОЛЬШИЙ (A1: A50, СТРОКА (КОСВЕННАЯ («1:10»))))
  2. Это формула массива, и ее нужно вводить в массив. Для этого удерживайте
    клавиши Ctrl и Shift и нажмите Enter

Версия 3 — переменные верхние числа

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

  1. В ячейке C1 введите количество верхних ячеек, например 10
  2. Введите формулу:
    = СУММ (НАИБОЛЬШИЙ (A1: A7; СТРОКА (КОСВЕННО («1:» & C1))))
  3. Это формула массива, и ее нужно вводить в массив. Для этого удерживайте
    клавиши Ctrl и Shift и нажмите Enter

Сумма сумм в диапазоне дат

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

Сумма сумм за диапазон дат

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

— Итого с СУММЕСЛИМН

— Итого с СУММЕСЛИ

В этом примере дата начала и дата окончания вводятся на листе.Даты указаны в столбце A, а проданные единицы — в столбце B.

Использование СУММЕСЛИМН для вычисления итога для диапазона дат

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

  • не позднее Даты начала
  • не позднее даты окончания.

Вот формула, которая вводится в ячейку D5:

= СУММЕСЛИМН ($ B $ 2: $ B $ 9, $ A $ 2: $ A $ 9, «> =»
& $ D $ 2, $ A $ 2: $ A $ 9, «<=" & $ 2)

  • Первый аргумент, $ B $ 2: $ B $ 9 ,
    — это диапазон чисел, которые мы хотим просуммировать.
  • Второй аргумент, $ A $ 2: $ A $ 9 , равен
    диапазон для проверки по критерию 1.
  • Третий аргумент, «> =» & $ D $ 2 ,
    — это диапазон со значением критерия 1 (дата начала), а
    оператор для использования с этим значением (больше или равно)
  • Четвертый аргумент, $ 2 $: 9 $ A $ ,
    диапазон для проверки критериев 2.
  • Пятый аргумент, «<=" & $ E $ 2 ,
    — это диапазон со значением критерия 2 (дата окончания) и оператором
    использовать с этим значением (меньше или равно)

В этом примере результат для выбранного диапазона дат составляет всего
Продано 494 единицы.Для проверки вы можете выбрать ячейки B3: B6 и посмотреть на
итоговое значение отображается в строке состояния Excel.

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

Использование СУММЕСЛИ для вычисления итога для диапазона дат

Для Excel 2003 и более ранних версий вы можете использовать функцию СУММЕСЛИ, чтобы
рассчитать итоговую сумму по единственному критерию. Мы будем использовать одну формулу СУММЕСЛИ
для суммирования всех единиц с датой продажи:

  • не позднее Даты начала

Затем мы воспользуемся другой формулой СУММЕСЛИ, чтобы вычесть любые значения, где есть
дата

Вот формула, которая вводится в ячейку D5:

= СУММЕСЛИ ($ A $ 2: $ A $ 9, «> =»
& $ D $ 2, $ B $ 2: $ B $ 9)
— СУММЕСЛИ ($ A $ 2: $ A $ 9, «>»
& $ E $ 2, $ B $ 2: $ B $ 9)

  • Диапазон, $ 2: $ 9 ,
    содержит числа, которые мы хотим просуммировать.
  • Критерий «> =» & $ D $ 2 :
    диапазон с датой начала и оператор для использования с этим значением
    (больше или равно)
  • Диапазон: $ 2 B $: 9 B $ — это диапазон
    проверить дату
  • Критерий «>» & $ E $ 2 :
    диапазон с датой окончания и оператор для использования с этим значением
    (больше)

В этом примере результат для выбранного диапазона дат составляет всего
Продано 494 единицы.Для проверки вы можете выбрать ячейки B3: B6 и посмотреть на
итоговое значение отображается в строке состояния Excel.

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

Суммируйте отфильтрованный список с ПРОМЕЖУТОЧНЫМ ИТОГОМ

После фильтрации строк в списке вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ,
вместо функции СУММ, чтобы суммировать числа в видимых строках.

  1. Примените к столу автофильтр.Здесь есть инструкция — Автофильтр
    Основы
  2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере
    первый столбец отфильтрован для связующих.
  3. Выберите ячейку непосредственно под столбцом, для которого требуется суммировать.
  4. Нажмите кнопку «Автосумма» на вкладке «Главная» ленты.
    • Если вы хотите, чтобы функция ПРОМЕЖУТОЧНЫЙ ИТОГ использовалась не в той ячейке,
      непосредственно под отфильтрованным списком вы можете ввести формулу вместо
      использования кнопки Автосумма.
  5. Формула ПРОМЕЖУТОЧНОГО ИТОГА будет автоматически вставлена, суммируя видимые
    ячейки в столбце
  • Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции,
    который определяет, как должны быть рассчитаны числа. Есть 9
    в этом примере, который сообщает Excel, что нужно СУММИРОВАТЬ числа.
  • Можно использовать другие номера функций, например 1 для СРЕДНЕГО и 3
    для COUNTA.Полный список приведен в моем блоге
    опубликовать всего отфильтрованный список.
  • Нажмите клавишу Enter, чтобы завершить ввод формулы.
  • Примечание : в Excel 2003 и более поздних версиях вы можете использовать формулу:
    = ПРОМЕЖУТОЧНЫЙ ИТОГ (109, B2: B9)
    для вычисления промежуточных итогов видимых ячеек в диапазоне, где строки были скрыты вручную,
    или отфильтрованный.

    Промежуточные номера функций

    Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции, которая
    указывает, как следует рассчитывать числа.Есть 11 функций
    который вы можете использовать в качестве первого аргумента в функции ПРОМЕЖУТОЧНЫЙ ИТОГ. Список
    находится в алфавитном порядке, чтобы вы могли запомнить некоторые числа,
    не заходя каждый раз в справку Excel.

    Каждая функция указана дважды. Первая группа функций — это
    пронумерованы 1-11.

    Каждая функция указана дважды. Вторая группа функций — это
    под номерами 101-111.

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

    Функция АГРЕГАТ, представленная в Excel 2010, аналогична ПРОМЕЖУТОЧНОМУ ИТОГУ.
    функция, но она имеет больше функций и может игнорировать значения ошибок.
    как скрытые строки в данных.

    Посмотрите это видео, чтобы узнать, как настроить АГРЕГАТИВНУЮ формулу,
    и письменные инструкции под видео.

    Суммируйте отфильтрованный список с помощью AGGREGATE

    После фильтрации строк в списке вы можете использовать функцию АГРЕГАТ,
    вместо функции СУММ, чтобы суммировать числа в видимых строках. Этот
    функция была представлена ​​в Excel 2010.

    Подобно функции ПРОМЕЖУТОЧНЫЙ ИТОГ, АГРЕГАТ игнорирует скрытые строки и
    предлагает несколько функций, например СУММ или СРЕДНЕЕ, для выбранных данных.Тем не менее, он имеет 19 функций по сравнению с SUBTOTAL.
    11 функций.

    В отличие от функции ПРОМЕЖУТОЧНЫЙ ИТОГ, АГРЕГАТ может игнорировать ошибки,
    а также скрытые строки и вложенные функции SUBTOTAL и AGGREGATE.

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

    1. Выберите ячейку, в которой вы хотите получить сумму
    2. Тип = АГРЕГАТ (
    3. В списке функций дважды щелкните 9 — СУММ, чтобы добавить 9 в качестве
      первый аргумент.
    4. Введите запятую и в списке параметров дважды щелкните параметр 3.
      или вариант 7. В этом примере 3 — второй аргумент, а результат
      будет игнорировать скрытые строки, ошибки и вложенные функции АГРЕГАТ и ПРОМЕЖУТОЧНЫЙ ИТОГ.

    5. Введите запятую и выберите диапазон ячеек, содержащих данные.
      — D2: D7 в этом примере.
    6. Введите скобку, чтобы завершить формулу, и нажмите клавишу Enter.

    Заполненная формула: = АГРЕГАТ (9,3, D2: D7)

    Сумма отдельных элементов в отфильтрованном списке

    Пример 1

    Лоран Лонгре создал формулу, которая позволяет работать с видимыми строками.
    после фильтра. Дополнительные сведения см. В разделе «Техника формул мощности» в этой статье.
    на веб-сайте Джона Уокенбаха (больше не доступен).

    Включая эту технику, СУММПРОИЗВ может использоваться для суммирования видимых элементов.
    в отфильтрованной таблице.В следующем примере столбец D был отфильтрован.
    для сумм больше 100. Следующая формула суммирует Итого
    сумм в строках, содержащих «Pen» в столбце A.

    • Фильтрующая колонна D для количеств, превышающих 100.
    • В ячейке A12 введите: Pen
    • В ячейке B12 введите следующую формулу:
      • = СУММПРОИЗВ (ПРОМЕЖУТОЧНЫЙ (3; СМЕЩЕНИЕ (A1: A10; СТРОКА (A1: A10))
        -МИН (СТРОКА (A1: A10)) ,, 1)), — (A1: A10 = A12), D1 : D10)
    • Нажмите клавишу Enter, чтобы завершить ввод формулы.

    Пример 2

    Другой пример использования SUMPRODUCT и SUBTOTAL вместе, см. В моем сообщении в блоге Subtotal и Sumproduct with Filter. Сэм поделился своей техникой выполнения дополнительных сумм или подсчетов на основе видимых данных в отфильтрованной таблице.

    В книге Сэма есть список с полями «Продукт», «Регион» и «Сумма». Он создал динамические именованные диапазоны для записей в каждом поле, используя INDEX и COUNTA.

    Вы можете получить книгу Сэма в разделе «Загрузки» ниже.

    Скачать файлы примеров

    • Примеры СУММ: Загрузите заархивированный образец книги функций суммирования.
      Рабочая тетрадь содержит примеры для СУММ, СУММЕСЛИ, СУММЕСЛИМН, СУММПРОИЗВ,
      ПРОМЕЖУТОЧНЫЙ ИТОГ и ОБЩИЙ ИТОГ. Заархивированный файл имеет формат xlsx,
      и не содержит макросов.
    • Заказы СУММЕСЛИМН: загрузите образец книги заказов СУММЕСЛИМН и просмотрите видеоролик Суммы сумм с 2 критериями.Заархивированный файл имеет формат xlsx и не содержит макросов.
    • 7 способов подвести итог: скачать 7 способов
      Образец рабочей тетради для подведения итогов, чтобы следовать вместе с видео «7 способов подвести итог»
      Заархивированный файл имеет формат xlsx и не содержит макросов.
    • Рабочая тетрадь Сэма: чтобы просмотреть книгу Сэма и формулы для выполнения дополнительных сумм или подсчетов на основе видимых данных в отфильтрованной таблице, загрузите образец файла SUMPRODUCT SUBTOTAL.
    • Ссылки на таблицы: Чтобы увидеть проблему с копированием формул со ссылками на таблицы, загрузите рабочую книгу по проблемам справочника таблиц. Заархивированный файл имеет формат xlsx и не содержит макросов
    Дополнительные уроки по функциям

    Список функций

    формул,
    Начало работы

    ПРОМЕЖУТОЧНЫЙ ИТОГ Функция

    СРЕДНЕЕ

    СЧЁТ / СЧЁТЕСЛИ

    Как добавить несколько ссылок на диапазон в формулы в Excel

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

    Уровень квалификации: Начинающий

    Экономьте время, ссылаясь на диапазоны с помощью клавиши Ctrl

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

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

    Это отлично подходит для таких функций, как СУММ, СЧЕТЕСЛИ, СУММЕСЛИМН, ВПР или любой функции, которая имеет аргументы для нескольких массивов (диапазонов).

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

    Запоминает абсолютное или относительное эталонное состояние

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

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

    Это может быть полезно, если вы хотите, чтобы все ссылки на диапазон имели одно и то же абсолютное / относительное состояние.

    Это не так полезно, если вы пишете что-то вроде формулы СУММЕСЛИМН и хотите, чтобы каждый аргумент имел другое состояние.

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

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

    Заключение

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

    Пожалуйста, оставьте комментарий ниже, если у вас есть какие-либо вопросы или дополнительные советы по экономии времени. Спасибо! 🙂

    Сложить (суммировать) целые столбцы или строки в Excel

    В этом руководстве рассказывается, как добавлять целые строки или столбцы в Excel.

    Функция суммы

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

    = СУММ (число1, число2,…)

    Функция Sum принимает входные данные в двух основных формах: ссылки на отдельные ячейки (например, = sum (a1, b2, c3)) или массив ячеек (= sum (A1: E1)). Мы будем использовать последний метод:

    Совет 1 : сочетание клавиш ALT + = (нажмите и удерживайте ALT, затем нажмите =) автоматически создаст функцию суммы.По возможности Excel угадывает, какие ячейки вы хотите суммировать, заполняя функцию суммы.
    Совет 2 : После использования сочетания клавиш ALT + = или после ввода = sum (используйте клавиши со стрелками, чтобы выбрать соответствующую ячейку. Затем, удерживая нажатой клавишу SHIFT или CTRL + SHIFT, выберите желаемый диапазон ячеек.
    Совет 3 : вместо клавиатуры вы также можете использовать мышь для перетаскивания и выделения желаемого диапазона и завершения формулы.

    Суммировать весь столбец

    Чтобы сложить весь столбец, введите Sum Function: = sum (а затем выберите нужный столбец, щелкнув букву столбца в верхней части экрана или используя клавиши со стрелками для перехода к столбцу и используя CTRL + Ярлык ПРОБЕЛ для выбора всего столбца.Формула будет иметь вид = сумма (A: A).

    Суммировать всю строку

    Чтобы сложить весь столбец, введите Sum Function: = sum (а затем выберите строку, щелкнув номер строки в левой части экрана или перейдя к нужной строке с помощью клавиш со стрелками и используя SHIFT + ПРОБЕЛ ярлык для выбора всей строки. Формула будет иметь вид = сумма (1: 1).

    Сложить сразу несколько столбцов или строк

    Для одновременного суммирования столбцов или строк используйте формулу вида: = сумма (A: B) или = сумма (1: 2).Помните, что вы также можете использовать сочетания клавиш CTRL + ПРОБЕЛ, чтобы выбрать весь столбец, или SHIFT + ПРОБЕЛ для всей строки. Затем, удерживая нажатой клавишу SHIFT, используйте клавиши со стрелками для выбора нескольких строк.

    Суммировать несмежные столбцы или строки за один раз

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

    Обратите внимание на разницу,
    Функции с несколькими суммами:

    Разделитель запятых:

    Часто вам нужно добавить весь столбец (или строку), кроме заголовка. В Excel 2013 нецелочисленные записи, включенные в формулу Sum, автоматически игнорируются. Если вы включили ячейку A1 (заголовок) в функцию суммы: = сумма (A: A), функция суммы будет работать правильно. Однако рекомендуется поддерживать обратную совместимость.

    Вместо этого вы можете использовать формулу = СУММ (A2: A1048576).Почему 1048576? На листах Excel всего 1 048 576 строк!

    Чтобы увидеть это, выберите ячейку в пустом столбце. Затем вы используете ярлык: CTRL + стрелка вниз, чтобы перейти к последней строке на листе.

    Сложить все строки, содержащие данные

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

    Autosum Magic

    Выберите ячейку выше / ниже или слева / справа от диапазона, который вы хотите суммировать. Затем используйте сочетание клавиш ALT + = или выберите Ленту формул> Автосумма. Это автоматически создаст для вас формулу!

    Ошибок общей суммы

    # ЗНАЧЕНИЕ! — у вас есть нецелые числа в формуле суммы (не проблема в Excel 2013+)

    # ССЫЛКА! — столбцы или строки, на которые ранее ссылались, больше не существуют

    # ИМЯ? — проверить написание формулы

    Для получения дополнительной информации об автосумме в Excel посетите веб-сайт Microsoft

    .

    Как автоматически заполнить СТРОКУ, КОЛОНКУ или ДИАПАЗОН ОДНЫМ ЗНАЧЕНИЕМ в Excel

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

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

    Как автоматически заполнить столбец или строку (смежные ячейки)

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

    Метод №1: Ctrl + D

    1. Введите данные в ячейку листа и нажмите Enter.
    2. Щелкните ячейку с данными и, удерживая нажатой левую кнопку мыши, перетащите, чтобы выбрать остальные ячейки в строке или столбце, которые вы хотите заполнить автоматически.
    3. Отпустите кнопку мыши.
    4. Нажмите Ctrl + D (клавиша Ctrl удерживается, пока нажата клавиша D), и ячейки заполняются.

    Метод № 2: Использование ручки заполнения

    Маркер заполнения — это мощный инструмент Excel для автозаполнения линейных рядов, рядов роста и многих других типов данных.

    Маркер заполнения также можно использовать для автозаполнения одного и того же значения, ПОКОЛЬКО это значение не является стартером серии. В таком случае необходимо использовать метод №1. Примеры серий включают дни недели, названия месяцев, серии с датами и временем.

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