Mini-ats102.ru

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

Функция СМЕЩ в Excel

СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина])
Возвращает ссылку на диапазон, смещенный относительно заданной ссылки на указанное количество строк и столбцов.

  • Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
  • Смещение по строкам(обязательный аргумент) и по столбцам(обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки; Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
  • Высота(необязательный аргумент) и ширина(необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки; Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.

Разберем несколько примеров, чтобы понять принцип работы функции СМЕЩ:

Принцип работы функции СМЕЩ

К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на (№2) вниз, на (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.

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

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

Создание именованного диапазона в Google Таблицах

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

Читайте так же:
Где находятся стили в фотошопе cs6

Вот шаги, чтобы создать именованный диапазон в Google Таблицах:

  • Выберите данные, для которых вы хотите создать именованный диапазон.
  • Перейдите на вкладку «Данные».
  • В раскрывающемся списке выберите Именованные диапазоны.
  • На панели Именованные диапазоны введите имя в первое поле. В этом примере мы используем имя SalesData .
  • Убедитесь, что во втором поле указан правильный диапазон данных. Если неверно, измените его.
  • Щелкните Готово.

Это создаст именованный диапазон — SalesData.

После создания вы можете использовать именованный диапазон в формулах. Вот несколько примеров использования именованного диапазона SalesData:

  • Чтобы получить сумму всех продаж: = SUM (SalesData)
  • Чтобы найти среднюю стоимость продаж: = СРЕДНЕЕ (SalesData)
  • Чтобы найти максимальное значение продаж: = MAX (SalesData)

The Used Range Method includes all the cells that have values in them in the current worksheet.

The current region differs from the UsedRange in that it looks at the cells surrounding a cell that we have declared as a starting range (ie the variable rngBegin in the example below), and then looks at all the cells that are ‘attached’ or associated to that declared cell. Should a blank cell in a row or column occur, then the CurrentRegion will stop looking for any further cells.

If we use this method, we need to make sure that all the cells in the range that you require are connected with no blank rows or columns amongst them.

Создаем вот такой список диапазонов:

Далее, создаем именованные диапазоны для наших списков.

Далее — создаем ячейки с именованным диапазоном главной группы, то есть «ОСНОВНАЯ»

Вторую колонку оставляете пустой.

Переходим к написанию логики. Для этого нам потребуется Google script. Доступен он во вкладке «Инструменты» — «Редактор скриптов»

Называть его можно как угодно. Вставляем туда следующий код:

function depDrop_(range, sourceRange) <
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
>
function onEdit () <
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 1 && SpreadsheetApp.getActiveSheet()) <
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
>
>

Должен получиться следующий вид:

Читайте так же:
Где в ворде проставить страницы

Сохраняем, жмем на «Выполнить» (кнопка, похожая на кнопку Play кассетных плееров) Код попросит разрешение, выдаем его и идём в наш документ с диапазонами.

Теперь, при выборе в основной группе, справа в колонке +1 будет появляться подгруппа с диапазоном данных (видео — для понимания)

Все, конечно, хорошо, удобно и работает, но! При удалении данных из столбца А, данные остаются в В. Что не круто.

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

if (aColumn == 1 && aCell.getValue() == “”) <
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
range.clearDataValidations();
range.clearContent();
return;
>

Получится общий вид вот такой:

Делайте копию — код и всё остальное будет работать.

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

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

Прямо над снимком кофейной чашки и клавиатуры компьютера на столе с указанием клавиш Shift и Стрелка

Существует несколько способов выбора диапазона на листе. К ним относятся использование мыши, клавиатуры, поля имени или их комбинации.

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

Как сделать умную таблицу в Excel.

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

Умная таблица в Excel

После этого появиться диалоговое окно Создание таблицы.

Умная таблица в Excel

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

Читайте так же:
Как восстановить несохраненную версию документа ворд

Умная таблица в Excel

Поставим галочку в пункте Таблица с заголовками и получим умную таблицу с «правильными» заголовками столбцов.

Умная таблица в Excel

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

How to Create a Dynamic Chart Range in Excel?

There are two ways to create a dynamic chart range in Excel:

  • Using Excel Table
  • Using Formulas

In most of the cases, using Excel Table is the best way to create dynamic ranges in Excel.

Let’s see how each of these methods work.

Using Excel Table

Using Excel Table is the best way to create dynamic ranges as it updates automatically when a new data point is added to it.

Excel Table feature was introduced in Excel 2007 version of Windows and if you’re versions prior to it, you won’t be able to use it (see the next section on creating dynamic chart range using formulas).

In the example below, you can see that as soon as I add new data, the Excel Table expands to include this data as a part of the table (note that the border and formatting expand to include it in the table).

Now, we need to use this Excel table while creating the charts.

Here are the exact steps to create a dynamic line chart using the Excel table:

  • Select the entire Excel table.Select the Excel table with which to create dynamic chart range
  • Go to the Insert tab.Click on insert tab
  • In the Charts Group, select ‘Line with Markers’ chart.Click on Line Chart with markers

The above steps would insert a line chart which would automatically update when you add more data to the Excel table.

Читайте так же:
Можно ли в презентацию вставить музыку

Note that while adding new data automatically updates the chart, deleting data would not completely remove the data points. For example, if you remove 2 data points, the chart will show some empty space on the right. To correct this, drag the blue mark at the bottom right of the Excel table to remove the deleted data points from the table (as shown below).

While I have taken the example of a line chart, you can also create other chart types such as column/bar charts using this technique.

As I mentioned, using Excel table is the best way to create dynamic chart ranges.

However, if you can’t use Excel table for some reason (possibly if you are using Excel 2003), there is another (slightly complicated) way to create dynamic chart ranges using Excel formulas and named ranges.

Suppose you have the data set as shown below:

Data for creating dynamic named range in Excel

To create a dynamic chart range from this data, we need to:

  1. Create two dynamic named ranges using the OFFSET formula (one each for ‘Values’ and ‘Months’ column). Adding/deleting a data point would automatically update these named ranges.
  2. Insert a chart that uses the named ranges as a data source.

Let me explain each step in detail now.

Step 1 – Creating Dynamic Named Ranges

Below are the steps to create dynamic named ranges:

  • Go to the ‘Formulas’ Tab.Formulas tab in Excel for creating dynamic charts
  • Click on ‘Name Manager’.Name Manager in Formulas Tab
  • In the Name Manager dialog box, specify the name as ChartValues and enter the following formula in Refers to part: =OFFSET(Formula!$B$2. COUNTIF(Formula!$B$2:$B$100,”<>”)) ChartValues Named Range Dialog Box
  • Click OK.
  • In the Name Manager dialog box, click on New.
  • In the Name Manager dialog box, specify the name as ChartMonths and enter the following formula in Refers to part: =OFFSET(Formula!$A$2. COUNTIF(Formula!$A$2:$A$100,”<>”)) Creating Named Range for Months
  • Click Ok.
  • Click Close.
Читайте так же:
Как в ворде выровнять строки в таблице

The above steps have created two named ranges in the Workbook – ChartValue and ChartMonth (these refer to the values and months range in the data set respectively).

If you go and update the value column by adding one more data point, the ChartValue named range would now automatically update to show the additional data point in it.

The magic is done by the OFFSET function here.

In the ‘ChartValue’ named range formula, we have specified B2 as the reference point. OFFSET formula starts there and extends to cover all the filled cells in the column.

The Same logic works in the ChartMonth named range formula as well.

Step 2 – Create a Chart Using these Named Ranges

Now all you need to do is insert a chart that will use the named ranges as the data source.

Here are the steps to insert a chart and use dynamic chart ranges:

  • Go to the Insert tab.Insert Chart from Ribbon in Excel
  • Click on ‘Insert Line or Area Chart’ and insert the ‘Line with markers’ chart. This will insert the chart in the worksheet.Click on Line Chart with markers
  • With the chart selected, go to the Design tab.Design tab - Contextual tab that appears when a chart is selected
  • Click on Select Data.Select Data in the Design tab
  • In the ‘Select Data Source’ dialog box, click on the Add button in ‘Legend Entries (Series)’.Add Series Data for the Excel Dynamic Chart Range
  • In the Series value field, enter =Formula!ChartValues (note that you need to specify the worksheet name before the named range for this to work).Edit Series for Chart with Sheet name along with named range ChartValues
  • Click OK.
  • Click on the Edit button in the ‘Horizontal (Category) Axis Labels’.Horizontal Axis Values for months in Excel chart
  • In the ‘Axis Labels’ dialog box, enter =Formula!ChartMonths Axis Namrd Range for Months in Excel Chart
  • Click Ok.

That’s it! Now your chart is using a dynamic range and will update when you add/delete data points in the chart.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector