Green-sell.info

Новые технологии
23 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Конструктор таблиц в excel

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

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

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

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

  1. Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
  2. В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].

  1. Появится диалоговое окно, в котором Excel автоматически предложит границы диапазона данных для таблицы

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

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

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

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

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

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

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

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

Вычисления в таблицах

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

  1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

  1. В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.

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

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

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

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

  1. Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
  2. В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
  3. OK.

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

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

  1. На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
  2. Выбрать вкладку Преобразовать в диапазон [Convert to Range].

  1. Нажать на кнопку Да [Yes].

Умные таблицы в Excel

Видео

Постановка задачи

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

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table) :

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

  1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

  • В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data) ).
  • При добавлении новых строк в них автоматически копируются все формулы.
  • При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
  • При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

    Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

  • К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
    • =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
    • =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
    • =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
    • =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
    • =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.

    (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

    В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Умные Таблицы Excel – секреты эффективной работы

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

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

    Как создать Таблицу в Excel

    В наличии имеется обычный диапазон данных о продажах.

    Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

    Есть горячая клавиша Ctrl+T.

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

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

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

    Структура и ссылки на Таблицу Excel

    Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

    Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

    А также при наборе формулы вручную.

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

    =Отчет[#Все] – на всю Таблицу
    =Отчет[#Данные] – только на данные (без строки заголовка)
    =Отчет[#Заголовки] – только на первую строку заголовков
    =Отчет[#Итоги] – на итоги
    =Отчет[@] – на всю текущую строку (где вводится формула)
    =Отчет[Продажи] – на весь столбец «Продажи»
    =Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

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

    Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

    то она автоматически переделается в

    Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

    А теперь о том, как Таблицы облегчают жизнь и работу.

    Свойства Таблиц Excel

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

    2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

    Очень удобно, не нужно специально закреплять области.

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

    4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.


    Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

    5. Новые столбцы также автоматически включатся в Таблицу.

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

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

    Настройки Таблицы

    В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

    С помощью галочек в группе Параметры стилей таблиц

    можно внести следующие изменения.

    — Удалить или добавить строку заголовков

    — Добавить или удалить строку с итогами

    — Сделать формат строк чередующимися

    — Выделить жирным первый столбец

    — Выделить жирным последний столбец

    — Сделать чередующуюся заливку строк

    — Убрать автофильтр, установленный по умолчанию

    В видеоуроке ниже показано, как это работает в действии.

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

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

    Однако самое интересное – это создание срезов.

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

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

    Для фильтрации Таблицы следует выбрать интересующую категорию.

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

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

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

    Ограничения Таблиц Excel

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

    1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

    2. Текущую книгу нельзя выложить для совместного использования.

    3. Невозможно вставить промежуточные итоги.

    4. Не работают формулы массивов.

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

    Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

    Множество других секретов Excel вы найдете в онлайн курсе.

    12 простых приёмов для ускоренной работы в Excel

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

    Автор проекта «Планета Excel», разработчик и IT-тренер.

    1. Быстрое добавление новых данных в диаграмму

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

    2. Мгновенное заполнение (Flash Fill)

    Предположим, у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы сделать это, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно. Подобным образом можно извлекать имена из email, склеивать ФИО из фрагментов и так далее.

    3. Копирование без нарушения форматов

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

    Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

    4. Отображение данных из таблицы Excel на карте

    В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.

    После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

    5. Быстрый переход к нужному листу

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

    6. Преобразование строк в столбцы и обратно

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

    1. Выделите диапазон.
    2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
    3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose). В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

    7. Выпадающий список в ячейке

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

    1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
    2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data → Validation).
    3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
    4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

    8. Умная таблица

    Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

    1. Автоматически растягивается при дописывании к ней новых строк или столбцов.
    2. Введённые формулы автоматом будут копироваться на весь столбец.
    3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
    4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

    9. Спарклайны

    Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

    После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.

    10. Восстановление несохранённых файлов

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

    На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

    В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

    В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

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

    11. Сравнение двух диапазонов на отличия и совпадения

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

    1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
    2. Выберите на вкладке «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
    3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

    12. Подбор (подгонка) результатов расчёта под нужные значения

    Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!

    Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

    Если этот обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

    Читать еще:  Как поставить процент в excel
    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector