Как составить формулу в Excel – простые примеры в простой инструкции. Excel вставка в формулы


Как вставить формулу в Excel – простая инструкция

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

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

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

Как вставить формулу в Excel – простая инструкцияСоздание формулы Excel

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

X+Y=Z

В нём X, Y – это какие-то цифры, которые мы знаем, и Z – неизвестный пока итог их сложения. Перед тем, как составить форму в Excel, откройте документ, и определите в нём ячейку, в которой буде содержаться итог выражения. В моём случае это C1.

В этой ячейке записываем формулу, которая начинается со знака «=», затем указываем левым одиночным кликом мышки на ячейку, которая будет у нас «X», в моём случае — это A1. После этого видим, что в ячейке C1 записалась координата ячейки первого слагаемого. Затем ставим алгебраический оператор, который выполняет вычисление, то есть «+» (набираем прямо с клавиатуры). И в конце кликаем мышкой на ячейку, которая у нас «Y», в моём случае — это B1, её координата сразу же появляется в ячейке, где у нас итог, то есть в С1. В итоге наша формула выглядит так «=A1+B1».

Как вставить формулу в Excel – простая инструкция

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

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

После того, как составить формулу в Excel удалось, нужно нажать клавишу «Enter». И теперь можно попробовать вписать какие-то цифры в ячейки A1 и B1, чтобы посмотреть, как посчитается итог в C1.

Перед тем, как составить формулу в Excel, запомните, какие алгебраические операторы можно поставить между координатами ячеек:
  • «+» — сложение;
  • «-» — вычитание;
  • «*» — умножение;
  • «/» — деление.

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

X+Y-N+5=Z

Здесь у нас, имеются известные переменные X, Y и Z, а также постоянная — число 5. Необходимо вычислить результат операции между ними. Для этого сначала вписываем в нужную ячейку постоянную, у меня она будет в ячейке B2. Затем снова переходим в ячейку, где у нас записывается итог вычислений, в моём случае – C1, и кликаем на неё дважды, чтобы продолжить нашу формулу.

Итак, число N у нас будет ячейка А2. Я продолжаю формулу знаком «–», и кликаю потом на A2, чтобы она появилась в формуле. Затем пишу «+» и кликаю на ячейку, где у нас постоянное число 5, то есть на B2. Формула Excel получает такой вид ««=A1+B1-A2+B2». В конце нажимаю «Enter» и можно испытывать формулу.

Как вставить формулу в Excel – простая инструкция

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

Составить формулу в Excel можно не только с одним, но и с двумя и более алгебраическими операторами. Также в формуле может участвовать какая-то постоянная величина (одна или несколько), для этого необходимо указать координаты этих ячеек. Кроме этого, постоянное число может быть указано непосредственно в формуле, то есть, она обретёт такой вид «=A1+B1-A2+5».

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

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

Например, как на этом скриншоте. Формула там выглядит так «=A1+B1(A3*B3)-A2+B2».

Как вставить формулу в Excel – простая инструкция

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

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

system-blog.ru

Работа в Excel с формулами и таблицами для чайников

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

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

ОператорОперацияПример
+ (плюс)Сложение=В4+7
- (минус)Вычитание=А9-100
* (звездочка)Умножение=А3*2
/ (наклонная черта)Деление=А7/А8
^ (циркумфлекс)Степень=6^2
= (знак равенства)Равно
Меньше
>Больше
Меньше или равно
>=Больше или равно
Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

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

В нашем примере:

  1. Поставили курсор в ячейку В3 и ввели =.
  2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
  3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+"=", чтобы вставить столбец.
  2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
  3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
  4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» - выбираем формулу для автоматического расчета среднего значения.

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

exceltable.com

Как вставить формулу в ячейку (таблицу) «Excel».

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

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

Способ первый.

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

    Мастер функций в ЭксельМастер функций в Эксель

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

    Аргументы функцииАргументы функции

  5. Нажать «Ok».

Подробный пример использования функции (формулы) показан по ссылке.

 

Способ второй.

  1. Кликнуть по ячейке, которой хотите присвоить функцию.
  2. Ввести(вставить) в нее знак равно («=»).
  3. После знака равно («=») написать ручным вводом с клавиатуры формулу. Например: =A1+A3.A1 плюс А3
  4. Эта формула посчитает сумму ячеек А1 и А3.

Кстати, если Вас интересует, как вставить красивую математическую формулу в документ «Excel»

с различными знаменателями, делителями и степенями, прочтите следующую статью:

математическая формула

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

 

 

ruexcel.ru

что такое fx, условия и тд

 

Доброго времени суток.

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

Формулы ассоциируются у вас с чем-то сложным и непонятным? После прочтения данной статьи вы перестанете так думать, потому что использовать их в программе предельно просто. Главное — понять алгоритм действия. Я вам в этом помогу.

Начнем с азов

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

  • Выделяете ячейку;
  • В строке Fx над таблицей пишете, =3+5 или другие цифры;
  • Нажимаете клавишу Enter;

В выделенной ячейке появляется цифра 8.

 

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

  • Выделите ячейку, где должен появиться результат;
  • В строке Fx запишите, к примеру, =А1+В8+Е5.

У вас появится сумма значений этих ячеек.

 

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

  • Выделите ячейку, где будет выведен итог;
  • Поставьте знак равенства в Fx;
  • Щелкните на ячейке А1;
  • Ее название появится в строке формул, поставьте после него +;
  • Проделайте то же самое с В8 и Е5;
  • Нажмите Enter.

 

Допустимые символы

Я привожу примеры со знаком «+», но знайте, что помимо него можно использовать минус, умножение (*), деление (/), больше или равно (>=) и т. д. В общем, все те знаки, которые вы изучали по математике. Также можете записывать формулы со скобками (=А1*(В8+Е5)). Помните со школы, что вычисления в них выполняются в первую очередь?

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

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

 

Степень

Если захотите узнать значение числа в какой-либо степени, используйте знак «^». Это может быть, например, =4^2 либо =А1^С6. Во втором случае вы получите значение числа, записанного в ячейке А1 в той степени, которая указана в клетке С6.

Текст

Не обязательно работать только с цифрами — можно объединять и текст. Для этого используется символ & (амперсанд). Допустим, в ячейке А1 у вас написано Петр, в В2 — Сидоров, а в С3 вы хотите получить результат. Выделите последнюю и запишите такую формулу =А1&В2, в С3 у вас появится «ПетрСидоров».

 

Виды ссылок

Excel предполагает 3 вида ссылок на ячейки: относительные, абсолютные и смешанные. Первые мы рассматривали в качестве примеров выше. Абсолютные ссылки не могут менять своего положения и обозначаются так «$A$1». Смешанные, как вы сами понимаете, включают в себя и одни, и другие ($A1, A$1).

 

Что все это значит? Снова разберем составление формулы на примере:

  • Вы подсчитали сумму ячеек А1 и А2, и вывели ее в А3.
  • Зажмите справа на нижнем крае клетки А3 курсор правой кнопки мыши и потяните его на поле В3;

 

 

  • После того, как вы отпустите кнопку, появится контекстное меню, в котором выберите команду «Копировать»;
  • Значение А3 продублируется в соседнюю ячейку;
  • Активируйте ее и вы увидите, что оно подсчитано по формуле =В1+В3.

Таким образом, относительные ссылки как бы подстраиваются под ваши запросы. Если вы хотите, чтобы какое-то поле в формуле оставалось неизменным, допустим С1, запишите ее так =А1+А2+$C$1. Это будет смешанная ссылка, где первые две составляющие являются относительными, а третья — абсолютной.

 

Формулы СУММ, СУММЕСЛИМН и СЧЁТЕСЛИМН

У вас большая таблица? Тогда вписывать каждое значение вручную будет отнимать много времени. Понадобятся более сложные формулы. Начнем с «СУММ»:

  • Выделите ячейку, где появится результат.
  • Определите диапазон клеток, числа в которых нужно суммировать. Это может быть строка, столбец и пр. В нашем случае будет В2:В7.
  • В строку Fx запишите =СУММ(В2:В7).
  • В выбранной на первом шаге клетке появится сумма чисел данного диапазона.

Теперь представьте, что у вас есть какое-то условие: например, прибавить нужно не все числа в определенной области, а только те, которые меньше 15. Причем подсчеты вы ведете не только среди ячеек В2:В7, но и среди Е3:Е14. Тогда ваша формула будет выглядеть так =СУММЕСЛИМН(В2:В7; Е3:Е14;«<15»).

 

До этого мы считали содержимое ячеек, но можно вычислить количество самих клеток, отвечающих определенным условиям.  В качестве примера возьмем тот же диапазон с тем же условием. Выглядеть это будет так: =СЧЁТЕСЛИМН(В2:В7; Е3:Е14;«<15»).

 

Упрощение задачи

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

 

Вариант 1

Excel позволяет автоматически подсчитывать итоговое значение. Воспользуйтесь опцией «Автосумма». Соответствующую кнопку вы найдете в поле «Редактирование» на главной вкладке программы.

 

Вариант 2

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

Один из примеров: вы ведете учет товара и в категории «Бакалея» у вас есть 10 наименований. Чтобы они не занимали большое пространство, удобно было бы держать их в свернутом виде, правда? Автоматически сворачивать их можно как раз таки с помощью макроса.

Для его записи вам необходимо:

  • На вкладке «Вид» нажать на кнопку «Макрос»;
  • В ниспавшем списке выбрать «Запись макроса»;
  • В диалоговом окне дать ему название, и вписать код.

 

Вариант 3

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

 

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

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

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

 

 

И как всегда, рекомендую вам неплохой курс по теме экселя: http://excel.konstantinfirst.com/

Удачи вам !

 

 

 

profi-user.ru

14 способов вставки Excel, о которых вы должны знать

Вставка или Ctrl+V, пожалуй, самый эффективный инструмент доступный нам. Но как хорошо вы владеете им? Знаете ли вы, что есть как минимум 14 различных способов вставки данных в листах Ecxel? Удивлены? Тогда читаем этот пост, чтобы стать пэйст-мастером.

Данный пост состоит из 2 частей:

— Основные приемы вставки

— Вставка с помощью обработки данных

1. Вставить значения

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

Начиная с Excel 2010, функция вставки значений отображается во всплывающем меню при нажатии правой клавишей мыши

Вставить значения правой кнопкой Excel

 2. Вставить форматы

Вставить форматы ExcelНравиться этот чудный формат, который сделал ваш коллега? Но у вас нет времени, чтобы так же оформить свою таблицу. Не беспокойтесь, вы можете вставить форматы (включая условное форматирование) из любой скопированной ячейки. Удерживая клавишу Alt, последовательно нажимайте Я, М, Ф, Ф, Ф и в конце нажмите клавишу Ввода.

Те же самые действия можно произвести с помощью меньшего количества операций, воспользовавшись меню, которое выпадает при нажатии правой кнопки мыши (начиная с Excel 2010).

3. Вставить формулы

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

4. Вставить проверку данных

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

Условия на значения Excel

5. Скопировать ширину столбцов с других ячеек

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

6. Берем комментарии и вставляем в другом месте

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

7. И конечно, вставляем все подряд

В этом нам помогут сочетания клавиш Ctrl+V или Alt+Я+М или клавиша вставки на панели инструментов.

Вставка с помощью обработки данных

8. Вставка с дополнительной математической операцией

Вставка с дополнительной математической операцией ExcelК примеру, у вас имеется строка 1 со значениями 1, 2, 3, и строка 2 со значениями 4, 5, 6. И вам необходимо сложить обе строки, чтобы получить 5, 7, 9. Для этого копируем первую строку, жмем правой кнопкой мыши по строке 2, выбираем Специальная вставка, ставим переключатель на «Сложить» и жмем ОК.

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

Вставка с дополнительными опреациами Excel

9. Вставка с учетом пустых ячеек

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

Вставка с учетом пустых строк Excel

В диалоговом окне «Специальная вставка» установите галку «Пропускать пустые ячейки»

10. Транспонированная вставка

транспониированная вставка ExcelК примеру, у вас имеется колонка со списком значений, и вам требуется переместить (скопировать) данные в строку (т.е. вставить их поперек). Как бы вы это сделали? Ну конечно, вам следует воспользоваться специальной вставкой и в диалоговом окне установить галку «Транспонировать». Либо воспользоваться сочетанием клавиш Alt+Я, М и А.

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

11. Вставить ссылку на оригинальную ячейку

Если вы хотите создать ссылки на оригинальные ячейки, вместо копипэйстинга значений, этот вариант, то, что вам нужно. Воспользуйтесь специальной вставкой, как примерах выше, и вместо кнопки «ОК» , нажмите «Вставить связь». Либо воспользуйтесь сочетанием клавиш Alt+Я, М и Ь, что создаст автоматическую ссылку на скопированный диапазон ячеек.

12. Вставить текст с разбивкой по столбцам

Эта опция полезна, когда вы вставляете данные извне. Например, если вы хотите вставить несколько строчек этого блога на лист Excel, но при этом каждое слово было в отдельном столбце. Для этого копируем текст (Ctrl+C), переходим на лист Excel и вставляем данные (Ctrl+V). У меня, по умолчанию, программа вставила строку с текстом в одну ячейку. Теперь необходимо проделать небольшой финт ушами. Идем во вкладку «Данные» -> «Текст по столбцам» и настраиваем мастер текстов. На первом шаге указываем формат данных – «с разделителями», жмем «Далее», устанавливаем символ-разделитель —  «Пробел» и «Готово». Текст, который, мы вставили в одну ячейку разбился по столбцам. Таким образом мы указали программе, как бы мы хотели воспринимать текстовые данные.

91-paste-text-import-wizard

Теперь, во время последующих вставок текста, кликаем правой кнопкой по ячейке, куда вы хотите вставить текст, выбираем «Специальная вставка» -> «Текст» -> «ОК». Excel разбил нашу строку на столбцы, что нам и требовалось.

92-paste-text-import-wizard

13. Импорт данных из интернета

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

14. Какой ваш любимый способ вставки?

Есть еще много других скрытых способов вставки, таких как вставка XML-данных, изображений, объектов, файлов и т.д. Но мне интересно, какими интересными приемами вставки пользуетесь вы. Напишите, какой ваш любимый способ вставки?

Вам также могут быть интересны следующие статьи

exceltip.ru

Вставляем функцию в Excel - Microsoft Excel для начинающих

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

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

  • СУММ – эта функция суммирует значения всех аргументов.
  • СРЗНАЧ – определяет среднее арифметическое величин, содержащихся в аргументах. Функция вычисляет сумму значений ячеек, а затем делит результат на их количество.
  • СЧЁТ – подсчитывает количество чисел в списке аргументов. Функция полезна для быстрого подсчета числа элементов в диапазоне.
  • МАКС – определяет максимальное значение из списка аргументов.
  • МИН – определяет минимальное значение из списка аргументов.

Как вставить функцию в Excel

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

  1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку C11.Вставка функции в Excel
  2. Введите знак равенства (=) и нужное имя функции. Вы также можете вставить функцию из списка, который появится при вводе ее названия (автозавершение в Excel). В нашем случае мы введем =СРЗНАЧ.Вставка функции в Excel
  3. Введите диапазон ячеек в качестве аргумента в круглых скобках. В нашем примере мы введем (C3:C10). Эта формула суммирует значения в диапазоне С3:С10, а затем делит результат на количество ячеек в этом диапазоне, тем самым определяя среднее значение.Вставка функции в Excel
  4. Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В данном примере средняя цена за единицу заказанных товаров составила $15,93.Вставка функции в Excel

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

Вставка функции с помощью команды Автосумма

Команда Автосумма позволяет автоматически вставлять наиболее распространенные функции в формулы Excel, включая СУММ, СРЗНАЧ, СЧЁТ, МИН и МАКС. В следующем примере мы создадим формулу для расчета полной стоимости недавно заказанных товаров, используя функцию СУММ.

  1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку D12.Вставка функции в Excel
  2. В группе команд Редактирование на вкладке Главная найдите и нажмите стрелку рядом с командой Автосумма, а затем выберите нужную функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.Вставка функции в Excel
  3. Выбранная функция появится в ячейке. Команда Автосумма автоматически определяет диапазон ячеек для аргумента. В данном примере диапазон D3:D11 был выбран автоматически, а значения просуммированы, чтобы вычислить полную стоимость. Вы также можете вручную ввести нужный диапазон.Вставка функции в Excel
  4. Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В нашем примере сумма значений диапазона D3:D11 составляет $606,05.Вставка функции в Excel

В Microsoft Excel команду Автосумма можно также найти на вкладке Формулы.

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

office-guru.ru

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

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

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

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

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

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

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

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

Вставляем данные сразу во все ячейки в Excel

  • Если же это обычный диапазон, т.е. при выделении одной из ячеек этого диапазона группа вкладок Работа с таблицами (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 выделяет текущую область. Второе нажатие, дополнительно к текущей области, выделяет строки с заголовками и итогами (например, в полноценных таблицах). Третье нажатие выделяет весь лист. Думаю, вы догадались, что в некоторых ситуациях вам потребуется всего лишь одно нажатие, чтобы выделить весь лист, а в некоторых – целых три нажатия.

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

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

Вставляем данные сразу во все ячейки в Excel

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

Вставляем данные сразу во все ячейки в Excel

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

Вставляем данные сразу во все ячейки в Excel

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

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

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

Вставляем данные сразу во все ячейки в Excel

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

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

    Вставляем данные сразу во все ячейки в Excel

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

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

Вставляем данные сразу во все ячейки в Excel

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

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

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

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

Вставляем данные сразу во все ячейки в Excel

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

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

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

office-guru.ru