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

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

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

Нужно чтобы эксель выдавал числа от -0,03 до 0,03 в случайном порядке и, желательно, используя только экселевские формулы (с ВБА я не знаком). Я реализовал это след. образом (получилось, к сожалению, только 2 варианта 0,01 и — 0,01 — основной недостаток, желательно вариантов побольше):

в А1 ячейке: =СЛЧИС()
в А2 =ЕСЛИ(А1

16 ответов

Когда использую формулу =СЛЧИС() — excel постоянно обновляет число в ячейке (например изначально сгенерированное число 0.53456, после того как я 2 раза щёлкну в любой свободной ячейке, измениться на 0.9678 , например, и т.д.). Как от этого избавиться? Желательно автоматом — без копирования и т.п.
Использую связь (ole вроде — бы называется) с текстовым документом в word’e — там эти числа встречаются 2 раза в двух разных таблицах, но числа должны быть одинаковыми, а так как =СЛЧИС() постоянно обновляет значения, то одинаковыми они не получаются 🙁

Конечно! Только подробно плз — я в программировании понимаю оч. плохо.

А кроме шуток, возможно ли заставить Excel не обновлять данные. т.е., чтобы значения не пересчитывались.. и какие у этого последствия??
З.Ы.: Насчет кнопки — ждем ответа ТопикСтартера:)

Конечно. Отменить автоматический пересчёт.

А теперь ответ «топикстартеру».

1. Открываешь свой Экселевский файл. Нажимаешь Alt+F11. Попадаешь в редактор Visual Basic for Applications (далее сокращённо ВБА).

2. В меню ВБА выбираешь команду Вставка / Модуль. У тебя откроется окно с чистым модулем.

3. Копируешь туда вот такой код:

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

5. Закрываешь редактор ВБА и возвращаешься в Эксель.

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

7. Нажимаешь на этом объекте правой кнопкой мыши, в появившемся меню выбираешь «Назначить макрос. » Перед тобой откроется окно, где будет список имеющихся макросов из одного пункта GetRandom. Выбираешь его и жмёшь Ок. Всё.

Спасибо огромное — всё работает как надо.

Правда появилось пара ламерских вопросов:
1. как сделать так чтобы этот макрос автоматом запускался 1 раз при открытии книги?
2. Все значения в нескольких ячейках получаются одинаковыми 🙁 мне это не подходит — надо чтобы были разные.

Источник:
http://forum.codenet.ru/q33592/

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

08 января 2007 года

Спасибо огромное — всё работает как надо.

Правда появилось пара ламерских вопросов:

1. как сделать так чтобы этот макрос автоматом запускался 1 раз при открытии книги?

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

Ещё решил попробовать сделать значения рандомных чисел зависимым от значений чисел в других ячейках — как это сделать?

Попробовал написать так —

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E19») > 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F19»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E19») 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F20»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E20») 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F21»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E21») 5 Then

ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«F22»).Value = Rnd * 0.06 — 0.03

If ThisWorkbook.Worksheets(«ÅÃÐÇ»).Range(«E22») =RAND()
=СЛЧИС()

Функция RAND (СЛЧИС) не принимает аргументов.

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

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

  • Если вы не хотите этого, просто скопируйте случайные числа и вставьте их как значения.
  • Выберите ячейку C1 и посмотрите на строку формул. Теперь в этой ячейке прописано значение, а не функция RAND (СЛЧИС).
  • Функция RANDBETWEEN (СЛУЧМЕЖДУ) возвращает случайное целое число между двумя границами.

    1. Выделите ячейку A1.
    2. Введите формулу ниже и нажмите Enter.

    Если вы хотите сгенерировать ряд не целых чисел между 50 и 75, используйте следующую формулу:

    Урок подготовлен для Вас командой сайта office-guru.ru
    Источник: /> Перевел: Антон Андронов

    Правила перепечаткиЕще больше уроков по Microsoft Excel

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

    В разделе Другие языки и технологии на вопрос Как в Excel 2003 «слегка» изменить ряд чисел? (Сейчас поясню) заданный автором Невропатолог лучший ответ это так?
    С2=СЛЧИС ()*((B2*1,014)-(B2*0,986))+(B2*0,986)

    2 ответа

    Привет! Вот подборка тем с ответами на Ваш вопрос: Как в Excel 2003 «слегка» изменить ряд чисел? (Сейчас поясню)

    Ответ от Ноябрь
    Функция СЛУЧМЕЖДУ.. . Попробуй так — =C4*СЛУЧМЕЖДУ (0,002;0,005) — результирующая ячейка. Пусть буде С6, Ячейка С4 с содежимым 0,002 и 0,005 — нижняя и верхняя граница случайного числа. У тебя будет 0,004 и 0,009

    Ответ от силосовать
    Попробуйте =(ячейка*(100-(0,7-(СЛЧИС ()))))/100 например: =(A1*(100-(0,7-(СЛЧИС ()))))/100 P.S.: Проверил, работает. Для изменения диапазона чисел результата, просто измените число «0,7». «СЛЧИС ()» дает случайное число от 0 до 1

    Ответ от . . r. u
    вы там не данные от скважин обрабатываете?

    2 ответа

    Привет! Вот еще темы с нужными ответами:

    • Функция СЛЧИС
    • Функция СЛУЧМЕЖДУ

    Если речь идёт о генерации случайных чисел, Excel предлагает две полезные функции: RAND (СЛЧИС) и RANDBETWEEN (СЛУЧМЕЖДУ).

    Функция RAND (СЛЧИС) генерирует случайное десятичное число между 0 и 1.

    1. Выделите ячейку A1.
    2. Введите следующую формулу и нажмите Enter.

    Функция RAND (СЛЧИС) не принимает аргументов.

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

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

  • Если вы не хотите этого, просто скопируйте случайные числа и вставьте их как значения.
  • Выберите ячейку C1 и посмотрите на строку формул. Теперь в этой ячейке прописано значение, а не функция RAND (СЛЧИС).
  • Функция RANDBETWEEN (СЛУЧМЕЖДУ) возвращает случайное целое число между двумя границами.

    1. Выделите ячейку A1.
    2. Введите формулу ниже и нажмите Enter.

    Если вы хотите сгенерировать ряд не целых чисел между 50 и 75, используйте следующую формулу:

    Урок подготовлен для Вас командой сайта office-guru.ru
    Источник: /> Перевел: Антон Андронов

    Правила перепечаткиЕще больше уроков по Microsoft Excel

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

    Источник:
    http://word-office.ru/formula-v-excel-sluchmezhdu-kak-sdelat-tak-chtoby-perestali-izmenyat-sya.html

    Генератор случайных чисел в Excel

    В данной статье мы рассмотрим особенности алгоритма генератора случайных чисел в Excel, и на примерах рассмотрим, как использовать функции СЛЧИС и СЛУЧМЕЖДУ в Excel для генерации случайных чисел, случайных чисел с заданным количеством знаков после запятой, дат и времени.

    Генератор случайных чисел с использованием функции СЛЧИС

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

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

    Функция СЛЧИС доступна во всех версиях: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

    Поскольку функция Excel СЛЧИС не имеет аргументов, вы просто вводите =СЛЧИС() в ячейке и затем копируете формулу на столько ячеек, сколько хотите:

    Генератор случайных чисел в Excel – Генерация случайных чисел

    А теперь давайте сделаем еще один шаг и напишем несколько формул СЛЧИС для генерации случайных чисел в соответствии с определенными условиями.

    Генератор случайных чисел от нуля до заданной верхней границы диапазона

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

    Например, для создания последовательности случайных чисел, больших или равных 0, но менее 50, используйте следующую формулу:

    Примечание . Значение верхней границы никогда не включается в возвращаемую случайную последовательность. Например, если вы хотите получить случайные числа от 0 до 10, включая 10, правильная формула =СЛЧИС()*11.

    Генератор случайных чисел в диапазоне

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

    Где A – это нижнее значение границы (наименьшее число), а B – верхнее значение границы (наибольшее число).

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

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

    Генератор случайных целых чисел в Excel

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

    Чтобы сделать генератор случайных целых чисел от 0 до 50:

    Чтобы генерировать случайные целые числа от 10 до 50:

    Генератор случайных чисел в Excel – Генерация случайных целых чисел

    Генератор случайных чисел в Excel в диапазоне с помощью функции СЛУЧМЕЖДУ

    СЛУЧМЕЖДУ – это еще одна функция в Excel для создания генератора случайных чисел.. Она возвращает случайные целые числа в указанном диапазоне:

    СЛУЧМЕЖДУ (нижняя граница; верхняя граница)

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

    Подобно СЛЧИС, СЛУЧМЕЖДУ в Excel является изменчивой функцией, и она также возвращает новое случайное целое число каждый раз, когда ваша таблица пересчитывается или изменяется.

    Например, того чтобы сделать генератор случайных целых чисел от 10 до 50 (включая 10 и 50) используйте следующую формулу СЛУЧМЕЖДУ:

    Генератор случайных чисел в Excel – Генерация случайных чисел в заданном диапазоне

    Функция СЛУЧМЕЖДУ в Excel может создавать как положительные, так и отрицательные числа случайные числа. Например, чтобы получить список случайных чисел от -10 до 10, введите следующую формулу на листе:

    Функция СЛУЧМЕЖДУ доступна в следующих версиях: Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

    В более ранней версии Excel 2003, вы можете использовать формулу СЛЧИС , рассмотренную выше.

    Далее в этой статье вы найдете еще несколько примеров формул, демонстрирующих, как использовать функцию СЛУЧМЕЖДУ для создания генератора случайных чисел, отличных от целых.

    Создание случайных чисел с заданным количеством знаков после запятой

    Хотя функция СЛУЧМЕЖДУ в Excel была предназначена для генерации случайных целых чисел, вы можете использовать ее для генерации случайных десятичных чисел с таким количеством десятичных знаков, сколько хотите.

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

    = СЛУЧМЕЖДУ(нижняя граница*10; верхняя граница*10)/10

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

    Следующая формула СЛУЧМЕЖДУ возвращает случайные десятичные числа от 1 до 50:

    Генератор случайных чисел в Excel – Генерация случайных чисел с одним знаком после запятой

    Аналогичным образом, чтобы сделать генератор случайных чисел от 1 до 50 с двумя знаками после запятой, вы умножаете аргументы функции СЛУЧМЕЖДУ на 100, а затем делите результат на 100:

    Генератор случайных чисел в Excel – Генерация случайных чисел с двумя знаками после запятой

    Генератор случайных дат в Excel

    Чтобы вернуть список случайных дат между данными двумя датами, используйте функцию СЛУЧМЕЖДУ в сочетании с ДАТА:

    =СЛУЧМЕЖДУ (ДАТА (дата начала); ДАТА (дата окончания))

    Например, чтобы получить список дат между 1 сентября 2017 и 20 ноября 2017 включительно, введите следующую формулу на листе:

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

    Генератор случайных чисел в Excel – Генерация случайных дат

    Генератор случайного времени в Excel

    Во внутренней системе Excel времена хранятся как десятичные числа, и вы можете использовать стандартную функцию Excel СЛЧИС для вставки случайных действительных чисел, а затем просто применить формат времени к ячейкам:

    Генератор случайных чисел в Excel – Генерация случайного времени функцией СЛЧИС и применение к ней формата Время

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

    Генератор случайного времени в указанном диапазоне

    Чтобы вставить произвольное время между любыми двумя указанными вами временными интервалами, используйте функцию ВРЕМЯ в сочетании с Excel СЛЧИС:

    = ВРЕМЯ (время начала) + СЛЧИС () * (ВРЕМЯ (время начала) — ВРЕМЯ (время окончания))

    Например, чтобы вставить случайное время между 5:30 и 18:00, вы можете использовать одну из следующих формул:

    Генератор случайных чисел в Excel – Генерация случайного времени в заданном интервале

    Генератор случайных букв в Excel

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

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

    Разберем функции, в приведенной выше формуле:

    • КОДСИМВ возвращает числовые коды ANSI для указанных букв.
    • СЛУЧМЕЖДУ принимает числа, возвращаемые функциями КОДСИМВ , как нижнее и верхнее значения диапазона.
    • СИМВОЛ преобразует случайные коды ANSI, возвращаемые СЛУЧМЕЖДУ, в соответствующие буквы.

    Генератор случайных чисел в Excel – Генерация случайных букв

    Так как коды ANSI отличаются для прописных и строчных букв, эта формула учитывает регистр.

    Если кто-то наизусть знает Коды символов ANSI, ничто не мешает вам передавать коды непосредственно в функцию СЛУЧМЕЖДУ.

    Например, чтобы получить произвольные прописные буквы между A (код ANSI 65) и Z (код ANSI 90), вы пишете:

    Чтобы генерировать строчные буквы между а (код ANSI 97) в z (код ANSI 122), вы используете следующую формулу:

    Чтобы вставить случайный специальный символ, например! «# $% & ‘() * +, -. /, используйте функцию СЛУЧМЕЖДУ с нижним параметром, установленным на 33 (код ANSI для«! »), а верхний параметр — 47 (код ANSI для« / »).

    Генератор случайных чисел в Excel – Генерация случайных символов

    Как предотвратить повторное вычисление СЛЧИС и СЛУЧМЕЖДУ

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

    1. Чтобы остановить функции СЛЧИС или СЛУЧМЕЖДУ от пересчета в одной ячейке , выберите эту ячейку, переключитесь на панель формул и нажмите F9 , чтобы заменить формулу на ее значение.
    2. Чтобы предотвратить функцию случайных чисел в Excel от автоматического обновления значений в нескольких ячейках , используйте функцию Вставить. Выберите все ячейки с формулой генерации случайных значений, нажмите Ctrl+C, чтобы скопировать их, затем щелкните правой кнопкой мыши выбранный диапазон и нажмите « Вставить специальные »—> « Значения ».

    Генератор случайных чисел в Excel – Вставка значений

    Генератор случайных чисел с помощью Анализа данных

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

    Пример генерации случайных чисел нормального распределения

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

    Генератор случайных чисел в Excel – Анализ данных

    В открывшемся списке выбираем «Генерация случайных чисел» и нажимаем кнопку « ОК ».

    Генератор случайных чисел в Excel – Генерация случайных чисел

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

    Генератор случайных чисел в Excel – Генерация случайных чисел нормального распределения

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

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

    Источник:
    http://naprimerax.org/posts/63/generator-sluchainykh-chisel-v-excel

    Создаем генератор случайных чисел в Excel

    Доброго времени суток, уважаемый, читатель!

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

    Итак, для чего же собственно мы можем использовать этом механизм:

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

    …… да и во многих других ситуациях!

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

    Создаём генератор случайных чисел с помощью функции СЛЧИС

    С помощью функции СЛЧИС, мы имеем возможность генерировать любое случайное число в диапазоне от 0 до 1 и эта функция будет выглядеть так:

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

    =СЛЧИС()*100; А вот если вам не нравятся дробные числа или просто нужно использовать целые числа, тогда используйте такую комбинацию функций, это позволит вам отсечь значения после запятой или просто отбросить их:

    =ОТБР((СЛЧИС()*100);0) Когда возникает необходимость использовать генератор случайных чисел в каком-то определённом, конкретном диапазоне, согласно нашим условиям, к примеру, от 1 до 6 надо использовать следующую конструкцию (обязательно закрепите ячейки с помощью абсолютных ссылок):

    • a – представляет нижнюю границу,
    • b – верхний предел

    и полная формула будет выглядеть: =СЛЧИС()*(6-1)+1, а без дробных частей вам нужно написать: =ОТБР(СЛЧИС()*(6-1)+1;0)

    Создаём генератор случайных чисел с помощью функции СЛУЧМЕЖДУ

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

    Создаём генератор с помощью надстройки AnalysisToolPack

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

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

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

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

    Не забудьте поблагодарить автора!

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

    Источник:
    http://topexcel.ru/sozdaem-generator-sluchajnyx-chisel-v-excel/

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

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

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

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

    Допустим в ячейке у Вас формула

    если Вы растяните ее вниз (или скопируете и вставите вниз — в следующую ячейку)

    то формула преобразуется и станет вот такой:

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

    Сделайте так и ячейка с «долларами» (то есть $B$2 в нашем случае) не будет меняться.

    Значки доллара ($) можно проставить как вручную введя их с клавиатуры, так и через F4.

    Использовать F4 надо так: мышкой выделяете (в формуле) нужный адрес (в нашем примере — это B2) и нажимаете 1 раз F4 — сразу и перед буквой и перед цифрой появится значок доллара $ (он, кстати, в Excel означает, т.н. абсолютный адрес — то есть такой адрес, который не будет изменяться при копировании формулы)

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

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

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

    Первый самый простой.

    Пример. Вот данные, которые получены в столбце «D» суммированием данных из столбцов «В» и «С», разделив результат на данные из столбца «J», чтобы преобразовать в евро.

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

    1) И вот какое решение существует — преобразовать относительные ссылки в абсолютные.

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

    2) Тогда можно попробовать другой способ — «дезактивировать» формулы, то есть сделать так, чтобы в документе «Excel» не воспринимались формулы — «формулами», а воспринимались, словно это обычный текст.

    Тогда знак «=» на какое-то время следует заменить любым другим символом, например, решеткой — «#» или парой амперсандов — «&&».

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

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

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

    Дальше надо скопировать диапазон из Exel в блокнот —

    4) Четвертый способ подходит тем, кто постоянно выполняет копирование, перенося каждый раз. Можно воспользоваться макросом — здесь все проще всего, все задано заранее, надо только создать макрос.

    Чтобы работать с макросами, надо перейти на вкладку «Разработчик» или применить сочетание горячих клавишь Alt+F8.

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

    В авторском ролике Николай Павлов, специалист по Exel, расскажет еще более подробно и наглядно.

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

    Самое простейшее — сделать так, чтобы «Excel» вместо формулы видел текст. Для этого знак «=» надо заменить любым знаком или символом. Например, на «$» или «#». Чтобы это сделать, используют горячие клавиши. Выделили диапазон, нажали сочетание горячих клавиш, чтобы вызвать окно «найти и заменить», там вместо «=» подставили другой символ. Сделали копирование, а потом точно так же обратно вместо символа вернули знак равенства.

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

    Другой вариант — изменить формулу таким образом, чтобы «Excel» воспринимал формулу не как формулу, а как обычный текст. Для этого надо изменить знак равенства на другой. Горячими клавишами открывается окно из вкладки «Главной» — «Найти и выделить» и там смотрим «Заменить» — вместо «равно» — любой знак, ту же «решетку». Конечно, потом все придется поменять обратно, чтобы формулы были формулами.

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

    Источник:
    http://www.bolshoyvopros.ru/questions/1371527-excel-kak-sdelat-chtoby-jachejki-v-formule-ne-menjalis-pri-kopirovanii.html

    Проблемы с формулами в таблице Excel

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

    Решение 1: меняем формат ячеек

    Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.

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

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

    Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:

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

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

    1. Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.
    2. В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.

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

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

    Решение 2: отключаем режим “Показать формулы”

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

    1. Переключаемся во вкладку “Формулы”. В группе инструментов “Зависимость формул” щелкаем по кнопке “Показать формулы”, если она активна.
    2. В результате, в ячейках с формулами теперь будут отображаться результаты вычислений. Правда, из-за этого могут измениться границы столбцов, но это поправимо.

    Решение 3: активируем автоматический пересчет формул

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

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

    Решение 4: исправляем ошибки в формуле

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

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

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

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

    Распространенные ошибки

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

    • #ДЕЛ/0! – результат деления на ноль;
    • #Н/Д – ввод недопустимых значений;
    • #ЧИСЛО! – неверное числовое значение;
    • #ЗНАЧ! – используется неправильный вид аргумента в функции;
    • #ПУСТО! – неверно указан адрес дапазона;
    • #ССЫЛКА! – ячейка, на которую ссылалась формула, удалена;
    • #ИМЯ? – некорректное имя в формуле.

    Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, все ли данные в ячейках, участвующих в формуле, заполнены корректно. Затем проверяем саму формулу и наличие в ней ошибок, в том числе тех, которые противоречат законам математики. Например, не допускается деление на ноль (ошибка #ДЕЛ/0!).

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

    1. Отмечаем ячейку, содержащую ошибку. Во вкладке “Формулы” в группе инструментов “Зависимости формул” жмем кнопку “Вычислить формулу”.
    2. В открывшемся окне будет отображаться пошаговая информация по расчету. Для этого нажимаем кнопку “Вычислить” (каждое нажатие осуществляет переход к следующему шагу).
    3. Таким образом, можно отследить каждый шаг, найти ошибку и устранить ее.

    Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.

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

    Заключение

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

    Источник:
    http://microexcel.ru/problemy-s-formulami/