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

Sverit 2 tablici 1 8 способов как сравнить две таблицы в Excel Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

  • простой способ, как сравнить две таблицы в Excel;
  • быстрое выделение значений, которые отличаются;
  • сравнить две таблицы в Excel с помощью условного форматирования;
  • сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил;
  • как сравнить две таблицы в Excel с помощью функции ВПР;
  • как сравнить две таблицы в Excel с помощью функции ЕСЛИ;
  • сравнить две таблицы с помощью макроса VBA;
  • сравнить с помощью надстройки Inquire.

12 наиболее распространённых проблем с Excel и способы их решения

12 наиболее распространённых проблем с Excel и способы их решения

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

В Telegram-канале «Лайфхакер» только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!

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

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

Вы не даёте заголовки столбцам таблиц

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

Пустые столбцы и строки внутри ваших таблиц

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

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

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

Данные одного типа искусственно располагаются в разных столбцах

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

правильно работать в Excel

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

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

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

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Рекомендуемый формат таблицы выглядит так:

Excel

Разнесение информации по разным листам книги «для удобства»

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

Информация в комментариях

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

Бардак с форматированием

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

Excel

  1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
  2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
  3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

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

excel

Страничный режим листа в Excel

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

Причина

Эта проблема возникает, когда книга содержит более 4000 различных комбинаций форматов ячеей в Excel 2003 или 64 000 различных комбинаций в Excel 2007 и более поздних версиях. Сочетание определяется как уникальный набор элементов форматирования, применяемых к ячейке. Сочетание включает в себя все форматирование шрифта (например, шрифт, размер шрифта, точечная, полужирная и подчеркнутая), границы (например, расположение, вес и цвет), шаблоны ячений, форматирование номеров, выравнивание и защита ячеей.

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

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

Книга с более чем 4000 стилями может открываться в Excel 2007 и более поздних версиях из-за увеличенного ограничения форматирования. Однако это может привести к ошибке в Excel 2003.

Microsoft Excel/Форматирование ячеек

Здесь (слева направо) кнопки: по левому краю, по центру и по правому краю.

Выравнивание по вертикали изменяется с помощью окна «Формат ячеек», закладка «Выравнивание»:

Format yacheek viravnivanie.png

Объединение ячеек [ править ]

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

Obedinennie yacheyki 001.png

Это можно сделать двумя способами:

  • кнопка «Объединить и поместить в центре» на панели инструментов «Форматирование» Knopka obedinenie yacheek.png;
  • галочка «объединение ячеек» в окне «Формат ячеек», закладка «Выравнивание».

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

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

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

Очистка данных в ячейках и удаление ячеек [ править ]

Окно «Удаление ячеек»

  • Выделяем «нужные» ячейки и нажимаем Delete ;
  • Выделяем «нужные» ячейки, щелкаем правой кнопкой и выбираем «Очистить содержимое»;
  • Выделяем «нужные» ячейки, заходим в меню «Правка» → «Очистить» → «Выбираем нужный вариант»;
  • Клавиша ← Backspace очищает содержимое активной (одной!) ячейки и включает режим редактирования. Ее удобно использовать при необходимости удалить одно (не все, как по Delete ) значение из выделенного диапазона;
  • Если «родную» неотформатированную ячейку протащить с помощью маркера автозаполнения по отформатированным, произойдет своего рода удаление;
  • пункт меню «Правка» → «Удалить…» и пункт контекстного меню «Удалить…» приведет к открытию окна «Удаление ячеек».

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

Заливка ячеек цветом [ править ]

Есть два способа изменить цвет заливки выделенных ячеек:

  • кнопка «Цвет заливки» на панели инструментов «Форматирование» Knopka cvet zalivki.png;
  • окно «Формат ячеек», закладка «Вид»:

Format yacheek vid.png

Добавление границ ячеек [ править ]

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

  • Кнопка «Границы» на панели инструментов «Форматирование» Knopka granici.png;
  • окно «Граница», вызываемое из кнопки «Границы» —> «Нарисовать границы…» (см. выше):

Knopka narisovat granici.png Okno granica.png

  • окно «Формат ячеек», закладка «Граница»:

Format granica.png

Формат представления данных в ячейках [ править ]

Формат ячеек по умолчанию («Общий») [ править ]

По умолчанию после создания документа все ячейки находятся в формате «Общий». Этот формат имеет ряд хитростей:

  • числа выравниваются по правому краю, а текст — по левому;
  • если, изменяя ширину столбца, сделать ее меньше определенной, то число в ячейках заменяется на символы «#». Это не ошибка. Это означает, что нужно сделать столбец пошире;
  • если число очень большое («6000000000000») или очень маленькое («0,00000000000001»), оно автоматически преобразуется в экспоненциальный (научный) формат («6E+12» и «1E-14» соответственно);
  • при изменении ширины столбца округляются десятичные дроби. Например, если написать «3,1415», затем изменить ширину так, чтобы «5» перестала помещаться, в ячейке отобразится «3,142».
Необходимость изменения формата по умолчанию на другой [ править ]

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

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

Есть 3 способа изменить формат представления данных в ячейках:

  1. автоматически после ввода определенных данных в ячейку Excel сам сменит формат ячейки;
  2. с помощью кнопок на панели инструментов «Форматирование».
  3. с помощью окна «Формат ячеек»;
Автоматическое изменение формата ячейки после ввода данных [ править ]

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

  • Дата. Если в ячейке написать «1.2.3» или «1/2/3», Excel заменит это на «01.02.2003» (первое число второго месяца третьего года). При этом формат ячейке будет автоматически преобразован в «Дата». Если написать «1.2» или «1/2», то Excel заменит это на «01.фев».;
  • Процентный. Если в ячейке написать «1%», формат ячейки автоматически сменится на «Процентный»;
  • Время. Если в ячейке написать «13:46:44» или «13:46», формат ячейки автоматически сменится на «Время»;
Изменение формата ячеек с помощью кнопок на панели инструментов «Форматирование» [ править ]

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

Format dannih v yacheyke 002.png

Описание кнопок (слева направо):

  • Денежный формат. Будет использована денежная единица по умолчанию (см. выше);
  • Процентный формат. Если в ячейке уже будет находится число, то Excel домножит его на 100 и добавит знак «%». Все правильно, ведь 1 арбуз — это «100%», а «0,7» арбуза — «70%»;
  • Формат с разделителями (числовой формат). В этом формате будут отделяться пробелом группы разрядов (сотни, сотни тысяч и т.д.) и будет добавлено 2 знака после запятой;
  • Увеличить разрядность. Добавляет один десятичный разряд;
  • Уменьшить разрядность. Убирает один десятичный разряд.
Изменение формата с помощью окна «Формат ячеек» [ править ]

Общие сведения. Способы запуска

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

Существует 3 способа открытия окна «Формат ячеек»:

  1. выделяем нужные ячейки —> меню «Формат» —> «Ячейки…»;
  2. выделяем нужные ячейки —> щелкаем правой кнопкой на одной из них —> «Формат ячеек»;
  3. выделяем нужные ячейки —> нажимаем «Ctrl+1».

Format dannih v yacheyke 001.png

Формат «Общий»

Формат «Числовой»

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

    Также можно установить галочку «Задать точность как на экране» для всей книги:

    1. «Параметры Excel» → вкладка «Дополнительно» → раздел «При пересчете это книги: «.

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

    Формат «Денежный»

    Те же настройки, что и в «Числовой» (кроме отключения разделения разрядов), плюс выбор денежной единицы. По умолчанию отображается денежная единица, указанная в настройках «Панель управления» —> «Язык и региональные стандарты» —> закладка «Региональные параметры».

    Формат «Финансовый»

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

    Формат «Дата»

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

    Формат «Время»

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

    Формат «Процентный»

    В формате «Процентный» все числа умножаются на 100 и добавляется знак «%». Также можно настроить количество десятичных разрядов после запятой.

    Формат «Дробный»

    В формате «Дробный» Excel пытается преобразовать десятичные дроби в обыкновенные. Например, «1,2» (одна целая, две десятых) будет преобразовано в «1 1/5» (одна целая, одна пятая).

    Формат «Экспоненциальный»

    Формат «Экспоненциальный» удобен для представления очень больших (расстояние до Солнца в метрах) или очень маленьких (масса атома водорода в килограммах) чисел. Например, число «299 792 458» (скорость света в метрах) в этом формате преобразуется в «3,E+08». Знак «+» здесь означает, что запятую нужно передвинуть вправо, а «08» — на какое количество разрядов. Также можно настроить количество знаков после запятой.

    Формат «Текстовый»

    Значения в ячейках, оформленных в этом формате, отображаются точно так же, как вводятся. Они обрабатываются как строки вне зависимости от их содержания. Например, если в ячейке, оформленной в формате «Текстовой», написать «1.2.3», Excel не будет пытаться преобразовать это в дату.

    Ввод в ячейку 1-го символа « ’ » (клавиша «Э» русской раскладки) автоматически приводит содержимое ячейки к текстовому формату. Символ « ’ » на экране не отображается.

    Формат «Дополнительный»

    В выпадающем списке «Язык» выберите «Русский». В списке «Тип» появятся следующие варианты: «Почтовый индекс», «Индекс + 4», «Номер телефона», «Табельный номер». Попробуйте оформить ячейку типом «Номер телефона» и введите туда 10-значный номер. Думаю объяснять не надо. Для других языков могут отобразится другие варианты.

    Пункт «(все форматы)»

    В этом пункте можно создать свой формат (например, «шт», «кг.», «м/с» и т. п.), отсутствующий среди стандартных. Для этого используется специальный язык описания форматов. В текстовом поле сверху описываете его и нажимаете «Enter». Для удаления формата выделяете «нужный» формат и нажимаете кнопку «Удалить». Удалить встроенный формат невозможно. (Совет: форматы хранятся в рабочей книге; удаление ненужных форматов удобно завершать Отменой, чтоб не «испортить» формат текущей ячейки.)

    Условное форматирование [ править ]

    ПРИМЕЧАНИЕ: В Office 2007 значительно расширились возможности условного форматирования.

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

    Сделать это можно с помощью окна «Условное форматирование», вызываемым так:

    «Формат» —> «Условное форматирование…»

    Uslovnoe formatirovanie 001.png

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

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

    Uslovnoe formatirovanie 002.png

    Здесь первое условие «=»»» прописано для того, чтобы Excel пустые ячейки не оформлял как ячейки, содержащие «0».

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

    Функция «ОСТАТ(число;делитель)» находит остаток от деления. В даных формулах использована относительная ссылка на ячейку «C14» (первую ячейку диапазона), по остальным ячейкам Excel «пробежится» сам. Если бы ссылка была абсолютной (что происходит по умолчанию, если выделять из окна «УФ»), это бы работало только для ячейки «C14».

    Форматирование выделенной области с помощью мини-панели инструментов

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

    Как создать таблицу в Эксель: Форматирование выделенной области с помощью мини-панели инструментов

    Чтобы отобразить мини-панель, выделите ячейки, нуждающиеся в форматировании, и щелкните правой кнопкой мыши в любом месте выделенной области. Мини-панель отобразится непосредственно возле открывшегося контекстного меню. Если на мини-панели выбран какой-либо инструмент, например кнопка раскрывающегося меню Шрифт или Размер шрифта, то контекстное меню исчезнет.

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

    Гистограммы

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

    • градиентная;
    • сплошная.

    Условное форматирование в эксель

    Рассмотрим каждый из предложенных вариантов.

    Градиентная заливка

    1. Первым делом необходимо выделить нужные строки и столбцы. Затем кликнуть на иконку «Условное форматирование». После этого перейти в раздел «Гистограммы» и выбрать любую из предложенных заливок.

    Условное форматирование в эксель

    К значениям по умолчанию относятся:

    • зеленая;
    • красная;
    • оранжевая;
    • голубая;
    • фиолетовая.

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

    Сплошная заливка

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

    Условное форматирование в эксель

    Цвета используются те же самые.

    Другие правила

    Условное форматирование в эксель

    Здесь вы сможете настроить:

    Условное форматирование в эксель

    минимальное и максимальное значение;

    Условное форматирование в эксель

    внешний вид столбца.

    Условное форматирование в эксель

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

    Условное форматирование в эксель

    Как изменить или удалить правило? ​

    На одном листе может применяться более одного правила на один и тот же, либо на разные диапазоны.

    По кнопке «Изменить правило» откроется меню, в котором можно отредактировать формулу, изменить параметры форматирования и т.д.

    Кнопка «Удалить правило» удалит то, на которым в данный момент стоит выделение.

    Также правила можно менять местами, нажимая на стрелочки в этом же меню «вверх» или «вниз». Выполняются правила снизу-вверх, т.е. то, которое сверху, перекрывает нижние (выполняется последним).

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

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

    Принципы форматирования таблиц в Microsoft Excel

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

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

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

    АВТОМАТИЧЕСКОЕ ДОБАВЛЕНИЕ СТРОК И СТОЛБЦОВ В ТАБЛИЦУ EXCEL

    1. «Отменить авторазвертывание таблицы» – отменяет создание новой колонки. В этой ситуации для данной опции так же можно нажать комбинацию клавиш CTRL+Z – результат будет тот же.
    2. «Не развертывать таблицы автоматически» – опция полностью отключает автоматическое расширение всех таблиц при заполнении смежных ячеек.
    3. «Параметры автозамены» – открывает диалоговое окно «Автозамена» с активной закладкой «Автоформат при вводе».

    АВТОМАТИЧЕСКОЕ ДОБАВЛЕНИЕ СТРОК И СТОЛБЦОВ В ТАБЛИЦУ EXCEL

    Чтобы снова включить автоматическое добавление строк и столбцов таблицы при заполнении ближайших ячеек следует открыть настройки программы: «Файл»-«Параметры»-«Правописание»-«Параметры автозамены»-«Автоформат при вводе». На данной вкладке отмечаем галочкой опцию: «Выполнять в ходе работы»-«Включать в таблицу новые строки и столбцы».

    Глава 3. Работа с таблицами Excel

    Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). В этой главе мы обсудим различные методы работы с таблицами, включая создание таблиц, изменение их размера, вставку и удаление строк и столбцов, перемещение столбцов.

    Рис. 3.1. Диалоговое окно Создание таблицы

    Скачать заметку в формате Word или pdf, примеры в формате Excel

    Создание таблицы

    Вы можете создать таблицу с данными или пустую таблицу. При создании таблицы Excel запрашивает расположение данных и наличие заголовков (рис. 3.1). Если строки заголовков нет, снимите флажок, и Excel создаст заголовки по умолчанию: Столбец1, Столбец2 и т.д.

    Таблицу можно создать командами ленты. Пройдите по меню Главная –> Стили –> Форматировать как таблицу (рис. 3.2). Выберите один из предустановленных форматов таблицы. Excel откроет диалоговое окно Создание таблицы, как на рис. 3.1.

    Ris. 3.2. Sozdanie tablitsy knopkami lenty

    Рис. 3.2. Создание таблицы кнопками ленты

    Также можно пройти по меню Вставка –> Таблица (рис. 3.3). Excel снова откроет диалоговое окно Создание таблицы.

    Ris. 3.3. Vstavka tablitsy

    Рис. 3.3. Вставка таблицы

    Но, конечно же быстрее вставить таблицу с помощью клавиатурных сокращений. Нажмите Ctrl+T (английское) или Ctrl+L. Эти две команды эквивалентны, и обе открывают диалоговое окно Создание таблицы. С помощью клавиш также можно пройти по меню ленты. Alt+Я+Ь открывает стили таблицы, как на рис. 3.2. После чего стрелками можно выбрать нужный стиль и нажать Enter. Ну а Alt+С+1 эквивалентно вызову таблицы из меню Вставка (как на рис. 3.3).

    После создания таблицы появится контекстная вкладка ленты под названием Работа с таблицами –> Конструктор. Она будет появляться всякий раз, когда по крайней мере одна из ячеек Таблицы будет активна.

    Изменение размеров таблиц

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

    Чтобы изменить размер таблицы вручную:

    • Используйте небольшой значок, расположенный в правом нижнем углу таблицы (рис. 3.4). Перетащив маркер, можно изменить число строк или число столбцов Таблицы. Но не одновременно. Чтобы изменить число строк и число столбцов, используйте маркер дважды.
    • Или активируйте вкладку Конструктор и кликните на кнопке Размер таблицы. Excel отобразит диалоговое окно Изменение размера таблицы (рис. 3.5). Введите диапазон с клавиатуры или выберите мышкой на листе и нажмите кнопку ОК. Можно выбрать любой диапазон, если выбрана хотя бы одна ячейка заголовка и одна ячейка тела. Новый диапазон должен перекрывать текущий диапазон таблицы.

    Ris. 3.4. Marker ruchnogo izmeneniya razmera Tablitsy

    Рис. 3.4. Маркер ручного изменения размера Таблицы

    Рис. 3.5. Диалоговое окно Изменение размера таблицы

    Автоматическое изменение размера таблицы

    Когда вы вводите данные в первую пустую строку под таблицей, Excel автоматически увеличивает таблицу, чтобы включить эту новую строку данных. Новая строка инициализируется всеми форматами и формулами в вычисляемых столбцах. Можно указать Excel, следует ли автоматически включать новые строки и столбцы при вводе значений в ячейки, расположенные рядом с таблицей. Для этого пройдите по меню Файл –> Параметры. В окне Параметры Excel перейдите на вкладку Правописание, кликните на кнопку Параметры автозамены. В окне Автозамена перейдите на вкладку Автоформат при вводе. Установите или снимите флажок Включить в таблицу новые строки и столбцы. Эта опция, которая является опцией уровня приложения (а не книги), включена по умолчанию.

    Ris. 3.6. Parametry Excel dlya upravleniya povedeniem Tablits po umolchaniyu

    Рис. 3.6. Параметры Excel для управления поведением Таблиц по умолчанию; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

    Еще один способ добавить новую строку в таблицу – выбрать нижнюю правую ячейку в диапазоне тела данных (но не в строке итогов) и нажать клавишу TAB. Новая строка добавиться независимо от того стоит ли галочка Включать в таблицу новые строки и столбцы. Однако, если лист защищен, вы не сможете вставить новую строку в таблицу.

    Сохранение пустых строк под таблицей

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

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

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

    Ris. 3.7. Preduprezhdenie vyzvannoe popytkoj rasshirit tablitsu cherez obedinennye yachejki

    Рис. 3.7. Предупреждение, вызванное попыткой расширить таблицу через объединенные ячейки

    Перемещение столбцов

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

    Вставка строк и столбцов

    Не предусмотрена возможность вставлять строки или столбцы с вкладки ленты Конструктор. Это можно сделать пройдя по меню Главная –> Ячейки –> Вставить:

    Ris. 3.8. Vstavka stolbtsa ili stroki s vkladki Glavnaya

    Рис. 3.8. Вставка столбца или строки с вкладки Главная

    Как правило, удобнее щелкнуть правой кнопкой мыши нужное место в таблице, чтобы открыть контекстное меню:

    Ris. 3.9. Vstavka stolbtsa ili stroki iz kontekstnogo menyu pravoj knopkoj myshi

    Рис. 3.9. Вставка столбца или строки из контекстного меню правой кнопкой мыши

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

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

    Удаление строк и столбцов

    На вкладке ленты Конструктор нет возможности удалить строки или столбцы. Для этого можно пройти по меню Главная –> Ячейки –> Удалить:

    Ris. 3.10. Udalenie stolbtsa ili stroki s vkladki Glavnaya

    Рис. 3.10. Удаление столбца или строки с вкладки Главная

    Проще щелкнуть правой кнопкой мыши нужное место в таблице, чтобы открыть контекстное меню и выбрать, что удалить:

    Ris. 3.11. Udalenie stolbtsa ili stroki iz kontekstnogo menyu pravoj knopkoj myshi

    Рис. 3.11. Удаление столбца или строки из контекстного меню правой кнопкой мыши

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

    Преобразование таблицы в диапазон

    Некоторые ограничения препятствуют определенным действиям с таблицами, поэтому бывает полезно преобразовать таблицу в диапазон. При этом все структурированные ссылки преобразуются в стандартные (абсолютные) ссылки на ячейки. Например, формула со структурированными ссылками =[@Units]*[@Cost] преобразуется в =Лист1!$H5*Лист1!$I5.

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

    Удаление повторяющихся строк

    Пройдите по меню Конструктор –> Инструменты –> Удалить дубликаты. Excel запросит, какие столбцы использовать, чтобы определить, когда строка является дубликатом другой строки:

    Ris. 3.12. Udalenie dublikatov strok

    Рис. 3.12. Удаление дубликатов строк

    Excel сохраняет первую уникальную строку, удаляет все последующие повторяющиеся строки и сдвигает вверх строки ниже удаления.

    Параметры Таблиц

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

    Ris. 3.13. Parametry tablits

    Рис. 3.13. Параметры таблиц

    Первый столбец/Последний столбец. Эти параметры позволяют применить пользовательские форматы для первого и последнего столбцов.

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

    Экспорт данных таблицы

    Существует два способа экспорта данных из таблицы: экспорт в SharePoint в виде списка или экспорт в Visio в виде сводной схемы. Для обоих этих вариантов необходимо иметь соответствующее приложение.

    Экспорт в Visio

    Сводная схема – это набор фигур в иерархической древовидной структуре. Каждый узел в дереве разбивается на подузлы на основе данных таблицы. Сводные схемы Visio похожи на сводные таблицы Excel в том, что они позволяют изучать данные под разными углами. Перед экспортом сохранить книгу Excel. Чтобы экспортировать таблицу в схему в Visio, пройдите по меню Конструктор –> Экспорт –> Экспорт таблицы в сводную схему Visio:

    Ris. 3.14. Eksport Tablitsy v Visio

    Рис. 3.14. Экспорт Таблицы в Visio

    Ris. 3.15. Panel zadach svodnoj shemy Visio posle eksporta tablitsy Excel

    Рис. 3.15. Панель задач сводной схемы Visio после экспорта таблицы Excel

    Дополнительную информацию можно получить на сайте Microsoft – Создание сводной схемы в Visio.

    Экспорт в SharePoint

    SharePoint – это служба общего доступа по умолчанию, используемая в Office 365 и Power BI. Чтобы совместно использовать таблицу с помощью SharePoint, выберите таблицу и пройдите по меню Конструктор –> Экспорт –> Экспорт таблицы в список SharePoint (см. рис. 3.14).

    Появится окно мастера экспорта, включающее два шага. Введите URL-адрес SharePoint. Затем поставьте галочку Создать неизменяемое подключение к новому списку SharePoint, если хотите, чтобы список SharePoint был доступен только на чтение. Введите отображаемое имя и, по желанию, описание.

    Ris. 3.16. Eksport v spisok SharePoint shag 1

    Рис. 3.16. Экспорт в список SharePoint, шаг 1

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

    На втором шаге мастер экспорта отобразит типы данных столбцов:

    Ris. 3.17. Tipy dannyh eksportiruemoj tablitsy

    Рис. 3.17. Типы данных экспортируемой таблицы

    Внимательно прочитайте указания в окне мастера. Проверьте всё ли Ok. Нажмите Готово.

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

    При экспорте таблицы Excel на сайт SharePoint каждому столбцу в списке SharePoint назначается один из следующих типов данных: Текст (однострочный), Текст (многострочный), Денежный, Дата и время, Числовой, Гиперссылка (URL-адрес). Другие типы данных недопустимы. Если столбец содержит ячейки с разными типами данных, применяется тип данных, который может использоваться для всех ячеек. Например, если столбец содержит числа и текст, в списке SharePoint будет использоваться текстовый тип.

    Появится сообщение о том, что ваша таблица опубликована, а также URL-адрес списка (рис. 3.18). Чтобы перейти к списку, щелкните URL-адрес (рис. 3.19). Добавьте URL-адрес в избранное в своем браузере. Можно открыть список и другим способом. Перейдите на сайт SharePoint, в правом верхнем углу щелкните значок шестеренки и выберите элемент Контент сайта (рис. 3.20). Откроется окно доступного контента. Выберите ваш список.

    Ris. 3.18. Tablitsa Excel uspeshno eksportirovana v spisok SharePoint

    Рис. 3.18. Таблица Excel успешно экспортирована в список SharePoint

    Ris. 3.19. Spisok SharePoint v web brauzere

    Рис. 3.19. Список SharePoint в web-браузере

    Ris. 3.20. Otkrytie spiska SharePoint instrumentami sajta

    Рис. 3.20. Открытие списка SharePoint инструментами сайта

    Это соединение является OLE DB соединением, которое можно просмотреть, пройдя по меню Конструктор –> Обновить –> Свойства подключения. В окне Свойства подключения перейдите на вкладку Определение. У меня Строка подключения выглядит так:

    Provider=Microsoft.Office.List.OLEDB.2.0;Data Source= » » ;ApplicationName=Excel;Version=12.0.0.0

    Будьте внимательны, после того как таблица была экспортирована в список SharePoint, вы не можете изменить данные в таблице. Если вы внесете какие-либо изменения, они будут потеряны (без предупреждения) при обновлении таблицы.

    Как сделать таблицу в Excel. Пошаговая инструкция

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

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

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

    1. Как работать в Excel с таблицами. Пошаговая инструкция
    2. Прежде чем работать с таблицами в Эксель, последуйте рекомендациям по организации данных:
    3. 1. Выделите область ячеек для создания таблицы
    4. 2. Нажмите кнопку “Таблица” на панели быстрого доступа
    5. 3. Выберите диапазон ячеек
    6. 4. Таблица готова. Заполняйте данными!
    7. Форматирование таблицы в Excel
    8. Как добавить строку или столбец в таблице Excel
    9. Как отсортировать таблицу в Excel
    10. Как отфильтровать данные в таблице Excel
    11. Как посчитать сумму в таблице Excel
    12. Как в Excel закрепить шапку таблицы
    13. Как перевернуть таблицу в Excel

    Как работать в Excel с таблицами. Пошаговая инструкция

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

    • Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
    • Первая строка таблицы должна содержать короткие, уникальные заголовки;
    • Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
    • Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
    • В таблице не должно быть пустых строк и абсолютно пустых столбцов.

    1. Выделите область ячеек для создания таблицы

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

    2. Нажмите кнопку “Таблица” на панели быстрого доступа

    На вкладке “Вставка” нажмите кнопку “Таблица”.

    3. Выберите диапазон ячеек

    Во всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

    4. Таблица готова. Заполняйте данными!

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

    Форматирование таблицы в Excel

    Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

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

    Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

    • Отображение строки заголовков – включает и отключает заголовки в таблице;
    • Строку итогов – включает и отключает строку с суммой значений в колонках;
    • Чередующиеся строки – подсвечивает цветом чередующиеся строки;
    • Первый столбец – выделяет “жирным” текст в первом столбце с данными;
    • Последний столбец – выделяет “жирным” текст в последнем столбце;
    • Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
    • Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

    Как добавить строку или столбец в таблице Excel

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

    Как добавить строку/колонку в таблице Excel

    Чтобы добавить строку или колонку в таблице Excel:

    • Нажмите правой кнопкой мыши на любой ячейке таблицы, где вы хотите вставить строку или колонку => появится всплывающее окно:
    • Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

    • Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

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

    Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

    В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

    Как отфильтровать данные в таблице Excel

    Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

    • “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
    • “Фильтр по цвету” так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
    • “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
    • Во всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

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

    Как посчитать сумму в таблице Excel

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

    В списке окна выберите пункт “Таблица” => “Строка итогов”:

    Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.

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

    Как в Excel закрепить шапку таблицы

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

    Для того чтобы закрепить заголовки сделайте следующее:

    • Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

    • Выберите пункт “Закрепить верхнюю строку”:

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

    Как перевернуть таблицу в Excel

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

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

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

    • Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

    • В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

    • Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

    В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

    2. Имя диапазона

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

    1 способ.

    Шаг 1. Выделяем диапазон ячеек В1:В6.

    Шаг 2. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Создать из выделенного):

    Диапазон Excel

    Откроется диалоговое окно «Создание имен из выделенного диапазона». Оставим активным параметр «в строке выше». ОК.

    2 способ.

    Шаг 3. Выделите диапазон ячеек А1:А6.

    Шаг 4. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):

    Диапазон Excel

    Откроется диалоговое окно «Создание имени».

    1. По умолчанию содержимое первой ячейки выделенного диапазона становится именем диапазона. Но никто не мешает нам задать другое имя. Обратите внимание, что пробел заменился на нижнее тире. Что поделаешь? Требование Excel.
    2. Определение области действия имени: вся книга или конкретный лист.
    3. Пояснения, которые мы сочтем нужным дать этому диапазону. Пояснения не участвуют в вычислениях, операциях и во всем другом прочем. Это «напоминалка» для нас.
    4. Определенная величина диапазона. Но мы можем скорректировать величину диапазона. Например, у нас прибавились записи, которые следует отнести к этому диапазону. Тем более, что рядом присутствует кнопка со стрелкой, которая позволит нам на время свернуть диалоговое окно.

    Шаг 5. Нажимаем на кнопку выпадающего меню имени ячейки (неважно, где находится активная ячейка):

    Диапазон Excel

    Шаг 6. Щелкаем ЛМ по первому имени в этом списке:

    Диапазон Excel

    Диапазон с именем «Наименование_курса» выделился полностью.

    Понять и запомнить! Разница между двумя способами:
    1. Имя диапазона определяется автоматически
    2. Имя диапазона можно задать по своему желанию

    Шаг 7. Щелкните в любой ячейке по вашему выбору. Набираем «=су»:

    Диапазон Excel

    Двойной щелчок ЛМ по «СУММ».

    Шаг 8. Выбираем диапазон по имени (набираем непосредственно в ячейке «ц». сразу появляется выпадающий список, в котором присутствуют функции, начинающие на «ц», и имя нашего диапазона). Двойной щелчок ЛМ по имени диапазона:

    Диапазон Excel

    Шаг 9. И обязательно закрывающая скобка! Требования Excel по части синтаксиса написания формул надо соблюдать.

    Диапазон Excel

    Шаг 10. Нажимаем Enter:

    Диапазон Excel

    Смотрим на строку формул: =СУММ(Цена). То есть по имени определил соответствующий диапазон.

    Шаг 11. Лента Формулы → группа команд Определенные имена → команда Диспетчер имен. Откроется диалоговое окно «Диспетчер имен»:

    Диапазон Excel

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

    Как правильно и красиво оформлять таблицы Excel 2010?

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

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

    Рис. 2.31. Группа Стили

    Рис. 2.31. Группа Стили

    Условное форматирование

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

    Рис. 2.32. Условное форматирование

    Рис. 2.32. Условное форматирование

    Посмотрите, какие правила выделения ячеек предлагает нам Excel. Можно, например, выделить ячейки, имеющие значения больше тех, которые вы зададите. Допустим, я прошу выделить все ячейки, значение температуры воды в которых больше, чем 27,5 (рис. 2.33).

    Рис. 2.33. Задание условия для форматирования

    Рис. 2.33. Задание условия для форматирования

    Выбираю желтую заливку и темно-желтый фон и не забываю нажать ОК. (Кстати, в списке есть и пользовательский формат. Если щелкнете на нем, то появится окно Формат ячеек, с помощью которого сможете полностью удовлетворить свой художественный порыв.) В итоге получаю вот такую таблицу (рис. 2.34). То, что больше 27,5, — выделено.

    Рис. 2.34. Результат условного форматирования

    Рис. 2.34. Результат условного форматирования

    Учтите только: применять условное форматирование нужно не ко всей таблице, а к тем ячейкам, в которых находятся числовые данные. Потому что программе трудно решить, ячейка с текстом, например, — это большее или меньшее числовое значение относительно 27,5? Вы можете создать и свое правило, по которому будут выделяться данные таблицы (рис. 2.35).

    Рис. 2.35. Создаем правило форматирования

    Рис. 2.35. Создаем правило форматирования

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

    Рис. 2.36. Пример создания правила форматирования (а) и результат выделения ячеек на его основании (б)

    Рис. 2.36. Пример создания правила форматирования (а) и результат выделения ячеек на его основании (б)

    По этому правилу Excel выделяет цветом числа от 0 до 10. Чем больше число, тем светлее цвет фона ячейки. Основная прелесть Excel в том, что вы можете сколько угодно менять данные в ячейках. Если уж вы задали такое правило форматирования, то все, что вы введете в ячейку, будет подчиняться этому правилу. Захотите правило удалить, выделите ячейки, к которым вы его применяли, и выберите команду Удалить правила кнопки Условное форматирование (см. рис. 2.32), таблица опять станет скучная, нераскрашенная.

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

    Форматировать как таблицу

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

    Рис. 2.37. Шаблоны для форматирования таблицы

    Рис. 2.37. Шаблоны для форматирования таблицы

    Если вам этого покажется мало, то вы можете создать свой стиль таблицы.

    Стили ячеек

    Еще один способ украсить таблицу — задать стили ячеек (рис. 2.38).

    Рис. 2.38. Стили ячеек

    Рис. 2.38. Стили ячеек

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

    Если захотите, создайте свой стиль ячейки. В меню кнопки Стили ячеек (см. рис. 2.38) есть команда Объединить стили. Она позволяет перетащить стиль из одного документа в другой. Вдруг вам так сильно понравится табличка и вы захотите нарисовать такую же. В этом случае вы сможете перетащить стиль из понравившейся книги в свою.

    Создание и редактирование таблиц

    Запуск процессора Excel 2007 можно осуществить несколькими способами:

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

    Элементы экрана

    Верхняя строка окна приложения Microsoft Excel называется строкой заголовка (рис. 1.1 рис. 1.1). В ней слева указывается имя программы Microsoft Excel и название рабочей книги Книга 1 (либо открытого файла).

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

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

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

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

    Рабочий экран Excel разделен по вертикали на столбцы, а по горизонтали на строки. Столбцам присваиваются имена, соответствующие буквам латинского алфавита, либо числам (в зависимости от выбранного стиля с помощью Главное офисное меню > Параметры Excel > Формулы), а именами строк являются только числа. Области имен столбцов и строк располагаются в верхней (столбцы) и в левой (строки) части и называются заголовками. Пользуясь Excel , можно создать таблицы размером до 256 столбцов (название последнего – IV ) и 65536 строк.

    Экран MS Excel

    Пересечение строк и столбцов образует клетки, называемые ячейками таблицы. Все ячейки имеют адреса. Адрес любой ячейки состоит из имени столбца и номера строки, на пересечении которых она расположена, например, А20, BE6, IA300 .

    Активная ячейка выделяется жирным контуром (рамкой), называемым табличным курсором. Именно в активную ячейку осуществляется ввод данных.

    На экране Excel размещаются две полосы: вертикальная – у правого края экрана и горизонтальная – у нижнего края. С помощью этих полос удобно осуществлять быстрое перемещение по таблице.

    Расположенная в нижней части экрана строка состояния содержит ряд полезных при создании таблицы сведений. На этой строке выводится информация о выбранной команде или выполняемой операции. Правая часть строки состояния показывает значение суммы числовых данных в активной ячейке и состояние включения цифровой клавиатуры (клавиши Num Lock>).

    Ввод данных в таблицу

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

    Закончить ввод данных в текущей строке можно нажатием:

    • клавиши <Enter> – данные зафиксируются в текущей ячейке, и выделением переместится на одну ячейку вниз.
    • кнопки с «галочкой» на строке формул – данные зафиксируются в текущей ячейке, и выделение останется в этой же ячейке;
    • любой клавиши со стрелкой – данные зафиксируются в текущей ячейке, и выделение переместится в ячейку в направлении, указанном стрелкой;
    • кнопки с крестом на строке формул или клавиши <Esc> – ввод данных будет отменен.

    По окончании ввода текстовые данные выравниваются по левому краю ячейки, числовые – по правому (по умолчанию). Если выравнивание требуется изменить, нужно воспользоваться командой Главная > Выравнивание или Формат Ячейки, вкладка Выравнивание (контекстное меню). При вводе нецелочисленных данных десятичные знаки определяются с помощью запятой. Если следует изменить десятичный разделитель на точку, надо последовательно выполнить настройки с помощью Панели управления.

    Ввод формул

    Ввод формулы обязательно должен начинаться со знака равенства » http://www.intuit.ru/2010/edi» >

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

    Таблица 1.1. Основные арифметические знаки и операторы сравнения

    Арифметические знаки Операторы сравнения
    + сложение = равно
    вычитание <> не равно
    / деление > больше
    * умножение < меньше
    ^ возведение в степень >= больше или равно
    % процент <= меньше или равно

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

    • процент (%);
    • возведение в степень (^);
    • умножение и деление (*, /);
    • сложение и вычитание (+, -);
    • операторы сравнения (=, <>, > ,<, >=, <=).

    Операторы с одинаковым приоритетом выполняется слева направо.

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

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

    После ввода формулы в ячейке появляется вычисленный результат, а сама формула отображается в строке формул. Если необходимо (в ходе выверки таблицы) отобразить в ячейках таблицы именно формулы расчета, а не результаты, то следует задать команду Формулы > Зависимости формул > Показать Формулы.

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

    Организация ссылок

    При перемещении или копировании формулы адрес в указанной ссылке изменяется, ориентируясь на ту позицию, в которую переносится формула. Такие ссылки носят название относительных ссылок (рис. 1.2. рис. 1.2).

    Пример относительной ссылки

    Для ввода в формулу значения из фиксированной ячейки (адрес которой при копировании или перемещении формулы остается неизменным) используются абсолютные ссылки (рис. 1.3 рис. 1.3). При их обозначении в написании адреса ячейки добавляется знак доллара. (Например, $A$20, $IA200 ).

    В случае изменения только одного значения адреса и фиксации другого используются смешанные ссылки (рис. 1.4 рис. 1.4). Знаком $ фиксируется только имя столбца (например, $А9) или имя строки (например, E$6 ). Для ввода смешанных и абсолютных ссылок используется клавиша <F4> (курсор в этом случае помещается либо внутрь ссылки, либо после нее).

    Пример абсолютной ссылки

    Пример смешанной ссылки

    Автоматическое вычисление сумм

    При необходимости суммирования большого количества данных, расположенных в смежных ячейках, может оказаться более удобным воспользоваться встроенной функцией автосуммирования, а не писать длинную формулу с перечислением всех слагаемых. Для этого следует щелкнуть мышью по кнопке Автосумма стандартной панели – при этом в строке формул появится написанная функция суммирования с указанием предлагаемого диапазона ячеек, данные которого подлежат суммированию. Если окажется, что диапазон указан правильно, то необходимо нажать клавишу <Enter>. В противном случае сначала надо ввести исправления в указанный диапазон (щелкнуть для этого по строке формул и ввести новые адреса ячеек), а затем нажать клавишу <Enter>.

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

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

    Редактирование табличных документов

    Перемещение по таблице

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

    • четыре клавиши со стрелками, обеспечивающие перемещение во всех четырех направлениях;
    • клавиши <PgUp> и <PgDn>, осуществляющие поэкранное перемещение выделения соответственно вверх и вниз по таблице;
    • клавиша <Home>, позволяющая перемещать выделение на начало текущей строки;
    • клавиши <Ctrl><Home> приводят к перемещению выделения в ячейку A1 , а нажатие клавиш <Ctrl><End> вызывает перемещения выделения в последнюю заполненную ячейку таблицы;
    • щелчок указателя мыши в соответствующей позиции экрана приводит к перемещению выделения в данную ячейку;
    • для перемещения по таблице можно использовать полосы прокрутки (как это было описано ранее);
    • можно быстро перейти в любую ячейку таблицы, если нажать клавишу <F5> или задать команду Главная > Редактирование> Найти и выделить > Перейти и ввести адрес этой ячейки или имя диапазона в поле Ссылка.
    Выделение фрагментов таблицы

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

    Выделение произвольного непрерывного фрагмента: установить курсор на начало (или конец) выделяемого фрагмента. Далее либо протянуть по всей выделяемой области мышью (при нажатой левой кнопке), либо нажать клавишу <Shift> и, удерживая ее нажатой, с помощью клавиш перемещения курсора выделить требуемую область.

    Выделение всей строки: щелкнуть мышью по номеру строки или установить подсветку в любой ячейке данной строки и нажать клавиши <Shift><Пробел>,

    Выделение всей колонки: щелкнуть мышью на имени столбца или выделить любую ячейку данного столбца и нажать клавиши <Ctrl><Пробел>.

    Выделение всей таблицы: щелкнуть мышью по левому углу бордюра (по кнопке расположенной между заголовками строк и столбцов) и нажать клавиши <Ctrl><Shift><Пробел>.

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

    • выделить мышью первую область, затем, удерживая клавишу <Ctrl>, перейти к следующей области и выделить ее;
    • выделить с помощью клавиатуры первую область, затем нажать клавиши <Shift><F8>, перейти во вторую область и выделить ее. Если фрагмент выделен ошибочно, и его выделение необходимо снять, то следует щелкнуть мышью в любой ячейке или нажать любую клавишу управления курсором.
    Редактирование табличных данных

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

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

    • щелкнуть два раза мышью в ячейке или нажать клавишу <F2>. Редактирование в этом случае осуществляется в самой ячейке;
    • щелкнуть по строке формул и редактировать данные в этой строке.

    Для удаления символов можно использовать клавиши <Delete> и <Backspace>, а для управления режимами Вставка / Замещение клавишу <Insert>. По завершению редактирования нажать клавишу <Enter> или кнопку с галочкой в строке формул.

    Для удаления содержимого ячейки (или нескольких выделенных ячеек) выделить очищаемую область или установить курсор на очищаемой от данных ячейке, нажать клавишу <Delete> или выполнить команду Очистить содержимое (контекстного меню).

    Для перемещения данных в другую позицию выделить их. Далее можно:

    • задать команду Главная $\to$Вырезать панели инструментов из группы Буфер обмена, установить курсор в ту позицию, куда должен быть перемещен выделенный блок, и нажать клавишу <Enter>;
    • установить указатель мыши на границу выделенного блока или ячейки таким образом, чтобы он превратился в стрелку, и переместить блок в нужную позицию.

    Для вставки строки в таблицу установить курсор в любую ячейку строки, перед, которой будет осуществляться вставка, и задать команду Главная alt=»$\to$» />Вставить alt=»$\to$» />Строки на лист.

    Для вставки столбца в таблицу установить курсор в любую ячейку столбца, перед, которой будет осуществляться вставка, и задать команду Главная alt=»$\to$» />Вставить alt=»$\to$» />Столбцы на лист.

    Для удаления строк или столбцов из таблицы установить курсор в любую ячейку, принадлежащую строке или столбцу, которые надо удалить, и задать команду Главная alt=»$\to$» />Удалить строки с листа (Удалить столбцы с листа). Можно отметить действие последней команды или последний ввод данных. Для этого надо задать команду Панель быстрого доступа alt=»$\to$» />Отменить.

    Можно осуществить повтор последней выполненной операции. Для этого следует задать команду Панель быстрого доступа $\to$Повторить.

    Копирование данных и формул

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

    Существует три способа копирования:

    1. Активизировать копируемую ячейку. Задать команду Главная $\to$Копировать из группы Буфер обмена или щелкнуть правой кнопкой мыши по ячейке, и выбрать Копировать из контекстного меню. Далее выделить ту область куда надо скопировать данные, и нажать клавишу <Enter> или нажать пиктограмму Главная $\to$Вставить или выбрать команду Вставить контекстного меню.
    2. Установить указатель мыши на Маркер заполнения (правый нижний угол) копируемой ячейки (при этом курсор принимает форму черного крестика) и, держа нажатой кнопку мыши, протянуть на всю ту область, куда надо скопировать данные. После этого снять выделение.
    3. Начиная с ячейки, где содержится копируемая информация, выделить диапазон ячеек куда надо скопировать данные. Выполнить команды Главная $\to$Заполнить $\to$Вниз (Вправо). Снять выделение.

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

    Автозаполнение

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

    1. Внести данные в первые две ячейки ряда и выделить их. Далее протянуть маркер заполнения по всему ряду. После того, как мышь будет отпущена, ряд заполнится данными.
    2. Ввести данные в первую ячейку ряда. Протянуть маркер заполнения по всему ряду, удерживая при этом нажатой клавишу <Ctrl>. Образуемая при этом последовательность чисел будет всегда иметь приращение 1.
    3. Ввести данные в первую ячейку ряда. Выделить все ячейки, которые должны быть заполнены данными. Задать команду Главная $\to$Заполнить из группы Редактирование и указать параметр Прогрессия. Далее задать тип заполняемого ряда (как правило, тип определяется автоматически), в поле Шаг указать приращение.

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

    Для создания списка задать команду Данные $\to$Проверка данных, вкладки Списки. Выбрать Новый список и ввести элементы этого списка. Нажать кнопку Добавить.

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

    Режимы вычислений

    Выбор ручного или автоматического режима вычислений

    Сразу после создания таблицы действует автоматический режим вычислений. Он означает, что при вводе любого значения в любую ячейку таблицы происходит автоматический просмотр всей таблицы и пересчет всех содержащихся в ней формул. Такой режим очень удобен для пользователя, поскольку позволяет не думать о подобных пересчетах при изменении табличных данных. Однако такой режим замедляет работу при больших объемах таблиц. В таких случаях на момент ввода или исправления данных удобней перейти в ручной режим расчета. Для этого задать команду из меню Формулы $\to$Вычисление $\to$Параметры вычислений $\to$Вручную. Вводимые при этом данные не будут сказываться на результатах вычислений по формулам.

    • Для пересчета формул при установленном ручном режиме нажать клавишу <F9>.
    • Для возврата к автоматическому режиму вычислений из меню Формулы $\to$Вычисление $\to$Параметры вычислений $\to$Автоматически.
    Вычисления с точностью «как на экране»

    При округлении числовых данных (с использованием кнопки уменьшить разрядность) в памяти компьютера по-прежнему сохраняются неокругленные числа. В этом случае последующие вычисления, например, суммирование округленных данных может привести к кажущимся ошибкам – суммируются не данные, отображенные на экране, а хранящиеся в памяти числа. Данная проблема может быть решена следующим образом: задать команду Главное офисное меню $\to$Параметры Excel и выберите категорию Дополнительно. В разделе При пересчете этой книги выберите нужную книгу и установите флажок Задать точность как на экране.

    Однако такое решение не может считаться универсальным, поскольку в ряде случаев требуется потеря точности, например, для вычисления сумм, но необходима полная точность, например, для расчета средневзвешенных величин. В этом случае для выполнения округления нужна не кнопка Уменьшить разрядность, а функция =ОКРУГ(). Для ее использования следует установить подсветку в ячейку, содержащую округляемую величину и ввести =ОКРУГЛ(РАСЧЕТНАЯ ФОРМУЛА; ТОЧНОСТЬ).

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

    Форматирование таблиц

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

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

    Для изменения ширины и высоты строки можно использовать одно из следующих действий:

    1. Выделить любую ячейку столбца (или строки), ширину которого требуется изменить, и задать команду Главная $\to$Ячейки $\to$Формат $\to$Ширина столбца и ввести число, определяющее количество знаков стандартного шрифта, или соответственно Формат $\to$Высота строки и ввести требуемые значения высоты в пунктах.
    2. Для подбора ширины столбца по самому длинному содержимому ячейки, необходимо установить указатель мыши на линию, расположенную правее имени столбца, и дважды щелкнуть мышью. Или дать команду Главная $\to$Ячейки $\to$Формат $\to$Автоподбор ширины.
    3. Для установки максимально необходимой высоты строки выполнить двойной щелчок по нижней границе строки в области заголовков строк, или дать команду Главная $\to$Ячейки $\to$Формат $\to$Автоподбор высоты.
    4. Установить указатель мыши на линию, правее имени столбца, и протянуть в требуемую сторону (влево – для сужения, вправо – для расширения). Аналогичным образом можно изменить высоту строки.
    5. Для возврата на стандартную ширину задать команду Формат $\to$Ширина по умолчанию.
    6. Для скрытия столбца выделить любую ячейку данного столбца и нажать кнопку Главная $\to$Ячейки $\to$Формат $\to$Скрыть или отобразить $\to$Скрыть столбцы той же команды.
    7. Для показа скрытого столбца можно: выделить столбцы слева и справа от скрытого; выделить любую его ячейку, а затем нажать кнопку Отобразить той же команды.

    Вводимый в ячейку длинный текст можно, например, расположить в несколько подстрок, увеличив тем самым высоту строки – для этого надо выделить соответствующую ячейку, затем задать команду щелчок правой кнопки мыши: Формат $\to$Ячейки, выбрать вкладку Выравнивание и включить параметр Переносить по словам.

    Иногда требуется введенный в ячейку длинный текст переразместить по нескольким ячейкам по вертикали. В этом случае следует выделить диапазон ячеек, в котором должен разместиться введенный текст, а затем задать команду Главная $\to$Заполнить и указать Выровнять.

    Для улучшения читаемости таблицы часто приходится использовать различные шрифтовые выделения: подбирать вид и размер шрифта, а также специальные эффекты (курсив, подчеркивание, полужирный шрифт). Указанные шрифтовые выделения могут быть распространены на всю таблицу, на выделенные ячейки или даже на выделенную часть отдельной ячейки. Далее можно: использовать кнопки панели форматирования для выбора вида шрифта, размера, начертания или задать команду Главная $\to$Ячейки $\to$Формат $\to$Формат ячеек и последующего выбора вкладки Шрифт, где могут быть заданы все те же шрифтовые эффекты.

    Изменение вида выравнивания

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

    Для изменения вида выравнивания по горизонтали и вертикали следует прежде всего, выделить нужную ячейку или выделить требуемые ячейки. Далее можно использовать кнопки панели форматирования или команду (после нажатия правой кнопкой мыши) Формат ячеек $\to$вкладка Выравнивание, где могут быть включены параметры:

    • по горизонтали: по левому краю, по правому краю, по центру и д.р.
    • по вертикали: по верхнему краю, по нижнему краю, по высоте или по центру и др.

    Для размещения текста по центру области из нескольких ячеек поступают следующим образом. Текст вводится в крайнюю ячейку области, затем, начиная с данной ячейки надо выделить всю область и задать команду (после нажатия правой кнопкой мыши) Формат ячеек $\to$вкладка Выравнивание, установить параметр Объединить.

    Обрамление элементов таблицы

    Сетка, показывающая на экране ячейки таблицы, является вспомогательной и не отображается при печати таблицы. Задать или отменить показ на экране сетки можно, включив / выключив параметр Разметка страницы команды Параметры листа alt=»$\to$» />Сетка alt=»$\to$» />Вид. Для обрамления таблицы линиями, которые реально будут изображаться при печати, следует выделить элемент (ячейку или группу ячеек), затем использовать кнопку Граница панели форматирования или команду Формат > Ячейки, вкладка граница, где можно выбрать тип и цвет линии.

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

    Изменение формата вывода чисел

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

    1. Задать команду Главная $\to$Число и во вкладке Число выбрать нужный формат.
    2. Нажать кнопку Денежный формат панели форматирования – выделенные числа будут переведены в денежный формат. К числу добавляется символ денежной единицы, а также два знака после запятой. Изменение денежной единицы проще всего осуществить, последовательно выполнив действия: Главная $\to$Число $\to$Финансовый числовой формат$\to$Выбрать другой вид валюты.
    3. Нажать кнопку Процентный формат % панели форматирования – выделенные числа будут переведены в процентный формат (число умножается на 100 и в конце концов ставиться знак процента – %).
    4. Нажать кнопку Формат с разделителемпанели форматирования – выделенные числа будут переведены в формат тысяч (каждые три знака числа отделяются пробелами и добавляются два знака после запятой).
    5. Нажать кнопку Увеличить разрядностьпанели форматирования – точность вычисления в выделенных ячейках увеличится на один разряд.
    6. Нажать кнопку Уменьшить разрядностьпанели форматирования – точность вычисления в выделенных ячейках уменьшится на один разряд.
    7. Для отмены заданного формата и возврата на обычный стиль отображения чисел задать команду Формат $\to$Стиль и в поле Имя стиляe выбрать Обычный.
    Использование возможностей автоформатирования

    Можно значительно сократить время оформления таблиц, если воспользоваться заранее созданными (встроенными) шаблонами форматирования таблиц. Эти шаблоны включают различные виды представления чисел и текста, выравнивание, рамки, узоры, ширину колонок, высоту строк. Для использования встроенных форматов надо выделить необходимый интервал (обычно не включающий заголовок таблицы), а затем задать команду Формат alt=»$\to$» />Автоформат и выбрать требуемый вид формата. Для отмены оформления, созданного автоформатом, снова выделите таблицу, задать команду Формат alt=»$\to$» />Автоформат и выбрать из списка форматов таблицы пункт нет.

    Операции над документами

    Запись табличного документа на диске

    При первом сохранении нового документа следует задать команду Главная $\to$Сохранить или нажать кнопку Сохранить стандартной панели и в поле файл ввести имя файла. При необходимости можно переопределить имена папки и диска.

    Для перезаписи на диск уже существующего файла задать команду Главная $\to$Сохранить или нажать кнопку Сохранить стандартной панели. При этом перезапись документа производится автоматически на тот же диск и в туже папку, где он находился, без появления диалогового окна.

    При необходимости создания копии существующего на диске табличного документа на другом диске, в другой папке или под другим именем следует задать команду Главная $\to$Cохранить как и изменить соответствующие атрибуты.

    Для записи на диске всех открытых в сеансе работы документов следует задать команду Главная $\to$Сохранить рабочую область. При этом все файлы будут записаны на диске как единое рабочее пространство под единым именем. Тогда по команде Открыть с заданием имени рабочего пространства они могут быть сразу все загружены на экран.

    Для включения режима автоматической перезаписи редактируемого файла на диск через определенные интервалы времени задать из меню Главное офисное меню $\to$Параметры Excel команду Автосохранение каждые, включить в поле мин указать интервал сохранения.

    Для выхода из режима создания или корректировки таблицы нажать клавиши <Ctrl><F4> или задать команду Главное офисное меню $\to$Закрыть. Если закрываемый документ не был записан предварительно на диск, то на экране появится соответствующий запрос.

    Вызов существующего табличного документа

    Загрузка таблицы на экран может производиться одним из следующих способов:

    • Если данный файл является одним из последних отредактированных или создаваемых ранее, то вызов можно произвести, выбрав его имя в нижней части меню Файл.
    • В общем случае любой файл может быть вызван па экран по команде Главное офисное меню $\to$Открыть или нажатии на кнопку открыть стандартной панели. Далее нужно открыть нужную паку или диск, используя кнопки перехода, выбрав его имя в поле Файл или выбрать это имя из списка.
    • Если при записи файла на диск он был защищен паролем, то по запросу системы ввести пароль.
    Создание нового табличного документа

    Перед созданием нового документа рекомендуется определить количество листов в новой книге, выбрав Главное офисное меню $\to$Параметры Excel и в поле Число листов задать нужное число листов в рабочей книге.

    Для создания нового документа задать команду Главное офисное меню $\to$Создать, указать значок Новая книга, нажать ОК.

    Одновременная работа с несколькими табличными документами

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

    1. Если на экране видна часть окна с нужным документом, то достаточно щелкнуть мышью в любой позиции этого окна.
    2. Вызвать меню Окно и в нижней его части выбрать имя нужного документа.
    3. Нажимать клавиши <Ctrl><F6> до тех пор, пока на экране не появится нужный документ.

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

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

    • Если копируются обычные данные ( то есть, текст или числа, но не данные, рассчитанные по формулам), то следует нажать кнопку Вставить стандартной панели или задать команду Главная > Вставить.
    • Если выполняется копирование числовых значений, рассчитанных по формулам, то следует вызвать команду Главная > Специальная вставка и включить параметр Значения.
    • Если требуется поддерживать связь скопированных данных во второй таблице с данными исходной таблицы, то следует вызвать команду Главная > Специальная вставка и нажать кнопку Вставить связь.
    • Если та область таблицы, куда следует выполнить копирование, уже заполнена данными, и надо не просто заместить эти данные новыми, а сложить их, то следует вызвать команду Главная alt=»$\to$» />Специальная вставка alt=»$\to$» />Сложить.
    Предварительный просмотр таблицы перед печатью

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

    1. Кнопки Далее и Назад позволяют листать страницы. Аналогично действуют клавиши <PgUp> и <PgDn>.
    2. Нажатие кнопки Масштаб приводит к укрупнению изображения таблицы на экране. Повторный щелчок мыши по таблице восстанавливает предыдущий вид.
    3. Кнопка Печать осуществляет переход в диалоговое окно печати, в котором может быть задана область печати – вся таблица (параметр все), определенные страницы (параметр страницы и поля с и по) или выделенная область, а также количество экземпляров (параметр Число копий).
    4. При нажатии на кнопку Параметры страницы на экране появляется диалоговое окно, где можно:
      • Нажать кнопку Поля и определить поля, включить центрирование по вертикали и горизонтали, указать расстояние от края листа до колонтитула.
      • Нажать кнопку Лист. При помощи параметров Печать можно отключить или включить печать табличной сетки и заголовков строк и столбцов. При помощи окна Последовательность вывода страниц можно задать порядок нумерации страниц таблицы.
      • Для ввода повторяющегося текста на каждом листе в верхней или нижней его части нажать кнопку Колонтитулы. Далее для ввода в колонтитул предусмотренного программой колонтитула вызвать список для параметра верхние колонтитулы или нижние колонтитулы. В зависимости от того, где должен быть расположен вводимый текст колонтитула (в левой части листа, по центру или в правой части листа) перевести курсор в соответствующий блок. Для задания определенного шрифта следует нажать кнопку с буквой А, а затем выбрать шрифт. Также может быть указана некоторая служебная информация: текущий номер страницы, общее количество страниц в тексте, текущая дата, текущее время, имя файла.
      • Нажать кнопку Страница и указать ориентацию бумаги при печати (книжная или альбомная ). В окне Масштаб с помощью параметра Установить можно увеличить или уменьшить в заданной пропорции таблицу, а включив параметр Разместить на, можно указать, на скольких страницах должна быть размещена таблица. С помощью параметра Размер бумаги может быть указан размер бумаги, используя кнопки Свойства.
      • Нажатие кнопки Поля приводит к показу полей на листе бумаги. Кроме того, в этом режиме поля могут быть изменены: достаточно установить указатель мыши на одно из полей и протянуть его.
    Печать

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

    ЗАДАНИЕ

    1. Разработайте таблицу согласно варианту задания (используйте условные данные). Изучите различные способы ввода формул.
    2. Изучите способы выделения, копирования и переноса ячеек, строк и столбцов. Получить копию созданной таблицы, скопировав ее по частям.
    3. Поясните, как и почему изменились формулы в скопированной таблице.
    4. Отформатируйте исходную таблицу «вручную», используя:
    5. форматы данных (числовой, денежный, процент, дата и т.д.);
    6. обрамление и заполнение ячеек;
    7. различные форматы шрифтов;
    8. выравнивание;
    9. изменение высоты строк и ширины столбцов.
    10. Отформатируйте вторую таблицу, используя команду Автоформат из меню Формат.
    11. Подготовьте к печати одностраничный отчет. С этой целью: оформите рабочий лист в виде фирменного бланка, внедрив в левом верхнем углу листа логотип и указав название, адрес и телефон фирмы;
    12. Создайте нижний колонтитул, включающий номер страницы в центре, а справа и слева – фамилии авторов отчета.

    Варианты заданий

    Сравнительная таблица розничных цен на продовольственные товары по городам Северного Кавказа на «__» ____20_г. (руб. за 1 кг).

    Товар Краснодар Ростов Ставрополь Майкоп Нальчик Средняя цена
    Говядина
    Свинина
    Птица
    Рыба

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

    Данные о продаже автомобилей в 20_г.

    Марка 1 квартал % 2 квартал % 3 квартал % 4 квартал %
    БМВ
    Форд
    Ауди
    Рено
    Тойота
    Всего

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

    Температура воздуха в городах мира с 1 по 7 января 20___г.

    Дата Москва Рим Париж Лондон Мадрид
    01.01.2013
    02.01.2013
    03.01.2013
    04.01.2013
    05.01.2013
    06.01.2013
    07.01.2013
    Средн.Температура
    Общая средняя. по всем городам

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

    Перечень доходов и расходов за 1-е полугодие.

    Статья\Месяц Янв. Февр. Март Апр. Май Июнь
    Доходы
    Оклад
    Премия
    Совместительство
    Всего доходов
    Расходы
    Жилье
    Кредит
    Питание
    Налоги
    Другие расходы
    Всего расходов
    Остаток
    Остаток за полугодие

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

    Выручка от продажи книжной продукции в 200_ г.

    Название книги 1 кв. 2 кв. 3 кв. 4 кв. Процент за год
    Всего за год

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

    Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».

    Номер кандидата Эксперты Сумма баллов Процент
    1 2 3 4
    №1
    №2
    №3
    №4
    №5
    Всего:

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

    Прайс-лист фирмы «Трианон» на 22 декабря 200_ г.

    Наименование товара Розничная цена, руб. От 10 шт. От 100 шт. Свыше 1000 шт. Диллеры

    Формулы в ячейках столбцов «От 10 шт. . Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей колонкой.

    Результаты зимней сессии студентов группы ______

    Фамилия И.О. Математика Информатика Иностр. язык Маркетинг Сумма Средний балл

    В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.

    Анализ цен на товары в конкурирующих фирмах.

    Наименование товара Название фирмы
    ВЛАДОС ОК Контраст
    Сумма, руб.
    Изменение курса валют за период 1 по 5 марта 2010г.

    Дата Евро Доллар Евро/Доллар
    1 марта 2010
    2 марта 2010
    3 марта 2010
    4 марта 2010
    5 марта 2010
    Средний за период
    Процент роста

    В столбце «Евро/Доллар» должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего значения в столбце, в строку «Процент роста» – формулы для определения процентного прироста курса на 5 марта относительно курса на 1 марта 2010г.

Ссылка на основную публикацию