Как сделать чтобы в эксель менялись числа
Сформируем последовательность 1, 2, 3, . Пусть в ячейке A2 введен первый элемент последовательности - значение 1 . В ячейку А3 , вводим формулу =А2+1 и копируем ее в ячейки ниже (см. файл примера ).
Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки , то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1 , затем в =А4+1 и т.д., тем самым формируя числовую последовательность 2, 3, 4, .
Если последовательность нужно сформировать в строке, то формулу нужно вводить в ячейку B2 и копировать ее нужно не вниз, а вправо.
Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7, . необходимо изменить формулу в ячейке А3 на =А2+2 . Чтобы сформировать последовательность 100, 200, 300, . необходимо изменить формулу на =А2+100 , а в ячейку А2 ввести 100.
Другим вариантом создания последовательности 1, 2, 3, . является использование формулы =СТРОКА()-СТРОКА($A$1) (если первый элемент последовательности располагается в строке 2 ). Формула =СТРОКА(A2)-СТРОКА($A$1) позволяет создать вертикальную последовательность, в случае если ее первый элемент последовательности располагается в любой строке. Тот же результат дают формулы =ЧСТРОК($A$1:A1) , =СТРОКА(A1) и =СТРОКА(H1) . Формула =СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1) создает последовательность, размещенную горизонтально. Тот же результат дают формулы =ЧИСЛСТОЛБ($A$1:A1) , =СТОЛБЕЦ(A1) .
Чтобы сформировать последовательность I, II, III, IV , . начиная с ячейки А2 , введем в А2 формулу =РИМСКОЕ(СТРОКА()-СТРОКА($A$1))
Сформированная последовательность, строго говоря, не является числовой, т.к. функция РИМСКОЕ() возвращает текст. Таким образом, сложить, например, числа I+IV в прямую не получится.
Другим видом числовой последовательности в текстовом формате является, например, последовательность вида 00-01 , 00-02, . Чтобы начать нумерованный список с кода 00-01 , введите формулу =ТЕКСТ(СТРОКА(A1);"00-00") в первую ячейку диапазона и перетащите маркер заполнения в конец диапазона.
Выше были приведены примеры арифметических последовательностей. Некоторые другие виды последовательностей можно также сформировать формулами. Например, последовательность n2+1 ((n в степени 2) +1) создадим формулой =(СТРОКА()-СТРОКА($A$1))^2+1 начиная с ячейки А2 .
Создадим последовательность с повторами вида 1, 1, 1, 2, 2, 2. Это можно сделать формулой =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1) . С помощью формулы =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2 получим последовательность 2, 2, 2, 2, 4, 4, 4, 4. , т.е. последовательность из четных чисел. Формула =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1 даст последовательность 1, 1, 1, 1, 3, 3, 3, 3, .
Примечание . Для выделения повторов использовано Условное форматирование .
Формула =ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1 даст последовательность 1, 2, 3, 4, 1, 2, 3, 4, . Это пример последовательности с периодически повторяющимися элементами.
Используем клавишу CTRL
Пусть, как и в предыдущем примере, в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая клавишу CTRL , скопируем Маркером заполнения (при этом над курсором появится маленький плюсик), значение из A 2 в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …
ВНИМАНИЕ! Если на листе часть строк скрыта с помощью фильтра , то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL , выделите любую ячейку с заголовком фильтра и дважды нажмите CTRL + SHIFT + L (сбросьте фильтр).
Используем правую клавишу мыши
Пусть в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши появится контекстное меню, в котором нужно выбрать пункт Заполнить . Получим последовательность чисел 1, 2, 3, 4 …
Используем начало последовательности
Если начало последовательности уже задано (т.е. задан первый элемент и шаг последовательности), то создать последовательность 1, 2, 3, . можно следующим образом:
Получаем результат как в предыдущем случае. Если в ячейке А3 введено значение 3 , т.е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.
Создадим последовательность вида 1, 2, 3, 1, 2, 3. для этого введем в первые три ячейки значения 1, 2, 3, затем маркером заполнения , удерживая клавишу CTRL , скопируем значения вниз.
Использование инструмента Прогрессия
Воспользуемся стандартным средством EXCEL для создания прогрессий, в то числе и арифметических.
- вводим в ячейку А2 значение 1 ;
- выделяем диапазон A2:А6 , в котором будут содержаться элементы последовательности;
- вызываем инструмент Прогрессия ( Главная/ Редактирование/ Заполнить/ Прогрессия. ), в появившемся окне нажимаем ОК.
Использование в работе : Подходы для создания числовых последовательностей можно использовать для нумерации строк , сортировки списка с числами , разнесения значений по столбцам и строкам .
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О последовательностях значений в формате дат (и времени) вида 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . можно прочитать в статье Последовательности дат и времен . О массивах значений, содержащих последовательности конечной длины, используемых в формулах массива , читайте в статье Массив значений (или константа массива или массив констант) .
При введите номер, содержащий более 15 цифр в ячейке в Microsoft Excel, Excel любые цифры за пятнадцатым местом на нули. Например, введите номер удостоверения кредитной карты в следующем формате:
В этой ситуации Excel последнюю цифру на ноль.
Причина
Excel спецификации IEEE 754 для хранения и вычисления номеров с плавающей точкой. Excel сохраняет только 15 значимых цифр в номере и изменяет цифры после пятнадцатого места в ноль
Обходной путь
Добавление кавычка
Чтобы не изменить цифры на ноль, введите одну кавычка перед вводом номера.
Для этого выберите пустую ячейку, введите одну кавычка ('), а затем введите номер. Все цифры отображаются в ячейке.
Формат ячеек
Чтобы не вводить кавычка в каждой затронутой ячейке, перед вводом каких-либо данных можно форматирование ячеек в виде текста.
Выберите все затронутые ячейки и нажмите кнопку Ctrl+1, чтобы открыть диалоговое окно Format Cells.
На вкладке Номер выберите Текст из списка Категорий и выберите ОК.
Дополнительные сведения
Такое поведение происходит только в том случае, если ячейка отформатирована как Номер, а число, в которое вошел, превышает 15 цифр. Для ячеек, форматированные в виде текста, можно ввести до 32 767 символов. Excel на таблице отображается до 1024 символов.
Так как настраиваемые форматы номеров предназначены для работы в основном с числами, невозможно создать настраиваемый формат номеров, который хранит более 15 цифр. Например, нельзя использовать следующий формат для хранения 16-символьного удостоверения кредитной карты в виде номера:
Однако, если ввести номер в ячейке, которая форматирована как текст, все символы остаются как вы введите их, так как Excel сохраняет номер в виде текста, а не как номер.
Когда вы введите дату или время в ячейку, она отображается в формате даты и времени по умолчанию. Этот формат по умолчанию основан на региональных параметрах даты и времени, заданных на панели управления, и изменяется при их настройке на панели управления. Числа можно отобразить в нескольких других форматах даты и времени, на большинство из которых параметры панели управления не влияют.
В этой статье
Отображение чисел в качестве даты или времени
Вы можете форматирование даты и времени по мере их ввести. Например, если ввести в ячейку 2/2, Excel будет автоматически интерпретирован как дата и отобразит в ячейке 02.фев. Если это не то, что вам нужно (например, если вы хотите, чтобы в ячейке были 2 февраля 2009 г. или 02.02.09), в диалоговом окне Формат ячеек можно выбрать другой формат даты, как покажем в следующей процедуре. Аналогично, если ввести в ячейку 9:30 a или 9:30 p, Excel интерпретирует это как время и отображает 9:30 или 21:30. Вы также можете настроить способ времени в диалоговом окне Формат ячеек.
На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
В списке Категория выберите дата иливремя.
В списке Тип выберите нужный формат даты или времени.
Примечание: Форматы даты и времени, которые начинаются со звездочки (*), отвечают на изменения в региональных параметрах даты и времени, заданных на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
Чтобы отобразить даты и время в формате других языков, выберите нужный язык в поле Языковой стандарт (расположение).
Число в активной ячейке выбранного на этом сайте отображается в поле Образец, чтобы можно было просмотреть выбранные параметры форматирования.
Создание пользовательского формата даты или времени
На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
В поле Категория выберите дата или время ,а затем выберите числовом формате, наиболее близком по стилю к тому, который вы хотите создать. (При создании пользовательских числных форматов проще начать с существующего, чем с нуля.)
В списке Категория выберите пункт (все форматы). В поле Тип вы увидите код формата, совпадающий с форматом даты или времени, выбранным на шаге 3. Встроенный формат даты или времени нельзя изменить или удалить, поэтому не беспокойтесь о переописи.
В поле Тип введите необходимые изменения формата. Вы можете использовать любой из кодов в следующих таблицах:
Дни, месяцы и годы
Для отображения
Используйте код
Месяцев в виде чисел от 1 до 12
Месяцев в виде чисел от 01 до 12
Месяцев в виде "янв", . "дек"
Месяцев в виде "январь", . "декабрь"
Месяцев в виде первой буквы месяца
Дней в виде чисел от 1 до 31
Дней в виде чисел от 01 до 31
Дней в виде "Пн", . "Вс"
Дней в виде "понедельник", . "воскресенье"
Лет в виде чисел от 00 до 99
Лет в виде чисел от 1900 до 9999
Если вы используете "м" сразу после кода "ч" или "чч" или непосредственно перед кодом "сс", Excel отображает минуты вместо месяца.
Часы, минуты и секунды
Для отображения
Используйте код
Часы в качестве 0–23
Часы в качестве 00–23
Минуты в качестве 0–59
Минуты в качестве 00–59
Секунды в качестве 0–59
Секунды в качестве 00–59
Часы с 04:00 до 04:0
Время в 4:36:03 P
Заслон времени в часах; например, 25,02
Заслон времени в минутах; например, 63:46
За считанные секунды
AM и PM Если формат содержит am или PM, часы основаны на 12-часовом формате, где "AM" или "A" указывает время от полуночи до полудня, а "PM" или "P" — время от полудня до полуночи. В противном случае используется 24-часовой цикл. Код "м" или "мм" должен отображаться сразу после кода "ч" или "чч" или непосредственно перед кодом "сс"; в противном Excel отображается месяц, а не минуты.
Создавать пользовательские числовые форматы может быть непросто, если вы этого еще не сделали. Дополнительные сведения о создании пользовательских числных форматов см. в теме Создание и удаление пользовательских числов.
Советы для отображения дат и времени
При попытке отменить формат даты или времени с помощью выбора в списке Категория общего Excel отображает числовом коде. При повторном вводе даты или времени Excel формат даты или времени по умолчанию. Чтобы ввести определенный формат даты или времени, например январь 2010г., можно отформать его как текст, выбрав текст в списке Категория.
Чтобы быстро ввести текущую дату, выйдите из любой пустой ячейки и нажмите CTRL+; (точка с за semicolon) и при необходимости нажмите ввод. Чтобы вставить дату, которая будет обновляться до текущей даты при каждом повторном повторном пересчете или пересчете формулы, введите =СЕГОДНЯ() в пустую ячейку и нажмите ввод.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:
Причем иногда такой индикатор не появляется (что гораздо хуже).
В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:
- перестает нормально работать сортировка - "псевдочисла" выдавливаются вниз, а не располагаются по-порядку как положено:
- функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
- при фильтрации псевдочисла отбираются ошибочно
- многие другие функции Excel также перестают нормально работать:
- и т.д.
Особенно забавно, что естественное желание просто изменить формат ячейки на числовой - не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК - и ничего не происходит! Совсем!
Способ 1. Зеленый уголок-индикатор
Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :
Все числа в выделенном диапазоне будут преобразованы в полноценные.
Если зеленых уголков нет совсем, то проверьте - не выключены ли они в настройках вашего Excel (Файл - Параметры - Формулы - Числа, отформатированные как текст или с предшествующим апострофом).
Способ 2. Повторный ввод
Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.
Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.
Способ 3. Формула
Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:
Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.
Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.
Способ 4. Специальная вставка
Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:
- в любую пустую ячейку введите 1
- скопируйте ее
- выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
- щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
- в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)
По-сути, мы выполняем то же самое, что и в прошлом способе - умножение содержимого ячеек на единицу - но не формулами, а напрямую из буфера.
Способ 5. Текст по столбцам
Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.
Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:
После нажатия на Готово Excel преобразует наш текст в нормальные числа.
Способ 6. Макрос
Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик - Макросы (Developer - Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) - и моментально преобразовать псевдочисла в полноценные.
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel'ем как текст, поэтому не будет работать группировка и сортировка. Решения - те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Читайте также: