Tw-city.info

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

Работа с листами в excel

Excel. Урок 11. Работа с несколькими листами: однотипные данные, формулы

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

1:20 Добавление листа

В рабочей книге три рабочих листа. Добавим новый лист для итога. Можно сделать это нажатием на ярлычок вставки листа (рядом с ярлычками существующих листов).

1:52 Переименование листа

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

«Итог», «Магазин 3», «Магазин 2», «Магазин 1»

2:50 Перемещение листов

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

«Магазин 1», «Магазин 2», «Магазин 3», «Итог»

3:18 Приемы работы с листами

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

4:16 Работа со всеми листами одновременно

Четыре листа у нас есть. Теперь приступим к заполнению листов однотипной информацией. Можно воспользоваться обычным приемом копирования вставки, а можно все сделать еще быстрее. Выделяем все листы — щелкаем правой кнопкой и даем команду «Выделить все листы». Все ярлычки листов стали светлыми, и активны все 4 листа. Начинаем вводить данные сразу во все таблицы (вводим на одном листе, на других они дублируются).

В А1 пишем «Магазин № 1».

В B1 пишем: «Данные по кварталам».

5:23

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

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


6:28 Улучшаем внешний вид

Изменяем размер столбца. Заголовок в строке 1 увеличиваем и центрируем. Заголовок в строке 2 центрируем. Заголовки столбцов и первый стлбец таблицы делаем полужирными.

7:11 Добавляем заливку ячеек, чтобы таблица лучше воспринималась.

7:35 Очерчиваем границы

7:50

Сейчас все таблицы заполнены одинаковыми данными, и необходимо подредактировать каждый лист. Перемещаемся в область ярлычков. Нажимаем правую кнопку и выбираем команду «Разгруппировать листы«. Ярлычки приняли прежний цвет. Перемещаясь между листами, редактируем каждый из них. В заголовочных строках меняем номер магазина и в итоговом листе «Магазин № 1» заменяем на «Итог».

8:45 Можно было бы группировать листы несколько иным способом. Выделяем первый лист зажимаем клавишу Ctrl, щелкаем на второй лист и третий лист. В данном случае я выделяю не все листы а лишь часть из них (лист «Итог» пока не выделяем).

9:20 в выделенных листах вводим формулы. Здесь пока нет исходных данных но как только появятся числа, появится результат. В В8 выполняем автосуммирование.

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

Выполняю автозаполнение вправо до столбца Е включительно.

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

= B4-B8

10:32

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

11:00

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

11:20 Переходим на лист «Итог» и подсчитаем сумму по 3-м магазинам по каждому кварталу.

Активируем ячейку В4 вводим знак «=» (начало формулы) и переходим на ярлычок первого магазина. Щелчок по ячейке В4 на этом листе. В строке формул появляется имя ячейки, которому предшествует название листа

Отсутствие наглядности при работе с несколькими листами иногда сбивает. Поэтому смотрите на строку формул. Жмем на знак «+», переходим на следующий лист «Магазин 2» жмем на ячейку B4. Опять жмем на «+», переходим на лист «Магазин 3», Нажимаем на B4 и затем на Enter. Никаких лишних кликов мышью ни в коем случае совершать нельзя!

=’Магазин 1′!B4+’Магазин 2′!B4+’Магазин 3′!B4

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

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

Читать еще:  Word нумерация страниц без титульного листа

14:10

Результат формулы не помещается в ячейку числа отображаютс #####

Двойной щелчок по границе между ячейками и формула приобретает удобочитаемый вид.

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

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

Работа с листами в Microsoft Excel

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

Пользователи могут упорядочить данные в пределах одной книги

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

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

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

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

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

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

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

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

Работа с листами в Excel

&nbsp&nbsp&nbsp&nbsp&nbspБывают домовые, а я — ОФИСНЫЙ!

&nbsp&nbsp&nbsp&nbsp&nbspОблегчаю трудовые будни!

Нам нужно выяснить, с какими клиентами нужно перезаключить договор в 2011 году в связи с окончанием его срока… дальше

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

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

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

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

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

Читать еще:  Как написать дроби в word

Для того, чтобы одновременно убрать все формулы нужно: 1. Выделить всю область листа, для чего необходимо нажать левой клавишей мыши на самый верхний левый угол серого поля; 2… дальше

Абсолютная ссылка дает возможность ссылаться на одну и ту же ячейку при копировании формулы (в отличие от относительной ссылки). Таким образом, мы можем в первой строке сослаться на конкретную ячейку, скопировать ее и протянуть до конца списка. Весь список будет ссылаться на ту ячейку, в которой действует абсолютная ссылка. Соответственно при изменении этой ячейки меняется весь столбец или строка. дальше

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

Брать или не брать кредит? Как не попасть в долговую яму

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

ВСЕ УРОКИ

Таблица расчета основных коэффициентов, оценивающих эффективность деятельности компании*
СКАЧАТЬ

* При заполнении форм № 1 и № 2 Баланса, коэффициенты будут считаться автоматически. Для того, чтобы продолжить приложенную таблицу, читайте ниже приведенную статью.

Работа с листами в Excel. Коэффициенты эффективности работы предприятия в Excel

Для расчета коэффициентов придется использовать данные из разных отчетов (баланс: форма № 1 и форма № 2), которые находятся в разных файлах.

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

Предположим, что у нас есть файл с Формой № 1 Баланса, где хранятся балансы за все периоды. Наша задача скопировать нужный нам лист и перенести в новый файл.

Копирование листа из одного файла в новый файл

Активизируем нужный нам лист (внизу страницы нажимаем на него левой клавишей мыши), вызываем меню правой клавишей мыши и выбираем «Переместить/скопировать»:

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

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

Переименуем лист для удобства работы в «форма1». Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):

и переименуем в «форма1»:

Лист готов. Таблицу можно продолжить, добавляя значения следующих периодов.

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

Копирование/перемещение листа из одного файла в другой

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

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

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

Переименуем лист для удобства работы. Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):

и переименуем в «форма2»:

По аналогии с листом «Форма1» мы будем добавлять в этот лист данные за все следующие периоды.

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

Делается это очень просто. Вызываем «Вставку» в главном меню и выбираем «Лист»:

Подготовим таблицу для занесения данных и возьмем для примера расчет Коэффицента оборачиваемости товарных запасов:

Как видим, один из показателей нужно взять из Формы № 1 (Среднегодовое значение товарных запасов), а другой — из Формы № 2 (Себестоимость проданных товаров).

  1. активизируем нужную ячейки и пишем в ней знак «=»
  2. выделяем ячейку со значением себестоимости на листе «Форма2»

  • пишем знак деления — «/»
  • открываем первую скобку «(«, в которой будем рассчитывать среднегодовое значение товарных запасов), и вторую скобку «(«, в которую заключим сумму значений товарных запасов за период
  • открываем лист «Форма1» и добавляем после двух открытых скобок сумму товарных запасов на 5 дат
  • закрываем первую скобку «)» и делим сумму товарных запасов на 5 дат на «5» (вычисляем среднее значение)
  • закрываем вторую скобку «)» и нажимаем ENTER
  • Получаем следующее выражение:

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

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

    Как скопировать формулы, ссылающиеся на разные листы

    Для начала скопируем готовые формулы, для чего активизируем все ячейки с формулами и правой кнопкой мыши вызовем «Копировать»

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

    Как сделать массовую замену формул

    Осталась самая малость. Скопированные формулы повторяют имеющиеся, но ссылаются не на те ячейки, которые нам нужны. Это происходит из-за того, что в одном листе (Форма2) для расчета показателя за год используется всего одна ячейка, а в другом листе (Форма1) — 5 ячеек.

    Читать еще:  Word начать нумерацию со второй страницы

    Т.е. в части ссылок на лист «Форма2» ссылки при копировании автоматически идут на нужные ячейки, а в листе «Форма1» нам придется передвинуть формулы на 4 ячейки (причем речь идет о буквенных обозначениях, цифровые меняться не будут).

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

    В нашем случае, второй столбик после копирования формулы ссылается в листе «Форма1» на ячейки J, K, L, M, N, в то время как для правильного результата должны ссылаться на M, N, O, P, Q. Как видим, две буквы повторяются. Их мы оставим. Значит, нам останется поменять J, K, L на O, P, Q.

    А дальше все проще простого. Выделяем нужный столбец по серому полю (обязательно, иначе мы, не желая того, можем поменять значения других ячеек), вызываем функцию «поиска и замены» и меняем J на O, K на P, L на Q и т.д.

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

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

    ШКОЛА ПРОГРАММИРОВАНИЯ

    Вы здесь

    Сообщение об ошибке

    Работа с листами, столбцами и строками в MS Excel 2003

    Структура книги Excel

    Итак, запустив приложение Excel, мы видим таблицу и три вкладки листов (Лист1, Лист2, …). Количество листов в книге Excel ограничивается технической возможностью системы т.е. памятью. Ради интереса с помощью макроса я создал еще 2000 листов (затратил секунд 5 времени), остановки не произошло. Листов в книге 2003. А вот со строками и столбцами у Excel 2003 проблема, хотя для решения стандартных задач вполне хватает. Количество строк ограниченно 65536 (2 в 16 степени), а столбцов 256 (2 в 8-ой). В 2007-ом, кол-во строк уже чуть более миллиона. Все строки пронумерованы от 1 до 65536, столбцы в обычном режиме обозначены буквами латинского алфавита (“A-Z”, далее “AA-AZ”, “BA-BZ” и т.д. до “IV”).

    На пересечении строк и столбцов находятся ячейки, основная рабочая единица Excel. Количество ячеек на листе можно посчитать перемножив строки на столбцы т.е. 65536х256 = 16 777 216 шт. Каждая ячейка в Excel имеет свой адрес. В обычном режиме, например «C5» ссылает на ячейку в третьем столбце, пятой строке (принцип шахматной доски или морского боя). Адреса используются для задания формул при расчетах, в функциях и т.д. Например, ячейка A1 =3, B3 = 5, задав формулу «=A1*B3» в ячейке C1 будет получен результат 8.

    Листы книги можно:

    Добавить – главное меню Вставка — Лист

    Удалить – главное меню Правка — Удалить Лист

    Переименовать – главное меню Формат – Лист – Переименовать

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

    Скрывать — главное меню Формат – Лист – Скрыть ( для отображения Формат – Лист – Отобразить)

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

    Строки и столбцы Excel

    Над строками и столбцами можно производить следующие операции:

    Добавление — главное меню Вставка – Строка (Столбец) или нажать сочетание Ctrl +

    Удаление – главное меню Правка – Удалить или Ctrl — . Для того чтобы выделить полностью столбец(ы) или строку(и), достаточно кликнуть (для выделения диапазона протянуть) левой кнопкой мыши по номерам строк или по буквенному обозначению столбцов.

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

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

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

    Для наглядности, можете просмотреть видео: Excel 2003. Работа со строками.

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