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

Как в Экселе добавить лист и другие важные действия с листами

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

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

Умение правильно обращаться с вкладками поможет вам делать удобные, аккуратные и компактные расчеты.

Переключение между активными рабочими листами Excel

Аналогично рабочим окнам, только один рабочий лист Эксель может быть активен для работы. Активировать рабочий лист можно так:

• Мышью выбрать вкладку нужного вам рабочего листа. После клика по его ярлыку, лист будет активированВкладки рабочих листов
• Использовать горячие клавиши Ctrl+PgUp и Ctrl+PgDn для циклического пролистывания вкладок

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

Чтобы увидеть ярлыки, которые не влезли – используйте стрелки прокрутки ярлыков (см. рис.). Еще один способ —  уменьшить длину горизонтальной полосы прокрутки, потянув её границу вправо.

Таким, образом, освободится больше места для отображения вкладок.

Стрелки прокрутки ярлыков

Добавление нового рабочего листа

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

• Кликнуть на ярлыке Новый лист справа от ярлыка последнего рабочего листа. Листик будет добавлен в конец списка
• Кликнуть на вкладке листа правой кнопкой мыши и в контекстном меню выбрать команду Вставить. Новый лист появится перед тем, на который вы кликнулиВставка нового листа
• Использовать комбинацию клавиш Shift+F11 Лист будет добавлен перед активной вкладкой

Переименование рабочего листа Эксель

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

Так же, можно нажать правой кнопкой мыши на вкладке и выбрать команду Переименовать. Эффект будет такой же, как и от предыдущего способа.

Переименование листа Эксель

Как скопировать или переместить лист

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

• Кликните правой кнопкой мыши на нужном листе и в контекстном меню выберите Переместить или скопировать. В открывшемся окне выберите документ, в который нужно вставить лист и его расположение в этом документе. По умолчанию лист будет перенесен. Если нужно сделать копию – поставьте галочку напротив Создать копию.Перемещение рабочего листа
• Перетянуть ярлык мышью в нужное расположение текущей или другой рабочей книги. По умолчанию, документ будет перемещён. Если нужно сделать копию – зажмите клавишу Ctrl и тяните. Курсор будет изменен на миниатюру листа и небольшую стрелку, помогающую выбрать, куда вставить листик

Если в документе уже есть лист с таким именем, как у перемещаемого – Excel переименует последний. Например, «Книга1» станет «Книга1(2)».

Скрыть или удалить рабочий лист MS Excel

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

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

Скрыть лист Эксель

Чтобы отобразить скрытый лист – кликните правой кнопкой мыши на любой вкладке и выберите Показать. В появившемся диалоговом окне выберите нужный лист и нажмите Ок. Ярлык листа снова появится в списке.

Показать лист

Если лист вам не нужен и не понадобится в будущем – его лучше удалить. Для этого жмите правой кнопкой мыши на ярлык листа.  В контекстном меню выбирайте команду Удалить. Осторожно, удаление листа нельзя отменить, есть риск случайно удалить нужные данные. Формулы, ссылающиеся на удалённые листы, придётся исправлять вручную.

Действия над несколькими листами

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

1. Кликнем на одном из нужных листов для его активации
2. Зажмем Ctrl и кликаем по остальным листам, которые будут обрабатываться в группе. В адресной строке к названию файла добавится текст [Группа]
3. Выполняем те операции, которые будут общими для всех выделенных листов
4. Когда все работы выполнены — снимем группировку. Для этого нужно кликнуть правой кнопкой мыши на ярлыке любого выделенного листа и в контекстном меню выбрать Разгруппировать.

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

Вот такие операции вы можете совершать над объектом «Рабочий лист».

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

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

Excel в Delphi. Методы объекта WorkSheet (лист)

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

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

План статьи:

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

1. Объекты Microsoft Excel

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

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

WorkBook:=WorkBooks.Item[index]

где index — порядковый номер рабочей книги в коллекции WorkBooks.

И на этом наше первое  знакомство с Excel закончилось. Между тем, каждая отдельная рабочая книга может содержать N листов, каждый из которых состоит из N ячеек, которые могут быть объединены в объекты Range.

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

2. Как активировать лист рабочей книги Excel?

Как Вы знаете при создании пустой рабочей книги Excel автоматически добавляет в эту книгу 3 пустых листа. Чтобы работать с конкретным листом (WorkSheet или просто Sheet) этот лист необходимо активировать. Один из способов активации листа рабочей книги Excel в Delphi выглядит следующим образом:

function ActivateSheet(WBIndex:integer; SheetName:string):boolean; var i:integer; begin Result:=false; try if WBIndex>MyExcel.WorkBooks.Count then raise Exception.Create('Задан неверный индекс для WorkBooks. Активация листа прервана') else begin for i:=1 to MyExcel.

WorkBooks[WBIndex].Sheets.Count do if AnsiLowerCase(MyExcel.WorkBooks[WBIndex].Sheets.Item[i].Name)=AnsiLowerCase(SheetName) then begin MyExcel.WorkBooks[WBIndex].Sheets.Item[i].Activate; Result:=true; break; end; end; except raise Exception.

Create('Активация листа завершена с ошибкой'); end; end;

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

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

Также, если вы уверены, что в данный момент времени активна необходимая Вам рабочая книга, то можно избежать использования лишней переменной (индекса рабочей книги WBIndex) и активировать лист вот так:

MyExcel.ActiveWorkBook.Sheets.Item[i].Activate;

Теперь рассмотрим более подробно методы, используемые объектом WorkSheet.

3. Методы  листа книги Excel

Теперь рассмотрим более подробно объект WorkSheet.

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

Всего объект WorkSheet насчитывает 30 различных методов, которые Вы можете использовать.

Рассмотрим теперь каждый из представленных на рисунке методов.
1. Activate — Делает текущий лист активным. Работу метода мы уже с Вами рассмотрели.
2. Calculate — рассчитывает все открытые книги, конкретный лист в книге, или указанный диапазон ячеек на листе.

Возможные варианты вызова метода:

расчёт всех открытых в данный момент книг;

MyExcel.Calculate

расчёт отдельного листа книги:

MyExcel.ActiveWorkBook.ActiveWorkSheet.Calculate

в данном случае будет произведен расчёт текущего активного листа активной книги.

MyExcel.ActiveWorkBook.ActiveWorkSheet.UsedRange.Columns(«A:C»).Calculate

при таком вызове будет произведен расчёт всех формул в столбцах A, B и C

3. CheckSpelling — проверка орфографии на выбранном листе.

Вызов метода:

expression.CheckSpelling(CustomDictionary, IgnoreUppercase, AlwaysSuggest, SpellLang)

При этом в качестве expression должен выступать лист (WorkSheet).

Параметры, используемые при вызове:

CustomDictionary : Variant — cтрока, указывающая имя файла пользовательского словаря, который будет рассмотрен, если слово не найдено в основном словаре. Если этот аргумент опущен, то будет использоваться словарь по-умолчанию.

IgnoreUppercase : Variant — true, если необходимо, чтобы Microsoft Excel пропускал слова, которые написаны прописными буквами. False для того, чтобы Microsoft Excel проверить все слова. Если этот аргумент опущен, то используются текущие настройки.

AlwaysSuggest : Variant — true, для того, чтобы Microsoft Excel отображал список предложений других вариантов написания при обнаружении неправильного написанного слова. False, чтобы Microsoft Excel ожидал ввода правильного написание. Если этот аргумент опущен, то используются текущие настройки.

Пример вызова метода CheckSpelling:

MyExcel.ActiveWorkBook.ActiveWorkSheet.CheckSpelling(CustomDictionary:=EmptyParam, IgnoreUppercase:= false, AlwaysSuggest:=EmptyParam, SpellLang:=1033)

в этом случае MS Excel проверит текущий лис, включая проверку слов из прописных букв. Язык проверки — русский.

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

Причём для того, чтобы пропустить какой-либо параметр, мы использовали EmptyParam (пустой параметр), который в Delphi представляет собой переменную типа OleVariant.

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

4. SaveAs — сохраняет все изменения в файле.

Вызов метода:

expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru,TextCodepage, TextVisualLayout, Local)

При этом в качестве expression должен выступать лист (WorkSheet).

Параметры, используемые при вызове:

FileName : Variant — строка, представляющая собой имя сохраняемого файла. Необходимо указывать полный путь к файлу, иначе файл будет сохранен в папку Excel.

Password : Variant — пароль для файла (не более 15 символов). Пароль чувствителен к регистру.

WriteResPassword: Variant — пароль, который будет использоваться для внесения изменений в рабочую книгу. Если пароль не будет указан, то книга будет открыта в режиме «Только для чтения»

ReadOnlyRecommended: Variant — если параметр равен True, то при открытии файла на дисплее появится сообщение, в котором рекомендуется, что бы файл был открыт только для чтения.

CreateBackup: Variant — True, для того, чтобы создать резервный файл

AddToMru: Variant — True, для того, чтобы добавить имя сохраняемого файла в список недавно открытых файлов в главном меню Excel

TextCodepage и TextVisualLayout : Variant — в настоящее время не используются и сохранены для обеспечения обратной совместимости

Local : Variant — не обязательный параметр, по-умолчанию равен false.

Пример вызова метода SaveAs:

MyExcel.ActiveWorkBook.ActiveWorkSheet.SaveAs('C:MyExcelFile.xls')

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

5. Delete — удаляет текущий лист WorkSheet

Используется без параметров. Пример Вызова метода:

MyExcel.ActiveWorkBook.ActiveWorkSheet.Delete

6. Select — выделение листа.

Вызов метода:

expression.Select(Replace)

Параметры, используемые при вызове:

Replace : Variant — true, чтобы заменить текущее выделение указанного объекта. False — продлить нынешний отбор включая все ранее выбранные объекты. По умолчанию используется True.

Т.е. не стоит путать активный лист — тот который отображается в данный момент в книге и выделенный — выделить Вы можете хоть 10 листов (в Excel это делается с зажатым Ctrl), но активный — только один.

Пример вызова метода Select

MyExcel.ActiveWorkBook.WorkSheets.Item[3].Select

Выделяет третий лист книги

7. Copy — копирует лист в другое место в рабочей книге.

Вызов метода:

expression.Copy(Before, After)

Параметры, используемые при вызове метода:

Before : Variant — лист до которого будет вставлен копируемый лист.

After : Variant — лист после которого будет вставлен копируемый лист.

При этом, если вы используете параметр Before, то вы не должны использовать After и наоборот. Обращу Ваше внимание, что указывается именно ЛИСТ, а не индекс листа.

Пример вызова метода Copy:

MyExcel.ActiveWorkBook.WorkSheets.Item[1].Copy(After:=MyExcel.ActiveWorkBook.WorkSheets.Item[2])

В этом случае первый лист будет скопирован и вставлен после третьего.

8. Paste — вставляет содержимое буфера обмена на лист

Вызов метода:

expression.Paste(Destination, Link)

Параметры, используемые при вызове метода:

Link : Variant — True, для того чтобы установить связь с источником вставляемых данных. Если этот параметр задан, Destination не может быть использован. Значение по умолчанию False.

Пример вызова метода Paste:

MyExcel.ActiveWorkBook.ActiveSheet.Paste(Destination:=MyExcel.ActiveWorkBook.ActiveSheet.Range(«D1:D5»))

В этом случае содержимое буфера будет вставлено в столбец D в строки с 1 по 5. Следует отметить, что если содержимое буфера не удовлетворяет условию, например в буфере только 1 число, то вызывается исключительная ситуация.

9. ChartObjects — возвращает объект, который представляет собой либо одну диаграмму (объект ChartObject) или совокупность всех диаграмм (объект ChartObjects) на листе.

Вызов метода:

expression.ChartObjects[Index]

Параметры, используемые при вызове метода:

index: Variant — индекс диаграммы в коллекции (нумерация начинается с 1)

Пример вызова метода:

MyExcel.ActiveWorkBook.ActiveSheet.ChartObjects[1]

В этом случае будет получена первая диаграмма на листе.

10. Move — перемещение листа. Этот метод аналогичен методу Copy. Различие лишь в том, что после вcтавки копируемый лист удаляется из книги.

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

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

Книжная полка

Все исходники, рассмотренные в статье можно скачать со страницы «Исходники»

Как работать в Microsoft Excel

В продолжение 18 урока, я расскажу, как работать в Microsoft Excel, электронной таблице, входящей в пакет Microsoft Office.

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

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

Возможно поэтому, с выпуском в 1979 году первой такой программы, которая называлась VisiCalc, для микрокомпьютера Apple II, началось широкое использование их не только для развлечения, но и для практической работы с расчетами, формулами, финансами.

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

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

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

Запуск программы Microsoft Excel

Для запуска программы нажимаем кнопку – Пуск – Все программы – Microsoft Office – Microsoft Excel (если у Вас бесплатный пакет WPS Office – Пуск – Все программы – WPS Office – WPS Spreadsheets).

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

Открывается окно программы рис.1. Так же как и Word можно выделить несколько областей – они помечены цифрами.

1. 1. Вкладки.
   2. Инструменты вкладки.
   3. Область навигации листа.
   4. Область формул.
   5. Колонки.
   6. Строки.
   7. Область разметки страницы.
   8. Масштаб изображения.
   9. Рабочая область листа.
   10. Область навигации книги.

Теперь немножко подробнее.

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

• текстовый
• числовой
• денежный
• даты
• время
• процентный
• логические значения
• формулы

Текст, как правило, необходим не для расчета, а для пользователя. Для того, что бы данные в ячейке стало текстовым, достаточно в начале строки поставить знак апострофа – ‘. То, что будет после апострофа, будет восприниматься как строка, либо выбрать формат ячейки “Текстовый”. Рис.2
Числа, это основной объект, над которыми производятся вычисления. рис.

3
Дата и время – отображение даты и времени. Над датами можно производить вычисления. рис.4 (разные форматы отображения)
Логические значения могут принимать два значения – “Истина (True)” и “Ложь (False)”. При использовании в формулах, в зависимости от логического значения можно выполнять различные ветки вычислений. рис.

5
Формулы в ячейках нужны непосредственно для вычислений. Как правило, в ячейке, где стоит формула, мы видим результат вычисления, саму формулу мы увидим в строке формул, когда мы стоим на этой ячейке. Начинается формула со знака ‘=’. Например, мы хотим сложить цифру в ячейке А1 с цифрой в ячейке В1 и результат поместить в ячейку С1.

Пишем первое числа в ячейку А1, второе в В1, в ячейке С1 пишем формулу ”=A1+B1” (А и В на английской раскладке клавиатуры) и нажимаем . В ячейке С1 мы видим результат. Возвращаем курсор в ячейкуС1 и в строке формул мы видим формулу. Рис.

6
По умолчанию формат ячеек является – общий, в зависимости от введенного значения программа пытается определить значение (кроме случаев, когда мы в начале поставили апостроф (‘) или равно (=)).

Цифрами 5 и 6 на рис.1 обозначены колонки и строки нашей таблицы. Каждая ячейка однозначно определена буквой колонки и цифрой строки. Курсор на рис.1 находится в левом верхнем углу в ячейке А1 (отображается в области навигации под цифрой 3), следующая вправо В1, а от А1 следующая вниз А2. рис.7

Пример работы в программе Microsoft Excel

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

Поставим курсор на колонку А, рис.8, и щелкнем на ней левой кнопкой мышки. В открывшемся меню щелкнем на пункте “формат ячеек…”. В следующем открывшимся меню рис.9 на вкладке “Число” выбираем пункт “Текст”. В этой колонке будем писать название услуг.

Со второй строки пишем:ЭлектроэнергияХолодная водаВывоз ТБО

Отопление

Надписи у нас заходят на колонку В. Ставим курсор на границу между колонками А и В, он превращается в черточку с двумя стрелочками влево и вправо. Нажимаем левую кнопку мышки и, перемещая границу вправо, увеличиваем ширину колонки А рис.10.
Встав курсором на колонку В, нажимаем левую кнопку мышки и, перемещая вправо, выделяем колонки B, C, D.

Щелкнув на выделенной области правой кнопкой мышки рис.11, в открывшемся меню, на вкладке “Число” выбираем формат “Числовой”.
В ячейке В1 пишем “Тариф”, в С1 “Объем”, D1 “Сумма” рис.12.В ячейку В2, тарифа “Электроэнергия” заносим тариф, в ячейку С2 объем, число киловатт потребленной электроэнергии.В В3 тариф за воду, в С3 объем, число кубометров потребленной воды.

В В4 тариф за вывоз ТБО, в С4, площадь квартиры (если тариф зависит от площади, либо число проживающих, если тариф от количества человек).В В5 тариф за тепловую энергию, в В5, площадь квартиры (если тариф зависит от площади).

Становимся в ячейку D2 рис.13, пишем формулу “=B2*C2” и нажимаем . В ячейке D2 появляется результат рис.14. Возвращаемся в ячейку D2 рис.15.

17, то увидим, что при копировании в строке формул она изменилась на “=B3*C3”, в ячейке D4 ,будет формула ”=B4*C4”, в D5, “=B5*C5”. Это относительные ссылки, об абсолютных, немного попозже.

Мы писали формулу вручную. Я покажу еще один вариант введения формулы. Посчитаем итоговый результат. Ставим курсор в ячейку D6, для того что бы вставить туда формулу итога. В строке формул рис.

18, нажимаем левой кнопкой мышки на надписи функции, отмечена цифрой 1, в открывшимся меню выбираем функцию “СУММ”, отмечена цифрой 2, внизу (выделено цифрой 3) идет подсказка, что делает данная функция. Далее нажимаем “ОК”. Открывается окно аргументов функции рис.

19, где по умолчанию предлагается диапазон аргументов для суммирования (цифры 1, 2). Если диапазон нас устраивает, нажимаем “ОК”. В итоге получаем результат рис.20.
Можно сохранить книгу, под каким либо именем.

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

Абсолютные и относительные ссылки

Что такое абсолютные и относительные ссылки. С относительными ссылками мы столкнулись в предыдущем примере. Когда мы копировали формулу, у нас адреса ячеек из формулы изменялись в соответствии с изменением строк. Записывается относительная ссылка так А1 или В5.

Теперь мы разберем еще один пример.

Наберем список фруктов, цена, вес, сумма, сумма со скидкой, сумма скидки, скидка.
Заполним таблицу рис.21. Для суммы в колонке D наберем формулу суммы, и скопируем как в предыдущем примере. В ячейку С2 поставим скидку, предположим 15%.

Теперь напишем формулу для суммы скидки в ячейку F2. Если мы напишем “=D2*G2”, то при копировании в 3 строке формул будет “=D3*G3”, а процент скидки находится в ячейке “G2”, а в ячейке “G3” пусто.

Для этого перед именем колонки и строки ставится знак доллара. Выглядит это вот так – “$G$2”. Попробуем написать так формулу “=D2*$G$2” и потом скопируем рис.22. Поставим курсор в ячейку F3 (отмечено цифрой 1) рис.

23, в строке формул мы видим формулу “=D3*$G$2” (отмечено цифрой 2), т.е. то, что нам и необходимо. Ну и теперь мы можем написать формулу для суммы со скидкой “=D2-D2*$G$2” (цифра 2) рис.23.
Существует еще один вид ссылок – смешанные.

Когда знак доллара стоит только перед именем столбца или только перед именем строки.

Маленькая хитрость. Иногда необходимо пронумеровать список в Microsoft Excel . Есть несколько способов это сделать. Я покажу один из них. Пусть у нас есть список рис.24. Правой кнопкой мышки щелкаем по колонке А, в открывшимся меню выбираем пункт “Вставить”. Новую колонку выделяем рис.

27 и ставим выравнивание посередине и выравнивание по центру. В ячейку А1 ставим цифру 1, в А2 ставим цифру 2. Выделяем две эти ячейки, становимся курсором в левый нижний угол рис.25 (на точку), курсор превращается в крестик, и тянем вниз до конца списка.

Отпускаем кнопку мышки – список пронумерован рис.29

Этих данных достаточно, что бы начать работать в Microsoft Excel. В этом цель данного урока.

по теме:

Вперед. Урок № 20. Переводчик с английского на русский

Рабочая книга и листы в MS Excel

Рабочая книга располагается в рабочей области окна табличного процессора.

Определение 1

Рабочая книга – это файл, который предназначен для хранения электронной таблицы и имеет расширение .xlsx. Рабочая книга состоит из рабочих листов, которых по умолчанию в новой рабочей книге 3.

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

Рабочие листы могут быть двух типов:

• рабочий лист с данными электронной таблицы (по умолчанию имеют имена Лист1–Лист3);
• лист диаграммы с графическим представлением данных электронной таблицы (по умолчанию называются Диаграмма1, Диаграмма2 и т.д.).

Рабочий лист разбит на строки (нумеруются арабскими цифрами) и столбцы (именуются латинскими буквами).Максимально допустимое число листов в одной книге – 255.

Количество листов в рабочей книге можно изменить в меню Параметры кнопки Office на вкладке Основные в группе При создании новых книг.

Ничего непонятно?

Попробуй обратиться за помощью к преподавателям

Ярлыки всех рабочих листов в книге отображается в нижнем левом углу окна над строкой состояния.

Операции с рабочими листами

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

Рисунок 1. Контекстное меню ярлыка рабочего листа

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

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

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

• одного листа нужно щелкнуть по ярлыку листа;
• нескольких смежных листов – щелкнуть по ярлыку первого листа и, при нажатой клавише Shift, щелкнуть по ярлыку последнего листа;
• нескольких несмежных листов – щелкнуть по ярлыку первого листа и, при нажатой клавише Ctrl, отмечать мышкой ярлыки нужных листов.

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

Рисунок 2. Диалоговое окно Защита листа

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

Режим скрытия формул

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

1. Выделить ячейки, формулы в которых нужно скрыть.
2. Из контекстного меню открыть вкладку Защита диалогового окна Формат Ячеек и установить флажок Скрыть формулы.

Групповое редактирование и форматирование

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

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

Создание колонтитулов

Определение 2

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

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

Для добавления колонтитула нужно щелкнуть в левом, центральном или правом поле верхнего или нижнего колонтитула, после чего автоматически откроется дополнительная вкладка Конструктор инструмента Работа с колонтитулами.

вкладка Конструктор для работы с колонтитулами»>

Рисунок 3. Дополнительная вкладка Конструктор для работы с колонтитулами

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

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

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

При вставке сама информация не отображается, а отображается символ & (амперсант) с названием типа информации в квадратных скобках (например, &[Файл]).

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

Для изменения некоторых параметров шрифта колонтитула нужно:

1. Выделить текст всего поля колонтитула или нужной части.
2. Установить параметры шрифта с помощью элементов группы Шрифт на вкладке или всплывающей мини-панели инструментов.

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

Выйти из режима редактирования колонтитулов можно щелкнув на любой ячейке листа или нажав Esc.

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

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

 О них расскажет Николай Павлов — автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office.

Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓ 

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

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

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

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.).

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

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

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

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

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

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

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

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

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

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

Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

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

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

Появится оглавление, и на любой нужный лист можно будет перейти мгновенно.

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

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

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

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

В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

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

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

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

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

«Умная» таблица

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

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

Спарклайны

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

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

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

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

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

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

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

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

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

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

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

Бонус: другие способы сравнить два диапазона и найти отличия/совпадения.

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

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

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

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

Программа Excel — основы работы с листами в Exel 2010

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

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

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

Когда вы открываете новую книгу Excel, в ней по умолчанию есть три листа. По умолчанию же, они называются Лист 1, Лист 2 и Лист 3. Чтобы систематизировать информацию в книге и облегчить навигацию, вы можете переименовать и даже изменить цвет ярлыка листов. Также вы можете добавить, удалить, передвинуть и скопировать листы.

Чтобы переименовать лист:

1. Кликните правой кнопкой мыши по ярлыку листа, который хотите переименовать. Появится контекстное меню.
2. Выберите Переименовать.
3. Текст на ярлычке станет выделен черным. Введите новое имя листа.
4. Кликните вне ярлычка. Лист переименован.

Чтобы создать новый лист:

Кликните по иконке Вставить лист. Появится новый лист.

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

Чтобы удалить лист:

Листы могут быть удалены из книги, даже если они содержат данные.

1. Выберите лист, который нужно удалить.
2. Кликните правой кнопкой мыши по ярлыку листа. Появится контекстное меню.
3. Выберите Удалить. Выбранный лист будет удален из книги.

Чтобы скопировать лист:

1. Кликните правой кнопкой мыши по ярлыку листа, который хотите скопировать. Появится контекстное меню.
2. Выберите Переместить/скопировать.
3. Появится диалоговое окно Переместить или скопировать. Отметьте поле Создать копию.
4. Нажмите OK. Ваш лист скопирован. Он будет называться, как оригинальный лист, но с номером версии, например «Январь (2)».

Чтобы переместить лист:

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

Чтобы перекрасить ярлык листа:

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

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

Группировка и разгруппировка листов

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

Чтобы сгруппировать листы:

1. Выделите первый лист, который нужно включить в группу.
2. Нажмите и удерживайте клавишу Ctrl.
3. Кликните по следующему листу, который нужно включить в группу. Продолжайте кликать по нужным листам.
4. Отпустите клавишу Ctrl. Теперь листы сгруппированы.

   Ярлычки сгруппированных листов белые.

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

Вы сможете перегруппировать их.

Чтобы разгруппировать все листы:

1. Кликните правой кнопкой по ярлычкам листов. Появится контекстное меню.
2. Выберите Разгруппировать. Листы будут разгруппированы.

Закрепление областей листа

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

  Работа со сводными таблицами в MS Excel

Чтобы закрепить строки:

1. Выделите строку под строками, которые хотите закрепить. Например, если нужно закрепить 1 и 2 строки, то выделите 3.
2. Кликните по вкладке Вид.
3. Кликните по команде Закрепить области.

   Появится выпадающее меню.

4. Выберите Закрепить области.
5. Под закрепленными строками появится черная линия. Прокрутите лист вниз, и вы увидите, что закрепленные строки остаются на месте.

Чтобы закрепить столбцы:

1. Выделите столбец справа от тех, которые нужно закрепить. Например, если вы хотите закрепить столбцы A и B, то нужно выделить столбец C.
2. Кликните по вкладке Вид.

3. Кликните по команде Закрепить области. Появится выпадающее меню.
4. Выберите Закрепить области.
5. Справа от закрепленных столбцов появится черная линия.

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

Чтобы открепить области:

1. Кликните по вкладке Вид.
2. Кликните по команде Закрепить области. Появится выпадающее меню.
3. Выберите Снять закрепление областей. Закрепление с областей будет снято, а черная линия исчезнет.

Практикуйте!

1. Откройте существующую Excel 2010 книгу.
2. Создайте новый лист.
3. Измените название листа.
4. Удалите лист.
5. Переместите лист.
6. Скопируйте лист.
7. Попробуйте группировать и разгруппировать несколько листов.
8. Попробуйте закрепить и открепить столбцы и строки.