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

Как Работать с Формулами в Excel: Копирование, Вставка и Автозаполнение.

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

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

Тут перечислены ключевые навыки, которые вы получите в результате этого урока:

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

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

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

В этом уроке вы узнаете как «приручить» ваши электронные таблицы и как управляться с формулами. Более того, то, чему вы научитесь можно использовать со многими электронными таблицами; работаете ли вы с Apple Numbers, Google Sheets, или Microsoft Excel — вы найдете здесь профессиональные советы по работе в формулами в электронных таблицах. Давайте начинать.

Как Работать с Формулами в Excel (Короткий Видео Урок)

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

Продолжайте читать дальше, что бы узнать больше о том, как работать с формулами и электронными таблицами.

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

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

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

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

Строки — это горизонтальные ряды, которые пронумерованы с левой стороны. Столбцы идут с лева на право и обозначены буквами. Когда строка и столбец пересекаются, образуется ячейка Excel. Если встречается столбец C cо строкой 3, то получается ячейка, которая обозначается как С3.

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

Ячейки названные в соответствии с пересечением строк и столбцов; столбец F и строка 4 пересекаются и в результате получается ячейка F4.

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

Работа с Формулам и Функциями в Excel

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

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

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

Посмотрите, как это выглядит в Excel, на картинке ниже:

Простая Формула и результат в Excel.

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

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

Ячейка содержит «=4+4» в виде формулы, а в самой таблице показывается результат. Попробуйте использовать другие подобные варианты вычислений, такие как вычитание, умножение ( с символом *) и деление (с символом /). Вы можете печатать формулы либо в строке формул либо в ячейке.

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

Формулы Excel с Обращением к Ячейке

Есть два способа работы с формулами:

• Использовать формулы с данными введенными прямо в формуле, как в примере, который мы рассматривали выше (=4+4)
• Использовать формулы со ссылкой на ячейку, что бы было легче работать работать с данными, например как =А2+А3

Давайте взглянем на пример ниже. На скриншоте ниже, я ввел данные для продаж в моей компании для первых 3х месяцев 2016 года. Теперь, я их сложу, просуммировав продажи по всем трем месяцам.

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

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

• =СРЗНАЧ — средние значения.
• =СЧЁТ подсчитывает число ячеек, которые содержат числовые данные.
• =ДЕНЬ возвращает значение для сегодняшнего числа.
• =СЖПРОБЕЛЫ удаляет лишние пробелы в строке, кроме пробелов между словами.

Как Копировать и Вставить Формулы в Excel

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

Когда мы копирует и вставляем ячейку с формулой, мы не просто копируем величину, мы копируем формулу. Если мы вставим ее куда-то еще, мы копируем формулу в Excel.

Посмотрите, что я делаю в примере ниже:

1. Я копирую (Ctrl+C) ячейку E2, в которой была формула, которая складывала ячейки B2, C2 и D2.
2. Затем, я выбираю другие ячейки в столбце Е, щелкнув и потянув мышку вниз в пределах столбца.
3. Я жму (Ctrl+V), что бы вставить туже Excel формулу во все выделенные ячейки в столбце E.

Как вы можете видеть на скриншоте выше, вставка формулы не вставляет значение ($21,933). Вместо этого, вставляется формула.

Формула, которую мы скопировали была в ячейке E2. Она складывала ячейки B2, C2, и D2. Когда мы вставляем ее, например, в ячейку E3, туда не вставляется то же самое, а туда вставляется формула, которая складывает значения в ячейках B3, C3, и D3.

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

Как Работает Автозаполнение Формул в Excel

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

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

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

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

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

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

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

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

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

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

Мы написали формулу для умножения суммы продаж на проценты бонуса.

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

Упс! Хотя для первого случая бонусы рассчитались нормально, для других случаев расчеты не получились.

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

Вот, например, как Excel пытается рассчитать значения для F3 и F4:

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

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

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

До этого момента, наша формула выглядела таким образом:

=Е2*Н2

Что бы «заморозить» использование в этой формуле значение из ячейки Н2, давайте преобразуем ее в абсолютную ссылку:

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

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

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

Резюмируем и Продолжаем Обучение

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

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

• В этом уроке, мы вскользь коснулись вопроса использования Математических формул; А здесь вы можете найти достаточно полное руководство по тому Как Работать с Математическими Формулами в Excel .
• Возможно вам приходится работать с «засоренной» таблицей или плохим массивом данных. Здесь вы узнаете как Найти и Избавиться от Дубликатов всего за пару кликов.
• Если вам приходится заниматься анализом и последующим представлением данных, вы можете использовать PowerPoint. Здесь вы узнаете Как Вставить Таблицу Excel в PowerPoint всего за 60 Секунд .

Как вы научились работать с формулами? Оставьте комментарий, если у вас есть советы или вопросы по таблицам Excel.

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

А также типичные сценарии, когда вам все это требуется.

Копирование ячейки с формулой в Excel — это на самом деле не проблема. И тут бы можно было закончить заметку, но нет! Вы выделяете то, что нужно скопировать. И Excel послушно скопирует текст, формулу, формат, шрифт. Другими словам — все, что было в исходной ячейке. Но проблемы начинаются, когда вы захотите скопировать ячейку только с формулой или, например, содержимое ячейки без формулы. А нередко нужно скопировать и просто саму формулу в отрыве от ячейки. Часто это необходимо, например, для того, чтобы составлять таблицы для анализа различных данных и показателей (например, посещаемость страниц) в рамках SEO-продвижения сайта
Как поступить в таких случаях? – Читайте наше руководство!

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

Копировать / вставить

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

Копирование происходит через одноименную команду в меню, либо нажав сочетание клавиш Ctrl+C.

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

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

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

Пример: в ячейке A1 нашей таблицы есть формула, которая ссылается на ячейку B1. Мы хотим вставить какую-либо формулу в ячейку C1. В таком случае, по умолчанию, формула в ячейке C1 будет ссылаться на ячейку D1.

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

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

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

• Если нужно оставить ячейку неизменной — используйте символ $. Пример: $C$2.
• Если нужно оставить неизменным только столбик — используйте тот же символ. Пример: $C2
• Если нужно оставить неизменным только строчку — используйте тот же символ. Пример: C$2.

Не используйте символ $ в изменяемых ячейках! Просто перетащите или скопируйте такую вставку.

В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров

Что происходит с ячейкой с формулой при копировании

Проделаем небольшой эксперимент:

1. Откроем таблицу.
2. Копируем формулу любым удобным способом.
3. Вставляем ее через инструмент «Вставить функцию».

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

Формула после вставки перешла в соседнюю ячейку

Мы рекомендуем два решения этой проблемы:

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

Итак, если новая формула переходит в соседнюю ячейку, то допустимо будет применить инструмент «Автофильтр»:

1. Наводим стрелку в угол ячейки (обязательно в нижний, справа).
2. Сразу же курсор примет вид символа +.
3. Кликаем по этому плюсу и зажимаем правую кнопку.
4. Не отпуская кнопки двигаем курсор вниз, вверх, либо — в любую сторону. Формула будет автоматически подтягиваться в новую ячейку.

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

Важно: независимо от того, куда вы переместите эту формулу, она постоянно будет ссылаться на A1 и A2.

Удаление $ перед 1 и 2 позволяет изменять строки, но не столбцы. Изменение $ перед A позволит менять столбцы, но не даст изменять строки.

Результат копирования: относительные и абсолютные формулы

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

Все виды формул в Excel можно разделить на два: относительные и абсолютные. Давайте смотреть в чем между ними разница.

Относительные формулы

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

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

Пример: у нас имеется одна формула «=A1+B1» в ячейке C1. Мы копируем указанную формулу в ячейку C2, при этом – формула в ячейке C2 автоматически изменится на «=A2+B2».

Абсолютные формулы

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

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

Пример: в нашей таблице имеется формула вида «=$A$1+$B$1» в ячейке C1. Мы хотим скопировать эту формулу в ячейку C2. Делаем это и видим: формула в ячейке C2 останется такой же, как и исходная формула «=$A$1+$B$1».

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

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

Решений у этой задачи может быть несколько. Рассмотрим каждый вариант.

Предварительные условия: допустим, у нас есть формула вида:

И нам нужно скопировать её вниз, чтобы получить:

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

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

Вот еще несколько важных моментов, которые нужно знать:

• Если поставить символ $ перед столбцом (например, $D62), то столбец будет заблокирован, но строка может изменяться,
• Если поставить символ $ перед строкой (например, D$62), то строка будет заблокирована, но столбец может изменяться.
• Если поставить символ $ перед обеими строками, то они будут заблокированы целиком. Например: $D$73.

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

Альтернативный вариант 1

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

1. Перейдите к нужной ячейке.
2. Нажмите F2.
3. Нажмите CTRL+A (так вы выделите все содержимое ячейки).
4. Нажмите CTRL+C (так вы скопируете все содержимое).
5. Теперь кликните по какой-либо другой ячейке, куда нужно вставить формулу, и нажмите F2.
6. Нажмите CTRL + V. Будет вставлена абсолютно идентичная формула.
7. Теперь осталось просто перетащить формулу по всему диапазону.

Альтернативный вариант 2

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

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

Попробуйте потянуть эту формулу вниз или вправо. Вы увидите, что ссылка осталась такой, какой была изначально. Опять же — вы можете просто удалить символ доллара перед «C». В таком случае значение 62 будет сохранено. Если же вы сотрете символ доллара перед 62, то сохранится «C».

Альтернативный вариант 3

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

И вот еще один вариант решения этой задачи:

1. Измените первую формулу (т.е. исходную формулу), закрепив C62.
2. Направьте указатель мыши в ячейку, где находится первое вхождение формулы.
3. Нажмите F2 (редактировать)
4. Переместите курсор на C62.
5. Нажмите F4 (закрепить).

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

Задача практически решена. Осталось лишь только копировать значения в соответствующие ячейки.

Важно:

• Все ссылки, которые ведут на С62, — абсолютные
• Все ссылки, которые ведут на ячейки К, — относительные.

Сложные вопросы в работе с Excel

Я хочу скопировать / вставить идентичную формулу. Как это сделать?

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

Для одной формулы

Просто скопируйте ее из панели формул и вставьте в нужное место.

Для нескольких формул

1. Создайте пустой лист в рабочей книге.
2. Вернитесь к первому рабочему листу и скопируйте нужный вам диапазон ячеек.
3. На новом рабочем листе добавьте идентичный диапазон ячеек и вставьте его туда. Обратите внимание: все адреса ссылок в формулах остались прежними.
4. Перетащите вставленный диапазон в тот же диапазон на новом рабочем листе. Именно на этот лист, в конечном итоге, и нужно будет вставить диапазон (внимание: речь про первый рабочий лист). Обратите внимание: все наши адреса ссылок остались прежними (внутри формул).
5. Скопируйте этот диапазон формул на новый рабочий лист.
6. Перейдите в нужный диапазон назначения на первом рабочем листе (это должен быть тот же диапазон адресов, из которого скопировали на новый рабочий лист) и примените команду вставки.

Обратите внимание: все ранее используемые адреса ссылок остались прежними (внутри формул).

Почему функция суммирования не работает?

Не видя вашего рабочего листа, сложно сделать на 100% корректный вывод и дать совет. Но наиболее частой причиной некорректной работы СУММ можно назвать наличие в ячейке текста (а не цифр).

Дело в том, что функция SUM (СУММ) всегда игнорирует любые текстовые ячейки.

Обычно текстовые числа легко обнаружить: они выровнены по левому краю и имеют зеленый маркер в углу. Проблема в том, что некоторые пользователи привыкли выравнивать текст по правому краю. Но Excel – умная программа: даже когда номер выровнен по правому краю, зеленый маркер также отображается. Некоторые пользователи идут еще дальше: они выбирают опцию «Игнорировать эту ошибку». В итоге — текст показывается как обычные числа (поэтому всегда обещайте внимание на зеленый уголок в ячейке):

Не нужно использовать конструкции типа =B7+B6+B5+B4+B3+B2. Вы получите ошибки. Но эту формулу можно задействовать для обнаружения текста, который маскируется под цифры.

Вот как решить проблему чисел, сохраненных как текст:

1. Выберите диапазон чисел (не включая функцию SUM или СУММ).
2. Используйте сочетание горячих клавиш Alt+D+E+F. Это приведет к запуску инструмента Text to Columns. В итоге текстовые числа автоматически переведутся в настоящие. Посмотрите на пример ниже:

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

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

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

Два чекбокса Manual (либо, в противоположном случае — Automatic) будут выводиться на панели инструментов. Соответственно, вы сможете очень быстро заметить, когда редактор находится в ручном режиме и наоборот.

Можно ли сделать фильтрацию дубликатов значений из уже настроенного диапазона?

Да, можно. В Excel есть специальная формула для этого:

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

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

• Если вернулся «duplicate» — значение в этой ячейке уже было в диапазоне.
• Если вернулся «unique» — значение в этой ячейке еще не было в диапазоне.

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

Заключение: краткая инструкция

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

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

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

Выделите ячейку и воспользуйтесь сочетанием горячих клавиш Control+С, или используйте правую кнопку мыши для выделения. Вставьте ячейку с формулой туда, куда вам необходимо (при помощи команды Control+V или через меню «Вставить»).

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

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

1. Нажать F2 и скопировать текст формулы вручную и вставить его в нужное место.
2. Скопировать ячейку, перейти в ячейку (куда вы хотите вставить формулу) и просто вставить её. Формула будет вставлена с соответствующими корректировками ссылок. Либо — можно скопировать ячейку с формулой и перейти в новую ячейку, воспользоваться сочетанием клавиш Alt+A+E+S и выделить формулу (это специальная вставка).

Если же нужно копировать формулу из «Эксель» в другое приложение, — нажмите клавишы Alt+. Вы увидите длинный список всех формул. Из него вы уже сможете скопировать любой необходимый вариант.

Как копировать текст формулы без результата

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

1. Нажмите клавишу F2. Выделите формулу в ячейке и задействуйте комбинацию гор. клавиш Ctrl+C (или используйте функцию копирования, вызываемую через меню).
2. Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (находится она на панели инструментов).

Теперь вы можете вставить формулу как-текст в любую другую программу.

Помните важное: при вставке формулы внутри «Эксель» автоматически обновятся ссылки.

Как вставить одну и ту же формулу несколько раз

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

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

• Перетащить или заполнить – ссылки на формулы будут меняться в каждой ячейке.
• Введите «статическую» формулу (например, $A$1), затем – перетащите её или заполните.

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

Способов скопировать такую ячейку несколько:

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

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

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

Здесь все не так сложно:

1. Примените нужные вам фильтры.
2. Маркируйте цветным выделением столбик, содержащие данные.
3. Удалите все фильтры. Для этого используйте сочетание горячих клавиш Alt+D, затем нажмите клавишу F, и после – клавишу S.
4. Теперь нужно сделать сортировку столбика по цвету (его мы задействовали в шаге до этого).
5. Спокойно применяем специальную вставку. Данные, которые расположены между фильтрами, никак не пострадают.
6. Уберите маркирование цветом, которое мы сделали в предыдущих шагах. Удалите раскраску из первого шага.
7. Снова примените фильтр.

Как копировать и вставлять текст и формулы в таблицу Excel

Самый простой способ — кликните по нужной ячейке и тут же нажмите клавишу F2, затем — просто вставьте текст. Либо — кликните по строке для ввода формулы (Fx), вставьте ее туда и нажмите Enter.

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

Если вы копируете «из Excel в Excel», то можно вставлять специальные значения или сами формулы.

Как написать формулу в Excel: обучение, самые нужные формулы

Когда-то, написать самостоятельно формулу в Excel — для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…

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

1. Основные операции и азы. Обучение основам Excel.

Все действия в статье будут показываться в Excel версии 2007г.

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

Формула должна начинаться со знака «=». Это обязательное условие. Дальше вы пишите то, что вам нужно посчитать: например, «=2+3» (без кавычек) и нажимаете по клавише Enter — в результате вы увидите, что в ячейке появился результат «5». См. скриншот ниже.

Важно! Несмотря на то, что в ячейке А1 написано число «5» — оно считается по формуле («=2+3»). Если в соседней ячейке просто текстом написать «5» — то при наведении курсора на эту ячейку — в редакторе формулы (строка сверху, Fx) — вы увидите простое число «5».

А теперь представьте, что в ячейку вы можете писать не просто значение 2+3, а номера ячеек, значения которых нужно сложить. Допустим так «=B2+C2».

Естественно, что в B2 и C2 должны быть какие-нибудь числа, иначе Excel покажет нам в ячейке A1 результат равный 0.

И еще одно важное замечание…

Когда вы копируете ячейку, в которой есть формула, например A1 — и вставляете ее в другую ячейку — то копируется не значение «5», а сама формула!

Причем, формула изменится прямо-пропорционально: т.е. если A1 скопировать в A2 — то формула в ячейке A2 будет равна «=B3+C3». Excel сам меняет автоматически вашу формулу: если A1=B2+C2, то логично, что A2=B3+C3 (все цифры увеличились на 1).

Результат, кстати, в A2=0, т.к. ячейки B3 и С3 не заданы, а значит равны 0.

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

Если вы не хотите, чтобы B2 и С2 изменялись при копировании и всегда были привязаны к этим ячейкам, то просто добавьте к ним значок «$». Пример ниже.

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

2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)

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

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

Для этого переходим в ячейку, в которой будет показываться результат и пишем формулу: «=СУММ(C2:C5)». См. скриншот ниже.

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

2.1. Сложение с условием (с условиями)

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

Для этого есть замечательная формула «СУММЕСЛИМН«. Сразу же пример, а затем пояснение каждого символа в формуле.

=СУММЕСЛИМН( C2:C5 ; B2:B5 ; «<100» ), где:

C2:C5 — та колонка (те ячейки), которые будут суммироваться;

B2:B5 — колонка, по которой будет проверяться условие (т.е. цена, например, менее 100);

«<100» — само условие, обратите внимание, что условие пишется в кавычках.

Ничего сложного в этой формуле нет, главное соблюдать соразмерность: C2:C5;B2:B5 — правильно; C2:C6;B2:B5 — неправильно. Т.е. диапазон суммирования и диапазон условий должны быть соразмерны, иначе формула вернет ошибку.

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

3. Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)

Довольно часто-встречающаяся задача: подсчитать не сумму значений в ячейках, а количество таких ячеек, удовлетворяющих определенным условиям. Иногда, условий очень много.

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

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

=СЧЁТЕСЛИМН( B2:B5 ; «>90» ), где:

B2:B5 — диапазон, по которому будут проверять, по заданному нами условию;

«>90» — само условие, заключается в кавычки.

Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.

Формула приобретает вид:

Здесь все осталось таким же, кроме еще одного условия (C2:C6;»<20″). Кстати, таких условий может быть очень много!

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

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

Представим, что к нам пришла новая таблица, с новыми ценниками для товара. Хорошо, если наименований 10-20 — можно и в ручную их все «перезабить». А если таких наименований сотни? Гораздо быстрее, если бы Excel самостоятельно нашел в совпадающие наименования из одной таблицы в другой, а затем скопировал новые ценники в старую нашу таблицу.

Для такой задачи используется формула ВПР. В свое время сам «мудрил» с логическими формулами «ЕСЛИ» пока не встретил эту замечательную штуку!

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

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

=ВПР( A2 ; $D$2:$E$5 ; 2 ), где

A2 — то значение, которое мы будем искать, чтобы взять новый ценник. В нашем случае ищем в новой таблице слово «яблоки».

$D$2:$E$5 — выделяем полностью нашу новую таблицу (D2:E5, выделение идет от верхнего левого угла к правому нижнему по диагонали), т.е. там, где будет производится поиск. Знак «$» в этой формуле необходим для того, чтобы при копировании этой формулы в другие ячейки — D2:E5 не менялись!

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

2 — Когда слово «яблоки» будет найдено, функция должна знать, из какого столбика выделенной таблицы (D2:E5) скопировать нужное значение. В нашем примере копировать из колонки 2 (E), т.к. в первой колонке (D) мы производили поиск. Если ваша выделенная таблица для поиска будет состоять из 10 колонок, то в первой колонке производится поиск, а со 2 по 10 колонки — вы можете выбрать число для копирования.

Чтобы формула =ВПР(A2;$D$2:$E$5;2) подставила новые значения и для других наименований товара — просто скопируйте ее в другие ячейки столбца с ценниками товара (в нашем примере копируйте в ячейки B3:B5). Формула автоматически произведет поиск и копирование значения из нужной вам колонки новой таблицы.

5. Заключение

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

Надеюсь что кому-то пригодятся разобранные примеры и помогут ускорить его работу. Удачных экспериментов!

PS

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

Как посчитать скидку в Экселе (Excel)?

Как рассчитать скидку (процент скидки) в программе Excel?

Задачи на подсчет скидок бывают разные

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

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

Рассмотрим несколько задач, связанных с расчётом скидки в Excel.

1) Имеется исходная цена товара, а также процент скидки. Нужно рассчитать цену товара со скидкой.

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

Для решения этой нужно воспользоваться формулой вычитания процентов.

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

Цена со скидкой = Старая цена — Старая цена * Скидка.

Виноград = 180 — 180 *0,1 = 162 рубля.

В программе Excel формула расчёта цены со скидкой будет такой (записываем её в ячейку E3):

Осталось скопировать данную формулу для остальных товаров.

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

Вот как это выглядит в Экселе:

Это обычная формула вычитания.

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

Скидка (в %) = (Старая цена — Цена со скидкой) / Старая цена.

Вот формула в Excel:

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

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

1. Создаем таблицу из пяти столбцов;
2. Заполняем шапку: наименование товара, цена, руб, скидка 10%, цена со скидкой, руб;
3. Ячейки С3, D3, E3 форматируем в финансовый формат;
4. В ячейку С3 ставим стоимость;
5. В ячейку D3 заносим формулу: знак =С3 * 10%;
6. В ячейку Е3 также ставим формулу: знак =С3+D3

Это один из самых простых вариантов. Удачи. Буду рад если кому-то помог.

Если известны начальная цена А и конечная цена В, то расчёт идёт по формуле

Для Экселя будет тоже самое, если А и В записать в ячейки А1 и В1, а результат готовить в ячейке С1. Таким образом, в ячейку С1 нужно записать

Методика расчета скидки (процента скидки) в программе Excel очень простая. Для этого нам необходимо взять две цены на товар: старую (без скидки) и новую (со скидкой). В расчете старая цена принимается за 100%. От нее отнимается процентное соотношение новой цены к старой. Все действия заносятся в одну формулу в одной ячейке Excel.

Например, нам нужно посчитать размер скидки на утюг стоимостью 1690 руб, который на время акции продается за 890 руб. Формула расчета будет следующей: 100%-(890/1690). У нас получится 47,34%.

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

Все верно! Продавец округлил скидку до целого значения — 47%.

Как же все считается в Excel? Посмотрим на данном примере.

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

В примере выше (скрин из Excel) формула расчета будет следующей: =100%-H4/E4. Она заносится в ячейку K4. Далее мы просто ухватываемся за маленький черный квадратик в нижнем правом углу выделенной ячейки K4 и протягиваем вниз на 3 ячейки. Эксель автоматически считает размер скидки для всех остальных товаров (фена и робота-пылесоса).