Методы работы в excel «с нуля» для новичиков

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

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

1. Выделяем ячейки, где надо вставить значения с нулями. Затем переходим во вкладку «Главная» и изменяем формат ячейки на «Текстовый».

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

Отметим, что изменять формат на текстовый, в котором программе не удаляет нуль перед числом в Excel, можно и при помощи другого варианта. Выделяем требуемые ячейки в Экселе и нажимаем правую кнопку мыши (ПКМ). Далее в открывшемся контекстном меню кликаем по «Формат ячеек».

Выбираем формат ячеек

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

Меняем формат

Вас также может заинтересовать

172 комментария к «Как заполнить пустые ячейки 0 со значением выше/ниже в Excel»

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

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

Здравствуйте, сэр/мэм, Пожалуйста, помогите мне, как заполнить все пробелы в нескольких выбранных строках нулем (0).

Привет! Обратите внимание на следующий абзац статьи выше — Заполните пустые ячейки 0 или другим конкретным значением. Отличное решение, спасибо, что поделились им! Сэкономлено довольно много времени

Отличное решение, спасибо, что поделились им! Сэкономлено довольно много времени

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

Здравствуйте, команда Ablebits!

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

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

Для заполнения пустых ячеек данными рекомендую обратить внимание на инструмент «Заполнить пустые ячейки». Он доступен как часть нашего Ultimate Suite for Excel, который вы можете установить в пробном режиме и бесплатно проверить, как он работает

Привет, У меня есть данные, в которых я хочу заполнить текстом последней строки в верхних строках. Я хочу заполнить 320304 во всех верхних строках.

55 215,24 0,23 237,24 756 7 745,72 841,02 1 806,26 307,32 0,04 Товары Квитанция 320304

Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

Проблема: кто-то настроил данные в представлении структуры. Мне нужно отсортировать по столбцам A, B и C, поэтому мне нужно заполнить все пробелы.

1. Заполните пропуски.

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

1. Выберите от A3 до последнего пробела в столбце C.
2. Выберите «Главная», «Найти и выбрать», «Перейти к специальному».
3. В диалоговом окне «Перейти к специальному» выберите «Пробелы» и нажмите «ОК».
4. Введите знак равенства и нажмите стрелку вверх. Это создаст формулу, указывающую на одну ячейку вверх.

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

1. Прежде чем вы сможете сортировать, вам нужно преобразовать формулы в значения. Вставить значения не работает для несмежных выделенных фрагментов, поэтому вам придется повторно выбрать столбцы A:C
2. Ctrl+C для копирования
3. Главная страница, раскрывающийся список «Вставить», «Вставить значения», чтобы преобразовать формулы в значения.

Дополнительные ресурсы для Microsoft Excel

ExcelArticles.com предоставляет примеры формул, функций и процедур Visual Basic только для иллюстрации, без явных или подразумеваемых гарантий, включая, помимо прочего, подразумеваемые гарантии товарного состояния и/или пригодности для определенной цели. Формулы, функции и процедуры Visual Basic на этом веб-сайте предоставляются «как есть», и мы не гарантируем, что их можно использовать во всех ситуациях.

Excel является зарегистрированным товарным знаком корпорации Microsoft. MrExcel является зарегистрированным товарным знаком Tickling Keys, Inc.

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

Как показано ниже:

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

И это легко исправить!

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

Это можно легко сделать, используя простой прием специального диалогового окна Go-To, VBA или Power Query.

Итак, приступим!

Это руководство охватывает:

Using Find and Replace to Replace Blank Cells with 0 in Google Sheets

The second technique to replace blank cells with 0 in Google Sheets is using the Find and Replace feature of Google Sheets.

There are slight variations of this method depending on whether your data is numeric (numbers only) or alphanumeric (text and numbers). This can be especially helpful if you’re building your own financial templates (including planning for IRA contributions).

This is because this method involves the use of regular expressions, which only work with text (or alphanumeric) data. We will look at both cases in the following sections.

How to Replace Blank Cells with 0 when you have a Text (or Mixed) Column

Consider the following data list where you have both text and numerical data, but they are interspersed with blank cells:

We want to fill these blank cells with 0’s. We can use the Find and Replace feature to accomplish this. The Find and Replace dialog box in Google Sheets looks like this:

Normally in Excel, we could simply leave the Find field blank and have Excel replace all blanks with 0. However, unlike Excel, Google Sheets does not allow you to leave the Find field empty. As such, you need to use a regular expression to express a blank space.

In Google sheets, you can use the “^s*$” regular expression to indicate a blank value. This expression means ‘a cell containing only whitespace, and it can have any number of white spaces’.

Now let us see step by step how to Find and Replace blank cells in Google Sheets with 0:

• Select the range of cells that you want to work with (cells A2:A13 in this case).
• From the Edit menu, select Find and Replace. Alternatively, you can just use the CTRL+H keyboard shortcut.
• Enter the regular expression “^s*$” in the input box next to ‘Find’.
• Enter the value 0 in the input box next to ‘Replace with’.
• Check the boxes next to ‘Match case’, and ‘Search using regular expressions’.

Click on the Replace all button.

Click Done.

All the blank cells in your selected data range should now get populated with the value 0.

How to Replace Blank Cells with 0 when you have a Numeric Column

As mentioned before, Google Sheets lets you use regular expressions with only text data. So, if you have a numeric dataset, you will need to first convert it to text before you can apply the Find and Replace method to it.

So, let us look at the steps to replace blank cells in a numeric dataset with 0 in Google Sheets. To demonstrate, we will consider the following dataset:

Here are the steps:

• Select the range of cells that you want to work with (cells A2:A7 in this case).
• From the Format menu, navigate to Number->Plain text. This converts all the numbers in the data range to plain text.

• From the Edit menu, select Find and Replace. Or use the CTRL+H shortcut.
• Enter the regular expression “^s*$” in the input box next to ‘Find’.
• Enter the value 0 in the input box next to ‘Replace with’.
• Check the box next to ‘Search using regular expressions’.

Click on the Replace all button.

You should now see all blank cells replaced with 0.

Convert the data back to numeric data by navigating to Format->Number->Automatic.

Способ 4: изменяем длину строки

Минус способа с использованием функции ТЕКСТ является то, что она не способна изменять формат чисел на текст тогда, когда эти числа находятся в комбинации с текстом. Она просто не будет работать.

Но в такой ситуации можно использовать метод с функциями ПОВТОР и ДЛСТР.

Формула:

При вызове такой функции, длина строки изменится на пятизначную. Это значит, что все числа будут записаны в пятизначном формате (например, число «3» будет записано как «00003»).

Что делает эта функция:

• Функция ДЛСТР(A2) возвращает длину нашей строки с числом.
• Функция =ПОВТОР(0;5-ДЛСТР(A2)) считает сколько нулей необходимо добавить к началу нашего числа. И так для каждой новой строки.

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

Особенности проставления нулей в Excel

1

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

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

2. Формулы: В Excel можно использовать формулы для автоматического проставления нулей в нужных ячейках. Например, если необходимо проставить ноль во всех ячейках столбца A, можно использовать следующую формулу: =IF(A1=»»;0;A1). Эта формула проверяет, пустая ли ячейка A1, и если да, то проставляет ноль, в противном случае оставляет значение ячейки без изменений.

3. Заполнение последовательности нулями: Есть несколько способов заполнить последовательность ячеек нулями. Один из способов — использовать функцию «Заполнить» — «Серия». В этом случае нужно выбрать диапазон ячеек, которые нужно заполнить нулями, и в окне «Серия» выбрать опцию «Step value» и ввести значение 0.

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

5. Макросы: Для более сложных задач можно использовать макросы в Excel. Например, можно написать макрос, который будет проставлять нули в заданных ячейках или проставлять нули в определенных условиях. Для этого нужно открыть редактор VBA (нажать ALT + F11), создать новый модуль и написать нужный макрос.

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

Способ 2: Ручное создание таблицы

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

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

2. Заполните данные каждого столбца в соответствии с имеющейся на руках информацией.

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

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

5. Найдите там подходящий тип границы. Чаще всего используется вариант «Все границы».

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

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

8. В таблице есть цена и количество, а значит, эти значения нужно перемножить, чтобы получить итог. Данная формула записывается как =B2*C2 (названия ячеек меняются в соответствии с требованиями). 

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

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

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

как заменить пустые ячейки на ноль

​ формулы, так и​​ заполнили все пустые​ выделенной ячейке содержится​ это сделать –​(Выделить группу ячеек)​ способ, как выделить​ при создании сводной​ открытии файла ТС​ Копировать.​Little​ проще​ ней пробел, нажмите​ или добавлении/удалении строк​ в несколько движений.​ заглядывали? Какие процессы?​Serge 007​ и нажимаем ОК​ при помощи инструмента​

​ ячейки нужным значением.​​ ссылка на ячейку,​ читайте далее.​ позволяет выбрать ячейки​ пустые ячейки:​​ таблицы. В таком​​ нет ярлыков листов,​Выделить диапазон D3:G5​​: Ребят,Всем спасибо,конечно,я пустоту​​Перестраховываюсь на тот​​Ctrl+H​​ корректность формул может​Выделяем диапазон ячеек в​ Что жрёт оперативку?​​: Выделите диапазон (а​​ (все пустые ячейки​​Find & Replace​​Выделите диапазон, содержащий пустые​ расположенную над ней.​​Выделите все пустые ячейки.​​ определённого типа, например,​Выделите столбцы или строки,​ случае Вам придётся​ даже после постановки​​ — Вставить Специальная​ копировала,а не пробел,ВСЕ​ случай, если пробелы​​, вставьте его в​ быть нарушена. Выделите​

​ первом столбце, который​​ Сколько занимает Excel?​​ не весь лист)​​ в диапазоне​(Найти и заменить).​ ячейки.​

​Замечание:​​Нажмите​ ячейки, содержащие формулы,​

​A2:A14​​Урок подготовлен для Вас​

​Нажмите​​Не забывайте, что​F2​ примечания, константы, пустые​

​ заполнить пустоты.​​ ячейки. Существуют разные​ в Параметрах?​ Ок.​Risovod​

​ всего листа, а​​Найти:​ первом столбце, скопируйте​

​ нашем примере, это​​: Спасибо Serge 007​ пустые ячейки -​ будут выделены);​ командой сайта office-guru.ru​

​Ctrl+H​​ все ячейки, которые​или просто поместите​ ячейки и так​Нажмите​ способы для решения​Serge_007​Или еще проще.​

​:​​ нули поставить только​, поле​ и тут же​

​Blanks​​F5​ быстрых способов заполнить​​Мышью подвиньте за​​ вставить 0 (вместо​Ещё один из​ значения)​ же поставьте галку​Специальной вставки (Paste Special)​​и затем кнопку​​ Вашим советом​: Пробовал (у меня​

​вместо​​Вместо того, чтобы вводить​(Найти и заменить).​ порядок в таблице,​ в активную ячейку.​​(Пустые ячейки) и​, чтобы отобразить диалоговое​​ пустые ячейки различными​ левый край полосу​ пробела) Нажать Ctrl+Enter​ вариантов:​​Little​ на​​в контекстом меню,​Выделить (Special)​Спасибо ещё раз!!!​ excel 2010) после​ENTER​ повторяющиеся данные, в​

​Перейдите на вкладку​​ то лучше заменить​ Как видно на​​ нажмите​ окно​ значениями в Excel​

excelworld.ru>

​ прокрутки вправо​

• Как в excel выделить пустые ячейки
• В excel посчитать количество заполненных ячеек
• Excel посчитать пустые ячейки в excel
• Excel количество пустых ячеек
• Как в эксель заполнить пустые ячейки
• Excel удалить пустые ячейки
• В excel убрать пустые ячейки
• Удалить пустые ячейки в excel
• Как убрать пустые ячейки в столбце excel
• Как в excel посчитать пустые ячейки
• Excel проверка на пустую ячейку
• Excel убрать пустые ячейки в столбце excel

Особенности использования функции ЕПУСТО в Excel

Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-либо условия, например, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:

=ЕПУСТО(значение)

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

Примечания:

1. Если в качестве аргумента функции было явно передано какое-либо значение (например, =ЕПУСТО(ИСТИНА), =ЕПУСТО(«текст»), =ЕПУСТО(12)), результат ее выполнения – значение ЛОЖЬ.
2. Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Например, =НЕ(ЕПУСТО(A1)) вернет ИСТИНА, если A1 не является пустой.
3. Запись типа =ЕПУСТО(АДРЕС(x;y)) всегда будет возвращать значение ложь, поскольку функция АДРЕС(x;y) возвращает ссылку на ячейку, то есть непустое значение.
4. Функция возвращает значение ЛОЖЬ даже в тех случаях, когда в переданной в качестве аргумента ячейке содержится ошибка или ссылка на ячейку. Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Например, в ячейку A1 была введена формула =ЕСЛИ(2>1;””;ЛОЖЬ), которая вернет пустую строку «». В этом случае функция =ЕПУСТО(A1) вернет значение ЛОЖЬ.
5. Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива (выделить требуемое количество пустых ячеек, ввести формулу «=ЕПУСТО(» и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl+Shift+Enter)

Отображение и скрытие нулевых значений в ячейках Excel

Как известно, в Excel довольно часто могут появляться нулевые значения. Чаще всего – это результат работы той или иной формулы. В общем-то в нуле как таковом нет ничего плохого. Глядя на него становится понятно, что это результат каких-то вычислений. Однако, потребность отображать нулевые значения (0) возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.

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

В столбце С содержится формула, которая вычисляет сумму (количество, умноженное на цену).

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

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

Подсчитать количество непустых ячеек

20.04.2012, 18:51

Подсчитать среднее значение непустых ячеекКак подсчитать среднее значение диапазона ячеек, чтобы среднее считалось по количеству не пустых.

Посчитать количество непустых ячеекДобрый день! Нужно посчитать количество непустых ячеек (содержащих число) каждому региону. .

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

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

20.04.2012, 22:402 23.04.2012, 11:58 3 23.04.2012, 12:10 4 Вложения

для отправки.xls (24.0 Кб, 345 просмотров)

23.04.2012, 13:135 Вложения

taurus-reklama.xls (34.0 Кб, 797 просмотров)

23.04.2012, 14:11 6 23.04.2012, 14:287

А если попробовать?

ЗЫ Специальная вставка не нужна, достаточно Ctrl+C – Ctrl+V

23.04.2012, 15:19 8

А если попробовать?

ЗЫ Специальная вставка не нужна, достаточно Ctrl+C – Ctrl+V

23.04.2012, 15:509

Потому что в столбце W у Вас нет данных

Работатет она. Смотрите: =СЧЁТЕСЛИ(D11:D24;”“&0) возвращает правильное значение – 13.

Разбираем по-полочкам: 1. Сколько всего ячеек в диапазоне D11:D24? – 14 (D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24) 2. Сколько ячеек в диапазоне D11:D24 содержащих значения? – 4 (D11,D13,D15,D17) 3. Сколько пустых ячеек в диапазоне D11:D24? – 10 (D12,D14,D16,D18,D19,D20,D21,D22,D23,D24) 4. Сколько ячеек в диапазоне D11:D24 равны нулю? – 1 (D17)

Сколько ячеек в диапазоне D11:D24 НЕ равны нулю? – 13 Это все ячейки диапазона ( 14 ) минус ячейки равные нулю ( 1 ).

Можно посчитать и так: Сколько ячеек в диапазоне D11:D24 НЕ равны нулю? – 13 Это все пустые ячейки диапазона ( 10 ) плюс ячейки содержащие значения ( 4 ) минус ячейки равные нулю ( 1 ).

Примеры использования функции ЕПУСТО в Excel

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

Исходные данные:

Выделим ячейки C3:C18 и запишем следующую формулу:

Формула ЕСЛИ выполняет проверку возвращаемого результата функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов («На пересдачу» или «Сдал»). Результат выполнения функции:

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

1. Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
3. Нажмите на кнопку «Формат» (как на образцу), потом укажите в окне «Формат ячеек» красный цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек или двоек мы получаем соответственное сообщение «На пересдачу» и красную заливку.

Как удалить строки из MS Excel, который имеет>65000 строк?

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

2 ответов

это иногда происходит, когда эти ячейки имеют форматирование, но нет данных

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

Как заполнить разреженные данные предыдущим непустым значением в SQL.
Предложение MODEL – не что иное, как специфический для Oracle диалект для реализации логики, подобной электронной таблице, в базе данных. Я настоятельно рекомендую прочитать соответствующую Белую книгу от Oracle, которая очень хорошо объясняет функциональность:

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

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

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

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

Рассмотрим настройки.

В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).

Посередине выбирается нужный тип:

• арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
• геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
• даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:

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

автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).

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

Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.

Использование функции ВПР

Сделать формулу в Excel можно так:

В качестве примера возьмем табличку с фамилиями и суммами.

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

Активируем поле с фамилией «Васильев» и через значок строки fx вызываем меню с формулами.

рис.2. Здесь нужно выбрать действие ВПРрис.3. После нажатия «ОК» появляется окошкорис.4. Добавляем в строку «Искомое значение» необходимую фамилиюрис.5. В строку «Таблица» нужно добавить весь диапазон, в котором будет осуществляться поиск информации

Чтобы обозначить рамки поиска в строку «Номер столбца» введите двойку.

рис.6. В данном случае она обозначает номер столбца, где будет выполнен поискрис.7. После нажатия «ОК» выполнится перенос данных из первой таблицы во вторуюрис.8. Чтобы выполнить аналогичные манипуляции с другой фамилией из списка, скопируйте формулурис.9. Активируйте следующую ячейку и вставьте в строку fx, изменив при этом адрес искомой информации

Можно работать и с большим объемом информации, которая будет иметь более беспорядочное расположение.

ПОСМОТРЕТЬ ВИДЕО

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

1. Если в качестве аргумента функции было явно передано какое-либо значение (например, =ЕПУСТО(ИСТИНА), =ЕПУСТО(«текст»), =ЕПУСТО(12)), результат ее выполнения – значение ЛОЖЬ.
2. Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Например, =НЕ(ЕПУСТО(A1)) вернет ИСТИНА, если A1 не является пустой.
3. Запись типа =ЕПУСТО(АДРЕС(x;y)) всегда будет возвращать значение ложь, поскольку функция АДРЕС(x;y) возвращает ссылку на ячейку, то есть непустое значение.
4. Функция возвращает значение ЛОЖЬ даже в тех случаях, когда в переданной в качестве аргумента ячейке содержится ошибка или ссылка на ячейку. Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Например, в ячейку A1 была введена формула =ЕСЛИ(2>1;””;ЛОЖЬ), которая вернет пустую строку «». В этом случае функция =ЕПУСТО(A1) вернет значение ЛОЖЬ.
5. Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива (выделить требуемое количество пустых ячеек, ввести формулу «=ЕПУСТО(» и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl+Shift+Enter)

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

=ЕСЛИ( СТРОКА() — СТРОКА (БезПустых) +1> ЧСТРОК( Пустые) — СЧИТАТЬПУСТОТЫ (Пустые);»«; ДВССЫЛ ( АДРЕС (НАИМЕНЬШИЙ ((ЕСЛИ (Пустые »»; СТРОКА (Пустые); СТРОКА()+ ЧСТРОК( Пустые))); СТРОКА() — СТРОКА (БезПустых)+1); СТОЛБЕЦ( Пустые);4)))

КАК АВТОМАТИЧЕСКИ ЗАПОЛНИТЬ ЯЧЕЙКИ В EXCEL С БОЛЬШИМИ ТАБЛИЦАМИ

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

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

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

КАК В EXCEL ЗАПОЛНИТЬ ЯЧЕЙКИ ОДИНАКОВЫМИ ЗНАЧЕНИЯМИ?

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

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

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

2. Выберите инструмент: «Главная»-«Найти и выделить»-«Перейти» (или нажмите горячие клавиши CTRL+G).

3. В появившемся окне щелкните на кнопку «Выделить».

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

5. Теперь введите формулу «=A1» и нажмите комбинацию клавиш CTRL+Enter. Так выполняется заполнение пустых ячеек в Excel предыдущим значением – автоматически.

6. Выделите колонки A:B и скопируйте их содержимое.

7. Выберите инструмент: «Главная»-«Вставить»-«Специальная вставка» (или нажмите CTRL+ALT+V).

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

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

Внимание! В 5-том пункте таблица красиво заполнилась без ошибок, так как наша активная ячейка была по адресу A2, после выполнения 4-го пункта. При использовании данного метода будьте внимательны и следите за тем где находится активная ячейка после выделения

Важно откуда она будет брать свои значения

ПОЛУАВТОМАТИЧЕСКОЕ ЗАПОЛНЕНИЕ ЯЧЕЕК В EXCEL ИЗ ВЫПАДАЮЩЕГО СПИСКА

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

В новой исходной таблице автоматически заполните колонки C и D соответствующие им данными.

1. Заполните заголовки колонок C1 – «Дата» и D1 – «Тип платежа».

2. В ячейку C2 введите дату 18.07.2015

3. В ячейках С2:С4 даты повторяются. Поэтому выделяем диапазон С2:С4 и нажимаем комбинацию клавиш CTRL+D, чтобы автоматически заполнить ячейки предыдущими значениями.

4. Введите текущюю дату в ячейку C5. Для этого нажмите комбинацию клавиш CTRL+SHIFT+; (точка с запятой на английской раскладке клавиатуры). Заполните текущими датами колонку C до конца таблицы.

5. Диапазон ячеек D2:D4 заполните так как показано ниже на рисунке.

6. В ячейке D5 введите первую буку «п», а дальше слово заполнять не надо. Достаточно нажать клавишу Enter.

7. В ячейке D6 после ввода первой буквы «н» не отображается часть слова для авто-заполнения. Поэтому нажмите комбинацию ALT+(стрела вниз), чтобы появился выпадающий список. Выберите стрелками клавиатуры или указателем мышки значение «наличными в кассе» и нажмите Enter.

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

Внимание! Если значение состоит из нескольких строк, то при нажатии на комбинацию ALT+(стрела вниз) оно не будет отображаться в выпадающем списке значений. Разбить значение на строки можно с помощью комбинации клавиш ALT+Enter

Таким образом, текст делится на строки в рамках одной ячейки.

Разбить значение на строки можно с помощью комбинации клавиш ALT+Enter. Таким образом, текст делится на строки в рамках одной ячейки.

Примечание

Обратите внимание, как мы вводили текущую дату в пункте 4 с помощью горячих клавиш (CTRL+SHIFT+;). Это очень удобно! А при нажатии CTRL+SHIFT+: мы получаем текущее время

« 4 СПОСОБА ЗАМЕНИТЬ ТОЧКУ НА ЗАПЯТУЮ В EXCEL

ПРИМЕР КАК ИЗМЕНИТЬ СРАЗУ ВСЕ ЦЕНЫ В EXCEL »