How to select data from excel. Recommendations on how to quickly create a list of what appears in MS Excel. Vikoristanya lists of koristuvach in Excel

Under the list of what appears, there is a replacement in one middle of several values. When the player clicks on the arrow with his right hand, a song appears. You can be more specific.

A very handy tool for checking entered data. Increase the comfort of working with data by allowing the possibility of lists that appear: substitution of data, display of data in another archive or file, availability of search and location functions.

Created list

Way: menu "Data" - tool "Verification of data" - tab "Parameters". Data type - "List".

You can enter the values that will form the list that appears in different ways:

Any of the options will give such a result.

List of what appears in Excel with data substitution

It is necessary to create a list that opens with values from the dynamic range. As soon as changes are made to the existing range (data are added or deleted), they are automatically displayed in the list that is opened.

We protest. The axis is our table with a list on one arch:

A new meaning for “Yalinka” has been added to the table.

Now we can see the meaning of “birch”.

A “reasonable table” helped us achieve our plan, as it is easy to “expand” and change.

Now we’ll set it up so that you can enter new values directly into the box with this list. And the data was automatically added to the range.

If we enter a new name into the empty checkbox of the list, a new name will appear: “Add the entered name of baobab to the list, what does it drop?”

Press “So” and add another row to the “baobab” values.

List that appears in Excel with data from another archive/file

If the values for the list that appear are arranged on a different arch or in a different book, standard method don't bother. You can verify the information using the additional function of DVSSIL: it will form the correct information sent to the external address.

Robomo is the active middle, where you want to place the list that opens up.
We open the data verification parameters. For the “Dzherelo” field, enter the formula: =DVSSIL(“[List1.xlsx]Arkush1!$A$1:$A$9”).

The name of the file from which the information for the list is taken is located in square arms. This file may be opened. If the book with the required values is in another folder, it is necessary to indicate the directions in full.

How to create stale lists that drop out

Let's take three named ranges:

Tse obov'yazkova umova. It is described below how to create a primary list of named ranges (using a name manager). We remember that we cannot remove spaces and division marks.

Create the first list of what appears where the names of the ranges go.

If you put the cursor at the “Dzherelo” field, we go to the arkush and see the required middles alternately.

Now let’s create another list that will be revealed. Someone may see the same words that correspond to the names in the first list of names. Like “Trees”, then “hornbeam”, “oak”, etc. For the “Dzherelo” field, enter a function like =DVSSIL(E3). E3 is the middle part of the first range.

Select a value from the Excel list that matches

It turns out that if the list opens, you need to select a number of elements. Let's take a look at the ways of implementing the task.

We create a standard list using the additional “Data Verification” tool. Available in output code leaves ready macro. Yak tse robiti, described more. Using your right hand, you can add selected values to the drop-down list.
To ensure that the selected values are shown below, insert a different detector code.
So that the selected values are displayed in one center, separated by any division sign, such a module is formed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval)<>0 And oldval<>newValThen
Target = Target & "," & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Don’t forget to change the ranges to “your own”. Lists are created in the classic way. And the reshtu robots will be robotic macros.

List of things that come up in searches

When you enter the first letters from the keyboard, additional elements are highlighted. And this is not all the best moments of this instrument. Here you can customize the visual presentation of information, indicating how there are two sections at a time.

There are many ways to create a list that appears. The choice of one of them depends on the structure of your data.

The first way to create a household list

The first method is based on the creation of a “reasonable” table, the title of which corresponds to the values of the first drop-down list (group), and the table rows correspond to the values of another drop-down list (subgroup). The values of the elements of the guilty subgroup vary in the congenital part of the group, as if they were a little lower.

Now let's proceed to creating the first list of groups that fall in (my selection is a list of countries):

Select a business and insert a list of what appears;
Go to the stitches tab Dani;
Select a team Data verification;
Select a value from the list overflow;
In the field Dzherelo I'll say the formula =DVSSIL("Table1[#Headers]").

Formula DVSSIL rotates the selected range of headers of the “smart” table. The advantage of using such a table is that when you add columns, the list that appears will be automatically expanded.

I forgot to create another stale list, which is missing - the list of subgroups.

We kindly repeat the first 4 points of the descriptions above. Jerel at the window Data verification for another variable in the service list, the formula is =DVSSIL("Table1["&F2&"]"). Komirka F2 at once - the value of the first drop-down list.

You can use the original “unreasonable” table, but in this case you will have to manually change the ranges of the headers and rows. When you look at the application, it appears automatically.

Another way to create a courtyard list

Another way is to manually stack, when given a list, what comes up is written in two columns. The first one has a group name, and the other one has a subgroup name.

IMPORTANT! In front of the gates stale list By subgroups, it is necessary to sort the output table by the first column (the column from the group) in order to avoid problems.

To create the groups that fall out, we need an additional element to place the unique values of the groups from the output table. To create this list, use the Remove Duplicates function or use the Unique command from VBA-Excel.

Now we can create a list of groups that appear. For this purpose, select the first 4 points in the first way to create a household list. In yakosti Dzherela enter range unique meaning group Everything here is standard.

Recommendation: manually, as usual, specify the naming range. For your creation, open the door Name Manager zі tabs Formula Then name the range with unique values.

Now the most difficult part is to put it in Dzherela dynamically sent to the range with the values of another list that falls (subgroup list). Click here for additional functions ZMISH(position, offset_by_rows, offset_by_columns, [height], [width]), which rotates the message to the range that is removed from the middle or the range of middles by the specified number of rows and columns.

Posilannya at our vipadka - $A$1- Upper left corner of the output table;
Shift_by_rows - SHOOKPOSE(F3,$A$1:$A$67,0)-1- the number of the row with the values of the group that is being searched for (in my type, the edges of the middle F3) minus one;
Displacement_by_stovpts - 1 - this is how we need cooperation from subgroups (places);
[Height] - RAHUNKS($A$1:$A$67;F3)- number of subgroups in a group (number of places near the edge F3);
[Width] - 1 - so this is the width of our column with subgroups.

When the table is created, it is very easy to manually check the lists that fall out (or, apparently, appear). Excel 2010 allows you to work in many ways. Let's take a look.

Method 1. Create a list that opens in Excel 2010 using the “Data Verification” tool

This method is considered standard, since it is simple and straightforward.

1. On to the free place Arkusha write down all the elements of the list that is being opened in a stack, the skin element is in its middle.

2. Enter the name of the range of coms. For whom:

Click on the top button of the list and, pressing the left mouse button, drag the cursor down until the entire list is visible.
place the cursor near the “Name” field, left-handedly in the row of formulas;
enter the name in the list and press Enter.

Please note that the list may start from being deleted and will not contain any gaps.

3. See the item in the table, where the list that appears will be placed.

4. Open the “Data” tab and click the “Data Verification” button. In the window, go to the “Options” tab. In the “Data type” list, select “List”.

5. In the “Dzherelo” row you need to indicate the address, the stars will be taken from the created list. The address will be the one you gave to the middle range. You can set the address in a number of ways.

I’ll write it in by hand, putting the sign “equally” in front of it, for example, “= month”. The registry letter has no meaning.
By clicking the mouse in the “Dzherelo” row (to activate), you can see with the cursor all the elements in the list in the table.

6. If you need to create a prompt for entering, open the single tab. Write the text that appears in the middle of the open list when you see it. On the last deposit - “Notice about the amends”, so you can write the text that informs about the amends.

7. Confirm the entry with “OK” and the ready list. To open it, press the button with the down arrow to appear in order from the middle to move the list.

Method 2. Shvidke gate on the list

A drop-down list in Excel 2010 can be created with just one press of keys, but it can only be expanded in one place - in the list under the elements of the list.

1. Reorganize all the elements of your new list.

2. See the box located under the remaining element and press the Alt keys + down arrow - the list will be created. This method allows you to specify the commercial value of one of the elements.

Method 3. Create a list of what appears as a control element

To complete this method, open the “Reseller” tab: open the menu “File” – “Options” – “Customize lines”. In the “Main Tabs” section, check the “Reseller” option. Confirm this by pressing “OK” - the tab will be created.

1. Reorganize the elements of the next list until it’s complete.

2. From the “Insert” menu of the “Reseller” tab, select “Form elements” - “List list”.

3. Draw on the arches with the cursor your Mayday list that will open. Click on new right button Click and select the “Object Format” item from the menu.

4. The values of the “Form list by range” field will be a list of elements - see it with the cursor, and the field will be filled in automatically. In the “Link from middle” field, the addresses in the middle are indicated, where the serial number of the seen element will be shown. Select a business and click on it. The “Number of rows in the list” field allows you to customize how many elements will be displayed when the list is opened.

5. Confirm your entry and click “OK”. The list will be created.

Method 4. Create a list that drops down as an ActiveX control

The most complex method, but it allows for maximum fine-tuning.

1. Create a list using the described method.

2. From the “Insert” menu of the “Reseller” tab, select “ActiveX Elements” - “Combo Box”.

3. Paint on the arches the Mayday list of things that will be revealed.

4. The option that allows you to edit the list that appears is called “Design Mode”. If this mode is active, you will see a single button in the “Current Elements” section, in addition to the “Insert” button. If the button is not visible, the editing mode is disabled.

5. To set parameters for the list, click the “Power” button in the same section. The “Properties” window opens. The two tabs of this window contain the same parameters, sorted in the first by alphabet, in the other by categories.

6. Most of the adjustments can be made for washing, and what is most necessary can be done lower.

ListRows is an analogue of the “Number of rows in a list” value, showing how many rows will be displayed.
Font — Customize the font. Allows you to select the font of your image.
ForeColor — Select a font color from the table.
BackColor – background color.
ListFillRange — expands the list of elements in the format: sheet (“!” - divider) and range of middles. For example: Sheet2! D2: D6. Register manually.
LinkedCell - a link with a middle. I will manually enter the addresses in the middle, where the serial number of the visible element in the list will be shown.

7. Save settings and deactivate the designer mode by pressing a single button. The list that appears will be creations, and you can check how it works.

When filling in the middle with data, it is often necessary to limit the possibility of entering a value with a single list of values. For example, in the middle, where the clerk is obliged to enter the name of the department, indicating that he is working. It is logical to first create a list of departments of the organization and allow the employees to select a value from this list. This approach will help speed up the process of introducing and change the number of drug benefits.

Variable list you can call for more help

This article is soluble Variable list for more help () with data type overflow.

Variable list can be formulated differently.

A. The simplest list that comes up is entering the elements of the list directly into the Dzherelo field

Let's say, in the middle B 1 it is necessary to create random list for the introduction of single vimirs. We see the business B 1 ta wiklichemo Verification of data.

Yakshcho near the field Dzherelo indicate through the dot with which unit vimir pcs;kg;sq.m;cub.m, then the choice will be limited by the same values.

Now we’re amazed at what happened. We see the business B 1 . When you see a right-handed checkbox, the checkbox appears with a square button and an arrow to select items on the list.

Nedoliky What's the approach: it's easy to spend items on the list (for example, having seen a row or a stack of items to take revenge on the shopping list) B 1 ); It is not possible to manually introduce a large number of elements. Suitable for small (3-5 values) permanent lists.
Perevaga: liquidity of the list

B. Entering list elements into the range (on the same page as the list that drops down)

Items for the list that appear can be placed in the range on the EXCEL box, and then in the field Dzherelo The tool will indicate the transmission to this range.

Let's say what's on the list pcs;kg;sq.m;cub.m introduced into the commercial range A 1: A 4 this field Dzherelo mistime =sheet1!$A$1:$A$4

Perevaga: perfect transfer of elements and ease of modification. Suitable for lists that change rarely.
Nedoliky: When new items are added, you need to manually change the range setting. It is true that, in general, a much wider range can be taken into account, for example, A 1: A 100 . In addition to the list that appears, you can remove empty rows (for example, some of the elements have been deleted or the list of items has been created). If there are empty rows, you must save the file.

Another drawback: the range of dzherel is due to grow on the same arch as i random list, because For the rules, it is not possible to copy messages to other books or books (this is true for EXCEL 2007 and earlier).

Let's get rid of the beginning of another little bit - the overflow of elements can be accommodated on the list on another arkush.

B. Entering list elements into a range (for whatever reason)

Entering list elements into the range of middles that are in another book

If you need to move the range with the elements of the list that matches into another workbook (for example, into the book Dzherelo.xlsx), then you need to do the following:

At the bottom Dzherelo.xlsx create the required list of elements;
At the bottom Dzherelo.xlsx range of temperatures to accommodate the overflow of elements, add moisture, for example ListList;
open the book in which you want to place the middle of the list that appears;
see the required range of apertures, select the tool , in the field Dzherelo say = DVSSIL("[Dzherelo.xlsx]sheet1!ListZovnish");

When working with a shift of elements, arranged in another book, the file Dzherelo.xlsx You are responsible for being open and in the same folder, otherwise it is necessary to indicate a new path to the file. Vzagali poslan na inshi arkushі krasche unikat i vykoristuvat Specialist macro book Personal.xlsx or Nadbudovi.

It is not necessary to give a name to the range of the file Dzherelo.xlsx, then the formula needs to be changed to = DVSSIL("[Dzherelo.xlsx]sheet1!$A$1:$A$4")

PORADA:
There are a lot of conflicts with the rules in Arkusha Data revisions, then you can use the tool ( Home/ Know and see/ See the middle group). Option Data verification This tool allows you to see the gaps for which the validity of the data is checked (specified using the additional command Data/Working with data/Checking data). When choosing a remixer Usikh You will see all the same middles. When selecting the option Tsich and There are only those companies that have established the same rules for data verification, as well as active companies.

Note:
Yakshcho random list If the value is over 25-30, then it becomes difficult to work with it. Variable list At one time it displays only 8 elements, and in order to get it right, you need to do a little scrolling, which is not always done manually.

In EXCEL there is no adjustment for font size Vipadayugo list. If there are a large number of elements, you can create a list of elements and select an additional classification of elements (so that one list that appears is divided into 2 or more).

For example, in order to effectively process the list of volunteers, there are over 300 volunteers, which should first be sorted in alphabetical order. Then create random list, to take revenge on the alphabet. Other random list I am guilty of avenging even those nicknames that begin with the list compiled in the first list. To complete such a project, you can use the Vicoristan structure or .

Many experts will not realize that the well-known spreadsheet editor Excel has such functions and has such tools that go far beyond the main function of the spreadsheet program - an editing table. This article will tell you about the option to select in other words, perhaps how to create lists that appear in the middle of the table.

Method 1: creating an additional list

Do you want to work in the middle? Excel list If you choose, the easiest way is to do it this way, which is to simply create a list that appears. Before the speech, two variations of it will be explained, so read to the end so that everyone can understand.

Lesson 1: ready data

First of all, you need to create a table in the middle range with the data that will appear in the list that appears. Let's take a look at everything from the product range. Well, we have a list of seven products, or more precisely, products. We will create this table just to the right of the main table, within which we will create lists that will fall out.

If you don’t want the table with the data to be on the main one, you can create it on the right side. It doesn’t play a role.

Procedure 2: enter the name of the range

To select the Excel list selection option, you must first enter the range name with the data for the current list. Trying to do it is simple:

See the middles that have different names of goods.
Press the right mouse button (RMB) to see it.
Select the “Assign a name” option from the menu.
In the “Name” window, enter the name of the range. You can absolutely be like that.
Click "OK".

Another Wiconano croc. Our creations have a wide range of middle ones to make it easier to create a list in the future.

Croc 3: detailed list of what comes up

Now you can go directly to the next option to select from the Excel list. Try it like this:

See the required range of middles in which the lists that fall out will be expanded.
Go to the "Data" tab.
In the “Work with data” group of tools, click on the “Data verification” button.
In the window, on the “Parameters” tab, select the “List” value from the “Data type” list.
Enter in the “Dzherelo” field the name of the previously created range of comics, first putting a sign of equality. Our vipadka has “= Products”.
Click "OK".

Immediately after the selected middle, lists of what will appear will appear. This is the first method of this creation, let’s move on to the other.

Method 2: open the list of items that appear through the “Retailer” menu

It’s entirely possible that the previous instructions seemed stupid to you, and you encountered difficulties when selecting a value from the Excel list. Another method of implementation may be a good alternative.

How to create a list of what falls in the middle of the arkush for the additional menu of the “Rozrobnik”? As before, for a quick understanding, all steps will be divided into stages.

Croc 1: turns on the “Retailer” menu

So, first of all, you need to activate the “Retailer” menu, as there are no other tabs left behind.

Click the “File” button.
Click on the “Options” button.
In the same window that appears, go to the “Customize Streams” section.
In the “Main Tabs” area, place the icon opposite the “Reseller” item.
Click "OK".

The required toolbar is activated, you can now print the created list.

Croc 2: insert a list of what comes up

It is necessary to create the “Drop-down list” element itself. For whom:

Go to the “Retailer” tab.
On Arkusha, create a list of products that will be selected to create a list that appears.
Click on the “Insert” button and select “List Box” in the additional menu.
Click on that business to see the list itself.

Already at which stage the required element appears, if you click on it, an empty list will open. Obviously, you need to add new products.

Lesson 3: set the necessary parameters

To add items to the list, you must:

On the toolbar, click the “Design Mode” button.
Then press the button “Power of the control element”, so that there is a manual.
In the window with authorities, in the ListFillRange column, enter the range of middles that contain the items in the current drop-down list.
Now right-click on the list that appears, and select “ComboBox Object” in the menu, and in the Edit submenu.

The items listed below will be added to the list that appears. You can simply select from the Excel list in another way.

Method 3: creating a linked list

To select several values from a list that appears in Excel, the best approach is, but sometimes there is a need to interconnect several such lists. Fortunately, the program allows you to work, and a report will be given later Pokrov's instructions with a detailed description of all actions.

Krok 1: creating additional overflow

Pershochergovo it is necessary to create a main list of what appears. We won’t dwell on this for too long, since the design is completely similar to that described in the first method. Let's just say that we will tie up the name of the product with this car. It is recommended to create range names based on product ranges (g, kg, ml, l).

Lesson 2: The first list is linked to others

Well, now let’s move on to the main one - to create another element “Select from the list” in Excel, which will be related to the first one.

Place the cursor near this business to display another list.
Open the “Value Verification” window by clicking on the “Data” tab next to the “Data Verification” button.
In the window, on the “Parameters” tab, select the “List” item from the “Data type” list.
In the input field “Dzherelo”, enter the formula “DVSSIL”, which applies to the first list. In this case, look like this: “=DVSSIL($B3)”.
Click "OK".

Another list has been created. There are no connections to the first, which means that, having selected the values in this product category, you will need to select the same for your world. To avoid creating the same lists in other middles, see what is already added and pull the lower right corner of the view down, thereby filling all the required middles.

Visnovok

The option to select from the Excel list is complete, so you can understand everything that has been said. But what is more important is that its creation does not require in-depth knowledge of the table processor. Moreover, there are three ways to implement this function, and following the instructions described above, you should not run into any special problems when installing them.

Categories
- Android
- Program
- iOS
- Printers
- Editors
- Play
- Smart TV
- Internet
- Gadgets
- Zalizo

Popular